Export Multiple Access Tables to One Excel Workbook with VBA
Question details
Export several Microsoft Access tables into one Excel workbook, with each table on a separate worksheet.

- 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.
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.
Export selected tables with TransferSpreadsheet
Use the same output filename for each table so Access adds the exports to one workbook.
Open the database in Microsoft Access, press Alt+F11, and select Insert > Module.
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
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.
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.
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.

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.




