# Using VBscript...how can I export Access form data to an Excel spreadsheet?



## matt_h76

Hi

I have a form in Access which has a button, which when clicked normally outputs the data to a report in Access. The form uses VB script to do this and the specific line of code is as follows:
DoCmd.OpenReport stDocName, acPreview

This all works fine.

How could I change this line of code so instead of viewing the report in Access it automatically sends the output to an excel spreadsheet file? 

Please note I want to do it all from within the line of code, without using the File menu and export as option.

Many thanks for any help.

Matt


----------



## matt_h76

Ok...I have worked out how to send my data to a spreadsheet using the 'TransferSpreadsheet' function along with 'acExport'. This all works. I am also using a LOOP to loop through a recordset and wish to keep adding more and more data to the same spreadsheet. But so far it keeps overwriting each time, and I get left with the last record of data in the spreadsheet. Basically is there a way for me to *append* to a spreadsheet document each time I loop through the recordset?


----------



## matt_h76

Hi 

Thank you for any help. I can output the report, query and table data. However each time I go through the loop the data changes (this is how my system works) but because of this I find the contents of the spreadsheet only holds the last record in the recordset as the query data refreshes each time. Is there a way to apend to the spreadsheet 'each' time the loop is carried out, or alternatively export the data to a new worksheet tab in the spreadsheet each time the loop is carried out, so I get all the records in the recordset, as opposed to just the last one?

Here is my LOOP code so far:

Dim x As Variant
Dim TempDB As Database
Dim Curtbl As Recordset

Set Curtbl = TempDB.OpenRecordset("SELECT [CustomerID] FROM [SETUP Delivery Sheet Position Tbl Qry] WHERE [Route Number] = " & [Route Code])

Curtbl.MoveFirst

Do Until Curtbl.EOF
x = Generate_Invoice_Data(Curtbl("CustomerID"), [Invoice Start Date], [Invoice End Date])

'DoCmd.OutputTo acOutputQuery, "Customer Account Details Qry", acFormatXLS, "C:\GenerateInvoices.xls" (commented out)

DoCmd.TransferSpreadsheet acExport, , "Customer Account Details Tbl2", "C:\GenerateInvoices.xls"


Curtbl.MoveNext

Loop

--------------------------------------------------

I am using Access 97 and Excel 97.

Thank you for any help.

Matt


----------



## matt_h76

Must be a pretty tough problem... anyone?

Thanks in advance.


----------

