Access to Excel Workbook add a Spreadsheet from an Access Query

militarydonut 1 Reputation point
2021-10-20T15:56:40.443+00:00

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

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
731 questions
No comments
{count} votes

1 answer

Sort by: Most helpful
  1. DBG 2,301 Reputation points
    2021-10-20T18:42:38.293+00:00

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

    No comments