DoCmd.OutputTo into multiple worksheets in excel

M

marcus.

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

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

marcus. said:
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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top