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.

Answer 2

Here's the full module I used.

Sub expotT()
 Dim td As DAO.TableDef, db As DAO.Database
 Set db = CurrentDb()
 For Each td In db.TableDefs
    If Left(td.Name, 4) <> "msys" Then
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
    td.Name, "C:\xExcelTables.xls", True, td.Name
    End If 
Next
End Sub