Export Each Access Table to a Separate Excel Workbook with VBA
Question details
Export each Microsoft Access table to a separate Excel workbook using VBA.

- Product
- Microsoft Access and Microsoft Excel
- Device & OS
- not provided
- Scenario
- An Access database contains multiple tables that need to be exported as individual Excel workbooks.
- Observed behavior
- Each table should be saved in its own .xlsx file rather than combined in one workbook.
Create or choose an existing output folder and confirm you have permission to write files there. Check that the table names are valid Windows filenames and that the database contains the tables you want to export.
Use a Table-Specific Filename in Access VBA
Loop through Access tables and use each table name to create a separate .xlsx workbook.
Open the database in Microsoft Access, press Alt+F11, and select Insert > Module.
Paste this procedure into the module: Public Sub ExportTablesToExcel() Dim tbl As DAO.TableDef Dim outputFolder As String Dim outputFile As String outputFolder = "C:\Scope Walk\GSAP In Scope Extracts\" For Each tbl In CurrentDb.TableDefs If Left$(LCase$(tbl.Name), 4) <> "msys" Then outputFile = outputFolder & tbl.Name & ".xlsx" If Dir$(outputFile) = "" Then DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, tbl.Name, outputFile, True End If End If Next tbl End Sub
Change the outputFolder value to your existing destination folder, keeping the final backslash. Each workbook will use its Access table name as its filename.
Place the cursor inside ExportTablesToExcel and press F5. Open the destination folder and check the exported .xlsx files.

Review Exported Access Tables in WPS Spreadsheets
WPS Office is a free, lightweight alternative for opening and working with exported Excel workbooks. Its familiar interface and Excel format compatibility make it easy to move your spreadsheet work to WPS. The Access VBA export itself still requires Microsoft Access.

Frequently Asked Questions
Why are all my Access tables going into one workbook?
If every TransferSpreadsheet call uses the same output path, the exports target the same workbook. Include tbl.Name in the filename so each table has a different path.
How do I exclude Access system tables from the export?
In the TableDefs loop, check that Left$(LCase$(tbl.Name), 4) is not "msys" before calling DoCmd.TransferSpreadsheet.
Which spreadsheet type should I use for .xlsx files?
Use acSpreadsheetTypeExcel12Xml in the DoCmd.TransferSpreadsheet call for an Excel .xlsx workbook.
Why was a table not exported?
The example skips a table if its .xlsx file already exists. Also check that the output folder exists, the table name is a valid Windows filename, and any linked table is accessible.
Can WPS Office run the Access export procedure?
No. Run the VBA procedure in Microsoft Access, then open the resulting .xlsx files in WPS Spreadsheets.




