VBA & Macro Problems

Export Each Access Table to a Separate Excel Workbook with VBA

Adam DavisAdam Davis Nov 20, 2026 870 views

Question details

Export each Microsoft Access table to a separate Excel workbook using VBA.

Export Each Access Table to a Separate Excel Workbook with 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.
Before you start

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.

Solution 1Recommended

Use a Table-Specific Filename in Access VBA

Loop through Access tables and use each table name to create a separate .xlsx 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 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

3
Set the output folder

Change the outputFolder value to your existing destination folder, keeping the final backslash. Each workbook will use its Access table name as its filename.

4
Run and check the export

Place the cursor inside ExportTablesToExcel and press F5. Open the destination folder and check the exported .xlsx files.

Use a Table-Specific Filename in Access VBA
Existing files are skipped: The procedure excludes tables whose names begin with MSys and skips any table whose output workbook already exists. It also attempts to export linked tables; ensure they are accessible. Table names containing invalid filename characters must be renamed or converted to valid filenames first.
Free Microsoft Office alternative

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.

Open and review individual workbooks exported from AccessCompatible with Microsoft Excel .xlsx and .xls formatsUse a familiar spreadsheet interface for an easier transitionWork with a free, lightweight office suite
microsoft office alternative - wps office

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.