I'm using TransferSpreadsheet to export a query from access to excel and it works fine.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryName", "test1.xls", True
Now I've got another query and I want to add the data from this query to the same worksheet. How can I do this?
For my first query I use
For the second query I put it in a recordset
Then I just copy this recordset to the worksheet with CopyFromRecordset.
objApp.ActiveSheet.UsedRange.Rows.Count will return the last used row number. I added + 2 because I want an empty row in between the two queries.
To add I did a performancetest. I tested this method with 500.000 records. The table containing 500k rows, the first query containing 250k rows, the second query (with the OpenRecordSet) containing 250k rows. It took about 10 seconds to generate the excel file sheet and display the data on a E6600 (2,40 Ghz), 4GB ram machine with access/excel 2010.
EDIT:
Another way to accomplish the same would be with using TransferSpreadsheet 2 times.
This will create 2 Sheets in the workbook, then just copy the data of one worksheet to the other. I think the performance will be the same but not sure, I will stick with the OpenRecordSet.