Output To for different workbooks in a spreadsheet?

[Ron]

Hi All,

Access2000. I've got a query I have running in conjunction with an OUTPUT
TO statement that pops the data over to an Excel spreadsheet. However, I've
got several to output...and they all need to be "TO" the same spreadsheet,
but different workbooks in that same spreadsheet. It's working fine to
individual spreadsheet files, but then I have to have the operator
copy/paste into the one that has separate worksheets. A little bit
cumbersome for something that'll be done regularly.

Is this possible? I'm completely unfamiliar with Excel and would rather not
have to learn it enough to figure this out right now. Any way it can be
done from Access with the OUTPUT TO command? Or, some other?

TIA,
ron

 

[Pat Hartman]

The OutputTo does not have enough flexibility to do what you want. Use the
TransferSpreadsheet method. It uses the query name as the sheet name and
you get to specify a file name separately. So, if two differently named
queries export data to the same workbook, the data will end up on separate
sheets.

 

[Jeanette Cunningham]

Ron,
a quick search on the newsgroup found the following:

Ken Snell [MVP]
Subject: Re: export several tables to one xls workbook
Use TransferSpreadsheet (macro or VBA) two times in a row. Specify the same
xls file each time. So long as the table or query names are different,
ACCESS will
write their data on separate worksheets.
TransferSpreadsheet creates new worksheets for each query.
TransferSpreadsheet will not add data to an existing sheet.

Jeanette Cunningham

 

[Ken Snell \(MVP\)]

In addition to the info posted by Pat and Jeanette, see Alex Dybenko's blog
about how the undocumented Range argument in TransferSpreadsheet sometimes
can be used to control writing data onto existing worksheets:
http://alexdyb.blogspot.com/2006/07/export-to-excel-range.html