re post: DoCmd.OutputTo into multiple worksheets in excel

[Guest]

Hi

I have a number of queries from which I want to export to excel. I can export a query to an invidual workbook. But how do I export another query to form a second worksheet in that same workbook

Example code below

Thanks
Marcus

myDir = "D:\My Documents\temp
myFile = Format(Now(), "yyyymmdd") & "_MonthSummary.xls

ChDir myDi

' export 1st query to excel output
myQuery = "_MonthSummary
DoCmd.OutputTo acOutputQuery, myQuery, acFormatXLS, myFile, Fals

' export 2nd query to excel output
myQuery = "_DaySummary
DoCmd.OutputTo acOutputQuery, myQuery, acFormatXLS, myFile, Fals

The second export currently overwrites the first file...

 

[Ken Snell]

Use the TransferSpreadsheet method. If you run it for each query, and use
the same EXCEL filename for each time you run it, each query will be put
into its own worksheet in that same file.

Check it out in Help. It can be run from macro or from VBA code.

--
- - - - - - - - - - - - - - - - -
Ken Snell
<MS ACCESS MVP>

Hi,

I have a number of queries from which I want to export to excel. I can

export a query to an invidual workbook. But how do I export another query to
form a second worksheet in that same workbook?

 

[Guest]

Thanks

Works like a treat

Marcus