Exporting data to excel

[Anthony Lisanti]

I have an access database that breaks a table up and dumps it to 3
spreadsheets. I need to make 1 spreadsheet with 3 tabs, not 3
different spreadsheets. Can this be done with a macro?

Thanks

Tony

 

[Jeanette Cunningham]

hi Anthony

when you use TransferSpreadsheet to export a query without specifying a
worksheet name in the Range argument, and the workbook already contains a
worksheet with the same name as the query, the new data will replace the
data on the existing sheet. If you want to export a query with the same
name multiple times so it creates multiple worksheets in the same
workbook, you have to specify the worksheet name each time you call
TranferSpreadsheet (or change the name of the query each time).


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Anthony Lisanti]

So, what you're saying is if I have a template, with tabs that say
"Affiliate, Domestic, International" the queries from each of those
names will populate the tabs, with the same name, right?

 

[Jeanette Cunningham]

Did you try it? What did you find?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Anthony Lisanti]

I dont see the option in the macros Transferspreadsheet. Is this a VB
function?

 

[Jeanette Cunningham]

All the options are there in the macro - transfer type, spreadsheet type,
table name, file name and range. If it doesn't work for you, which is the
bit that doesn't work?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia