You MUST change the name of the query/table to get a new worksheet.
Executing the following code results in an Excel workbook with 2 worksheets:
Code:
Public Sub TestTranfr()
Const strFileName As String = "TestExport"
Dim strSQL As String
Dim strTemp As String
Dim xlsxPath As String
xlsxPath = "F:\Forum\" & strFileName & ".xlsx"
strTemp = "qrytesting"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strTemp, xlsxPath, True
strTemp = "Query1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strTemp, xlsxPath, True
End Sub
Of course, they are saved queries.
The link I provided in Post #5 shows how to use a loop to create multiple queries to create multiple worksheets in one workbook.
It does require modification to meet your needs though.....
Without seeing your full code, this is the best I can do.