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 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....

 

[Nikos Yannacopoulos]

Marcus,

Don't use OutputTo, use TrabsferSpreadsheet instead. One of the arguments is
Range. A string passed to it will result in a worksheet created and named
after it, so you can use myltiple TransferSpreadsheet methods specifying the
same workbook and different sheets (different Range argument values).

HTH,
Nikos

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 workbook?