Output query to excel

[Bob]

Hi,

Can anyone help me with outputing the results of a query to a particular
worksheet in an Excel workbook.
Using an "Output To" macro, I've managed to output my query to Excel so
that it replaces the whole .xls workbook.
But what I need to do is output to Sheet 1 of the .xls workbook and
leave Sheet 2 intact as it will have formulas referring to Sheet 1.

Thanks

Bob

 

[fredg]

Hi,

Can anyone help me with outputing the results of a query to a particular
worksheet in an Excel workbook.
Using an "Output To" macro, I've managed to output my query to Excel so
that it replaces the whole .xls workbook.
But what I need to do is output to Sheet 1 of the .xls workbook and
leave Sheet 2 intact as it will have formulas referring to Sheet 1.

Thanks

Bob

Look up the TransferSpreadsheet method in VBA help.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"QueryName", "c:\PathToFolder\FileName.xls", True

The above will add a new worksheet to the workbook named "QueryName".
You can use code within the workbook to then copy your data to
whatever sheet you wish, then delete the "QueryName" worksheet.

Change "QueryName" to whatever the actual name of your query is.