Data Import & Export

Export Multiple Access Tables to One Excel Workbook with VBA

Amos GikundaAmos Gikunda Nov 18, 2026 871 views

Question details

Export several Microsoft Access tables into one Excel workbook, with each table on a separate worksheet.

Export Multiple Access Tables to One Excel Workbook with VBA
Product
Microsoft Access and Microsoft Excel
Device & OS
not provided
Scenario
A user wants to automate a multi-table export from an Access database using VBA.
Observed behavior
The desired result is a single Excel workbook containing a separate worksheet for each exported table.
Before you start

Save the Access database and confirm the table names in the Navigation Pane. Make sure you can write to the database folder, or choose another writable output path.

Solution 1Recommended

Export selected tables with TransferSpreadsheet

Use the same output filename for each table so Access adds the exports to one workbook.

1
Open the VBA editor

Open the database in Microsoft Access, press Alt+F11, and select Insert > Module.

2
Add the export procedure

Paste this code into the module: Sub Export2Excel() Dim strFile As String Dim varTable As Variant strFile = CurrentProject.Path & "\Export.xlsx" For Each varTable In Array("TblEmployee", "TblQuotation", "TblCustomer") DoCmd.TransferSpreadsheet TransferType:=acExport, _ SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _ TableName:=varTable, FileName:=strFile, HasFieldNames:=True Next varTable End Sub

3
Set the tables and output path

Replace the names in Array(...) with your Access table names. Keep CurrentProject.Path to save Export.xlsx beside the database, or replace the path with another writable location.

4
Run and check the export

Place the cursor inside Export2Excel and press F5. Open Export.xlsx from the output folder and check that each table appears on a separate worksheet.

Use a new output filename when testing: Choose a filename that does not already exist if you want to inspect a fresh export without affecting an existing workbook.
Free Microsoft Office alternative

Review exported Access tables in WPS Spreadsheets

After running the export in Microsoft Access, open the resulting workbook in WPS Spreadsheets. WPS Office is a free, lightweight Office alternative with a familiar interface and Excel format compatibility for an easy move between applications.

Review the exported table worksheets in one workbookOpen and edit Microsoft Excel XLSX workbooksNavigate spreadsheets using a familiar interfaceUse a free, lightweight Office alternative
microsoft office alternative - wps office

Frequently Asked Questions

How do I export different Access tables to the same workbook?

Call DoCmd.TransferSpreadsheet once for each table and use the same FileName value in every call. The example loops through table names in an Array.

Where does the example save the Excel workbook?

The code saves Export.xlsx in the same folder as the saved Access database using CurrentProject.Path. Change strFile in the VBA module to use another writable location.

Will the exported worksheets include column headers?

Yes. The example sets HasFieldNames:=True in DoCmd.TransferSpreadsheet, so the exported worksheets include field names as headers.

Can WPS Office run the Access export procedure?

Run the Access VBA procedure in Microsoft Access. You can then open the exported XLSX workbook in WPS Spreadsheets to review or edit the worksheets.