Import Multiple Tables from Access to Excel (VBA)

Introduction

This VBA sample illustrates how to import multiple Access tables to Excel worksheets at once, meanwhile each worksheet‘s name will correspond to each table's name. It can be run on Excel 2003, 2007 and 2010.

Scenarios

Users would like to import Access tables to Excel worksheets, meanwhile each worksheet's name will correspond to each table's name.

Script

Import the "mdlTransferAccessToExcel.bas" file.

Step1. Press Alt+F11 to open the VBE in Excel.
Step2. Drag the "mdlTransferAccessToExcel.bas" file to the Project Explorer (Press Ctrl+R if you cannot see it) or via File >> Import File... (Ctrl+M).

Run the Main macro to process an Access database file.

Step3. Go back to Excel UI, and then press Alt+F8 to open the Macros window.

Step4. Select "Main" in the names list, and then click the Run button.

Step5. Select an Access database file to open

Here are some code snippets for your references. To get the complete script sample, please click the "Download" button at the beginning of this page.

Visual Basic

Edit|Remove

' ################################
' The starting point of execution.
' ################################
Sub Main()
    
    ' /* Open the file dialog. */
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Title = "Open an Access Database file"
        
        With .Filters
            .Clear
            .Add "Microsoft Access Databases", "*.mdb,*.accdb"
        End With
        
        '/* The user pressed the button .*/
        If .Show = -1 Then
            Dim strAccessPath As String
            
            ' Get the path of the selected file.
            strAccessPath = .SelectedItems(1)
            
            Dim blnGetError As Boolean
            
            ' Call the function to process the selected file.
            blnGetError = TransferAccessToExcel(strAccessPath)
            
            If blnGetError Then
                MsgBox "Runtime error, please check to see if the problems listed below:" & vbNewLine & _
                       "1. ActiveX component (ADO) can't create object;" & vbNewLine & _
                       "2. Provider cannot be found. It may not be properly installed;" & vbNewLine & _
                       "3. The open database has a password.", vbCritical, "Error"
            Else
                MsgBox "Task complete.", vbInformation, "Tips"
            End If
            
        End If
    End With
    
End Sub

' ################################ 
' The starting point of execution. 
' ################################ 
Sub Main() 
     
    ' /* Open the file dialog. */ 
    With Application.FileDialog(msoFileDialogOpen) 
        .AllowMultiSelect = False 
        .Title = "Open an Access Database file" 
         
        With .Filters 
            .Clear 
            .Add "Microsoft Access Databases", "*.mdb,*.accdb" 
        End With 
         
        '/* The user pressed the button .*/ 
        If .Show = -1 Then 
            Dim strAccessPath As String 
             
            ' Get the path of the selected file. 
            strAccessPath = .SelectedItems(1) 
             
            Dim blnGetError As Boolean 
             
            ' Call the function to process the selected file. 
            blnGetError = TransferAccessToExcel(strAccessPath) 
             
            If blnGetError Then 
                MsgBox "Runtime error, please check to see if the problems listed below:" & vbNewLine & _ 
                       "1. ActiveX component (ADO) can't create object;" & vbNewLine & _ 
                       "2. Provider cannot be found. It may not be properly installed;" & vbNewLine & _ 
                       "3. The open database has a password.", vbCritical, "Error" 
            Else 
                MsgBox "Task complete.", vbInformation, "Tips" 
            End If 
             
        End If 
    End With 
     
End Sub

Note

1. Please make sure that macros are enabled in Excel.

For Excel 2010:

File >> Options >> Trust Center >> Trust Center Settings >> Macro Settings >> Enable all macros >> OK >> OK >> Restart Excel

For Excel 2007:

File >> Excel Options >> Trust Center >> Trust Center Settings >> Macro Settings >> Enable all macros >> OK >> OK >> Restart Excel

For Excel 2003:

Tools >> Macro >> Security... >> Security Level tab >> Low >> OK >> Restart Excel

2. It will overwrite all the data in the worksheet if its name is the same as the table name from Access database file.

3. Do not open an Access database file encrypted with a password.

Additional Resources

Related forum threads