Exporting Access Information into Multiple Excel Tabs Using a Macr

[rano126]

I have set up a macro to export a bunch of different queries to excel. Is
there a way to have those 6 different queries export to different tabs in the
same worksheet. I can only get it to export to different workbooks.

 

[bhicks11 via AccessMonster.com]

Not with a regular export. Maybe something with automation?

Bonnie
http://www.dataplus-svc.com

 

[Steve Schapel]

Rano,

By "different tabs in the same worksheet", I can only suppose you mean
different worksheets in the same workbook. Yes?

You can't do this using the OutputTo action. But with
TransferSpreadsheet action, you can achieve this by an entry in the
Range argument, using this syntax:
Sheet1!A1:A1

 

[bhicks11 via AccessMonster.com]

Thanks Steve - didn't know that.

Bon
http://www.dataplus-svc.com

 

[Ken Snell \(MVP\)]

Rano,

By "different tabs in the same worksheet", I can only suppose you mean
different worksheets in the same workbook. Yes?

You can't do this using the OutputTo action. But with TransferSpreadsheet
action, you can achieve this by an entry in the Range argument, using this
syntax:
Sheet1!A1:A1

Or, more preferably, by exporting differently named queries in the macro.
Each unique query name will go to a different worksheet in the workbook.

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

Ken Snell
<MS ACCESS MVP>