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?

Solution 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.

Solution 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

Solution 3

Here is formated and fixed version of above code. We don't need MSys tables in excel file and dbo prefix in sheet names. Export also can be made relative to MS Access db or fixed.

Here is code:

Sub exportTables2XLS()
Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String

out_file = CurrentProject.path & "\excel_out.xls" 

Set db = CurrentDb()
   For Each td In db.TableDefs
     If Left(td.Name, 4) = "MSys" Then
     '// do nothing -- skip
   Else
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
     td.Name, out_file, True, Replace(td.Name, "dbo_", "")
   End If 
   Next
End Sub