Download the file-
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 & Me.Text24.Value & Me.Combo19.Value, True, obj.Name
End If
Next obj
End Sub
Access Macro File - http://www.filefactory.com/file/cc94fc1/n/Export_all_table_from_access_to_excel_in_different_sheets_in_same_workbook.accdb
