How to export multiple tables to a Single Excel file? 1

[dannydanny]

Hi,

I know about the docmd.outputTo code to export a table to an Excel file. However I would like to export multiple tables to a single Excel file, with each table represented on different Excel Worksheets. Is this possible in Access 97? I suspect it might take some complex VBA coding in Access, using the Excel application object, but I am hoping that there is a more simple and elegant solution....

If I use multiple docmd.outputTo commands for each table, the Excel file just contains the table from the last command. e.g.
**********************************
sub ExportToExcel()

DoCmd.OutputTo acOutputTable, "myTable1", acFormatXLS, "c:\test.xls"
DoCmd.OutputTo acOutputTable, "myTable2", acFormatXLS, "c:\test.xls"

end sub
*********************************

The Excel file would only have the myTable2 data.


Thanks for any info,
Danny.

 

[danvlas]

Not OutputTo, but TransferSpreadsheet:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Table1", "C:\Path\TestFile.xls", True, "Sheet1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Table2", "C:\Path\TestFile.xls", True, "Sheet2"

Good luck




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dannydanny]

Hi Daniel,

Thanks for the tip! It was exactly what I was looking for!

Cheers,
Danny