Export Access to multiple Excel sheets using TransferSpreadsheet

[QEN]

Hello,

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.

In other words, 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!

 

[Alansidman]

Does this help to solve your issue?

http://accessblog.net/2006/07/export-to-excel-range.html

 

[sxschech]

Have a look here. http://www.accessmvp.com/KDSnell/EXCEL_Export.htm

The section "Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)" has this bit of code, which may work for your situation to specify where to start the pasting of data:

' Replace A1 with the cell reference into which the first data value
' is to be written
Set xlc = xls.Range("A1") ' this is the first cell into which data go

Also, further down, it talks about issues with named ranges "Using the Range Argument of TransferSpreadsheet when Exporting Data to an EXCEL File (VBA)"