Exporting multiple queries to multiple sheets within one excel fil

G

Guest

I want to export 5 queries to 5 sheets within the same excel file. The
following command keeps overwriting the same excel file.

DoCmd.OutputTo acOutputQuery, "qry1", acFormatXLS,
"C:\LocalDB\ExcelExportName.xls", False

DoCmd.OutputTo acOutputQuery, "qry2", acFormatXLS,
"C:\LocalDB\ExcelExportName.xls", False

I want qry1 and qry2 to exists as sheets in ExcelExportName.xls

Much appreciate any help. Thanks.
 
K

Ken Snell \(MVP\)

Use the TransferSpreadsheet action instead:

DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry1", "C:\LocalDB\ExcelExportName.xls"
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry2", "C:\LocalDB\ExcelExportName.xls"
 
G

Guest

Thank you so much Ken...that worked out great!!

Ken Snell (MVP) said:
Use the TransferSpreadsheet action instead:

DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry1", "C:\LocalDB\ExcelExportName.xls"
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry2", "C:\LocalDB\ExcelExportName.xls"
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I script to export two different queries to same excel file. 4
Send data to Excel using "OutputTo" 4
Export Query to Excel 1
Access export to excel produces "unreadable data" 0
output to, transferspreadsheet, or excel automation? 2
exporting multiple queries to Excel 1
Output To Excel defaults to version 5-7/want Excel 10 4
Exporting To Excel 5

Top