Thursday, September 24, 2009

Export All Tables from MS Access Database ToExcel

Public Sub ExportAlltablesToExcel()
Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strFolder As String

strFolder = "C:\Folder\"

Set dbCurr = CurrentDb
For Each tdfCurr In dbCurr.TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
DoCmd.TransferSpreadsheet acExport, , tdfCurr.Name, _
strFolder & tdfCurr.Name & ".xls", True
End If
Next tdfCurr

Set tdfCurr = Nothing
Set dbCurr = Nothing

End Sub