Solved: export to excel

[ailyn]

I have a form whose record I'd like to export to excell. The problem: I need to check first that the record exists and then if it doesn't create one (if possible using a patern determined in an example .xls) with all the fields of the form, including the independant fields that I made in the form for calculation of totals. Is that possible? If so, could anybody please enlighten me as to how the could has to look like? I tried a thousand variants of existant file + transferspreadsheet/output to but nothing.

[geekgirlau]

All things are possible given time and money.

Now to specifics - are you able to post your database here? Please strip out any confidential or sensitive information first and zip up your database. It would help to see the form and the tables and/or queries that populate the form, along with a sample of what you want in Excel.

[ailyn]

Well..I don't know about the money, but the time for sure. ^.^

I already found an export solution although the format is quite bad:


Private Sub ProformaExcell_Click()

Dataexport = "C:\invoices\" & Me("invoiceNr") & Me("SellerId") & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "invoices", Dataexport, True

End Sub


The other big problem now is how to export a query with an inside loop. Invoices query should include: invoices table + sellers table + orders table + order details with a loop to show all the items of the same order and export them in the same invoice. They are all joined by Ids and they work perfectly in the form with a subform for order details. But I can't make a query do that. What should I do?

Another thing I'd like to export is subtotal and total. they are not fields in any table but independant calculations (that I worked out in the form).

[geekgirlau]

Can I ask why you need to export this to Excel?

The issue is the structure of the data. Because you are dealing with sub-queries, you're not going to get the same sort of format that you would see in a form or report without some (or a lot of) manipulation.

However if you want to create a document containing that data you can generate a report (which will handle the structure of your data) and export it to either Word or Excel. It might be worth your while experimenting with these export options manually before looking at how to automate the procedure.

[ailyn]

I already made a report and it works perfect, but how do you export a report?

[geekgirlau]

If you have the Print Preview toolbar displayed, there is a button that looks like the Microsoft Word icon. It has options to publish the report to Word or analyse it with Excel - test both options to see what the end result looks like.

[xCav8r]

Ailyn, :hi:

Welcome to VBAX!

Sub GetOutReport()
DoCmd.OutputTo acOutputReport, "Report1", acFormatXLS, , True
End Sub

HTH! :whistle:

[geekgirlau]

Hi Ailyn,

Does this give you what you need?

[ailyn]

Well, I'm working on it at the moment. I still have not added the "GetOutRepport" action that xCav8r so kindly gave me because when I analysed with Excell or exported it it still gave me the same horrible looking results. It exports everything allright but all in a line. I need it with a nice display because is an invoice we have to send to the customer. Is there any chances that this can be done? I'm still searching for it but nothing.

[geekgirlau]

Did you try the export to Word? Does it have to be in Excel?

[ailyn]

Well, actually yes. But if I can't export it good I guess I'll have to stick to the report or the word version.
Thanks for all the help!

[geekgirlau]

You can export a report as an RTF (rich text format) file, which retains the format apart from graphic images such as logos, lines and boxes. The other option if you need a file to send is to create a mail merge template in Word, and merge your report data to this document.