Access to Excel Workbook add a Spreadsheet from an Access Query

Question

I am trying to export a query from Access and create an Excel file with a spreadsheet named (name it) and then take another query and add it to the same workbook and add a spreadsheet and name it and add that query to the prenamed worksheet.

Dim Apxl As Object, xlWorkbook As Object, xlWorksheet As Object
Set Apxl = CreateObject("Excel.Application")

Set xlWorkbook = Apxl.Application.WorkBooks.Add
xlWorkbook.SaveAs "\FileLocation\ExportedFiles\TrainingTEST.xlsx"
xlWorkbook.Worksheets.Add
Set xlWorksheet = xlWorkbook.Worksheets(1)
xlWorksheet.Name = "Train1Q"
xlWorksheet.Name = "Train2Q"

Dim sFileName As String
sFileName = "\FileLocation\ExportedFiles\TrainingTEST.xlsx"
'DoCmd.OutputTo acOutputQuery, "qrySIPR", acFormatXLSX, sFileName, Autostart:=True
On Error Resume Next
DoCmd.OutputTo acOutputQuery, "qryTrain1Q", acFormatXLSX, sFileName

Answer 1

Hi. Rather than using the OutputTo method, try using the TransferSpreadsheet method instead. Pay close attention to the Range argument.