Export from Access to Excel, add worksheet to existing Workbook

Question

I would like to automate a process of exporting data from Access query to Excel workbook. Each query relates to a batch. Each worksheet in a workbook relates to a batch. For example: Excel workbook called Jun2019.xlsx, might have batch1 relating to the worksheet named batch1. Then save each workbook into a different file path.

I am looking for some guidance, whether it can be done via Access VBA or would need a separate step to export xlsx from access and run procedure in Excel?

You can do this from either Access or Excel, just a matter of your own preference. Either way, put the query's results in a Recordset object, and then output the Recordset into an Excel worksheet. If you run into a specific problem, edit your question to include code you've tried. — tigeravatar
For ideas review accessmvp.com/KDSnell/EXCEL_Export.htm#ExpCopyFromRst — June7

marlan marlan · Accepted Answer · 2019-07-02T09:51:08

Working from Access VBA your code should look something like this.

Please note where some actions are done by Apxl object, while others must be done by the object's Application property. I have found this issue vital in various Excel actions done from MS Access.

First create an Excel objet:

dim Apxl as Object, xlWorkbook as Object ,xlWorksheet as Object
Set Apxl = CreateObject("Excel.Application")

Next If the Workbook (the file) exists already, open it:

ApXl.Workbooks.Open("C\:MyPath\MyFileName.xlsx")

Or create a new one:

Set xlWorkbook = Apxl.Application.WorkBooks.Add 
xlWorkbook.SaveAs "C\:MyPath\MyFileName.xlsx"

Now you can approach the Worksheat Object by it's name, or ordinal number (startin by 1 ):

Set xlWorksheet = xlWorkbook.Worksheets("MySheetName")
Set xlWorksheet = xlWorkbook.Worksheets(1)

If sheet does not exist, you can create a new one:

xlWorkbook.Worksheets.Add

The new worksheet will be added to the beginning of the collection.

You can also rename the WorkSheet:

Set xlWorksheet = xlWorkbook.Worksheets(1)
xlWorksheet.Name = "MyNewName"

Export from Access to Excel, add worksheet to existing Workbook

1 Answers