question

militarydonut-3429 avatar image
0 Votes"
militarydonut-3429 asked thedbguy answered

Access to Excel Workbook add a Spreadsheet from an Access Query

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

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

thedbguy avatar image
0 Votes"
thedbguy answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.