Export Access to multiple Excel sheet using TransferSpreadsheet

Question

I have multiple related queries that I want to filter and export to a single excel file with multiple sheets.

Using TransferSpreadsheet and a do loop, for each query, I was able to creat a excel file with multiple sheets.

I'm wondering if it's possible to export all queries to the same excel file created from exporting query1, and so on.

In another word, to have one excel file with multiple sheets (roughly 140 in my case). And for each sheet I will have the data from query1 in cell $a1:m10, data from query2 in cell from $a12:m23, and so on. Is this possible?

I tried to use the Range feature but received an error message because the range already exists. Is there a way to overwrite the range since there's actually no information in those cells? Or I should go with a different approach?

Many thanks!

Answer 1

Thank you for your help. I did try to creat a union query but gave up in the end because I have too much quries and the data columns are not really the same in all queries.

And no, I dont want the same data on 140 worksheets. Basicly what's going on is there are ~140 reports, each report has many sections and sub-secions. Data for each section/sub-section is stored in a query. Now I'm trying to push all data for each report to a different sheet in one workbook.

Answer 2

If the data columns are the same in all queries or could be made exactly the same, you could create a UNION ALL query and export that.

What do you mean, you want the same data on 140 worksheets?

Answer 3

TransferSpreadsheet doesn't allow for range exports.  For this purpose you can take something like the code I have here:

http://www.btabdevelopment.com/ts/default.aspx?PageId=49

And modify it to pass it a range for output.  Take a look and see if you can do that, or someone else might have a better idea.

Answer 4

Thank you, Bob!

Your code works great for me after some modifications except it takes a lot longer than Transferspreadsheet. Thank god i onlt need to do this once a year.