Exporting multiple queries to multiple sheets within one excel fil

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
Use the TransferSpreadsheet action instead:

DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry1", "C:\LocalDB\ExcelExportName.xls"
DoCmd.TranferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"qry2", "C:\LocalDB\ExcelExportName.xls"
 
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...
 
Back
Top