Export to Excel

[Paul]

I have three queries and I want to export them to Excel in one Excel
workbook or in seperate workbooks in a Excel file. I know the code to export
one query to Excel as follow:

DoCmd.OutputTo acQuery, "Qry_1", "MicrosoftExcelBiff8(*.xls)",
"C:\\Export\Query_1.xls", False, "", 0

I wonder if I can do this.....

DoCmd.OutputTo acQuery, "Qry_1" & "Qry_2" & "Qry_3",
"MicrosoftExcelBiff8(*.xls)", "C:\\Export\Query_All.xls", False, "", 0

 

[Sam]

Why make it so difficult. Just highlight the Query you want to export, go to
File and choose Export, name the query and save as an Excel file. It's
extremely simple to do. Once the files is exported to Excel you can put them
all in the same workbook if you choose.

 

[Ken Snell [MVP]]

Nope.

Check out the TransferSpreadsheet method in Help. It allows you to export
queries onto separate sheets in a single EXCEL file.

The first TransferSpreadsheet will create the file. The next
TransferSpreadsheet puts its results on a new sheet in the file. So does the
third one.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_1",
"C:\Export\Query_All.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_2",
"C:\Export\Query_All.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Qry_3",
"C:\Export\Query_All.xls"

 

[Chris Reveille]

You could export them to one workbook and use different
sheets. This will keep them in the same workbook.

DoCmd.TransferSpreadsheet acExport, 8, "Qry_1",
"C:\\Export\Query_1.xls", False, "Qry_1"

DoCmd.TransferSpreadsheet acExport, 8, "Qry_2",
"C:\\Export\Query_1.xls", False, "Qry_2"

DoCmd.TransferSpreadsheet acExport, 8, "Qry_3",
"C:\\Export\Query_1.xls", False, "Qry_3"

Chris