How do I export 2 queries to a single worksheet in excel from access

Question

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?

Answer 1

For my first query I use

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"

For the second query I put it in a recordset

Dim rstName As Recordset
Set rstName = CurrentDb.OpenRecordset("qryNameSecond")

Then I just copy this recordset to the worksheet with CopyFromRecordset.

Dim objApp As Object, objMyWorkbook As Object, objMySheet As Object, objMyRange As Object

Set objApp = CreateObject("Excel.Application")
Set objMyWorkbook = objApp.Workbooks.Open("test1.xlsx")
Set objMySheet = objMyWorkbook.Worksheets("MyWorksheetName")
Set objMyRange = objMySheet.Cells(objApp.ActiveSheet.UsedRange.Rows.Count + 2, 1)

With objMyRange
 rstName.MoveFirst 'Rewind to the first record
 .Clear
 .CopyFromRecordset rstName
End With

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.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameFirst", "test1.xlsx", True, "MyWorksheetName"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qryNameSecond", "test1.xlsx", True, "MyWorksheetName2"

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.