How to export all tables from an Access Database into Excel - A sheet for each table

Question

I have an Access database with ~30 tables.

How can I export all 30 tables into separate sheets in an Excel workbook?

I'm hoping to find some VBA/VBS code which I can run from within Access to accomplish this task.

Any ideas?

Answer 1

You should be able to do something like this:

Dim tbl as Tabledef
For Each tbl in Currentdb.TableDefs
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, tbl.name, "PathName.xls", True, tbl.name
Next

The second tbl.name is the worksheet name.