Export All tables in Access to Separate Workbooks

If you want to export each table of access database to new excel workbook and save workbook with table name

Download the file from below link -



Here is the code-


Option Compare Database
Private Sub Command0_Click()
'tool -> reference -> Microsoft Office 12.0 Object Library
Dim fldpth As String
'it will open the dialogbox and choose folder in which all workbooks are saved.
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
fldpth = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"

Me.Text21 = fldpth
End Sub
Private Sub Command23_Click()

Dim TblName As String
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllTables
TblName = obj.Name
If Not (Left(TblName, 4)) = "MSys" Then
' it will export your access table to excel
DoCmd.TransferSpreadsheet acExport, , obj.Name, Me.Text21.Value & obj.Name & Me.Combo19.Value, True
End If
Next obj
End Sub


Access Macro File- http://www.filefactory.com/file/cc91dda/n/Export_all_table_from_access_to_excel_to_seperate_workbboks.accdb