Multiple tabs in a single spreadsheet

[Anthony Lisanti]

Some time ago I posted about exporting queries to 1 spreadsheet with
multiple tabs. Some suggested TrasferSpreadsheet, but this only works
with 1 tab per spreadsheet.


I was able to find something that would do it for me

http://accesstoexcel.com/

It was the best $20.00 I've spent this year. Well, it was my
department's budget, but who cares. It was WELL WORTH IT.

If anyone is looking for exporting to multiple tabs, this is #1 so far
in my book. It cut my monthly reporting time my 70%.

Take care guys!

 

[Ken Snell \(MVP\)]

Advertising a product that costs money to do something that is already
available in ACCESS is gauche.

All you need to do is use the same EXCEL filename as the FileName argument
in TransferSpreadsheet action for each export. And it doesn't cost you any
more money.

 

[Anthony Lisanti]

It wont make more tabs in the sheet, it will over-write it with your
method.

Unless, I'm doing something wrong.

I doubt it though.

 

[Ken Snell \(MVP\)]

Here's the scoop:

Use a different query name each time you export to that same file. Then
you'll get a new spreadsheet tab in the EXCEL file with each export. For an
example of how you can do this using filtered "copies" of a single query,
see

Create a query and export multiple "filtered" versions of the query (based
on data in another table) to separate worksheets within one EXCEL file via
TransferSpreadsheet
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#FilterExportSameFile

Or, you may be able to use the Range argument in the export, though it's
undocumented and has many quirks. See

Using the Range Argument of TransferSpreadsheet when Exporting Data to an
EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ExpRange

 

[Anthony Lisanti]

Tried that, it just overwrites the previous data into a single tab.

I found a cool program to do it anyway. It helped me chip away at my
department budget.

 

[Ken Snell \(MVP\)]

I understand that you found a program, but for any others who read this
thread, if you use a different query name for each export, even if you
export to the same worksheet file, you'll get a different worksheet for each
query.