Exporting multiple queries to multiple sheets within one excel fil

M

MCSInc

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"
 
M

MCSInc

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"
 

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

Top