How to Export Access Data to Excel using VBA Code?

Creating a VBA tool in MS Access is always better as compare to MS Excel. MS Access provides better user interface and ability to handle multiple users. Still people prefer to pull and see the reports in MS Excel. Below VBA code helps you to export MS Access data into MS Excel.

Public Function ExportToExcel()
    'Variable declaration
    Dim strQuery As String
    Dim lCounter As Long
    Dim rsRecordset As Recordset
    Dim objExcel As Object
    Dim wkbReport As Object
    Dim wksReport As Object
    'Create new excel file
    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set wkbReport = objExcel.Workbooks.Add
    Set wksReport = wkbReport.Worksheets(1)
    'Set the query
    strQuery = "SELECT * from tblDummyData"
    'Execute the query on the database
    On Error GoTo Error_Query
    Set rsRecordset = CurrentDb.OpenRecordset(strQuery)
    On Error GoTo 0
    'Add header in row 1 of Excel sheet
    For lCounter = 0 To rsRecordset.Fields.Count - 1
        wksReport.Cells(1, lCounter + 1).Value = rsRecordset.Fields(lCounter).Name
    Next
    'Export data to Excel sheet
    wksReport.Cells(2, 1).CopyFromRecordset rsRecordset
    'Auto fit Excel columns to adjust as per data
    wksReport.Cells.EntireColumn.AutoFit
    'Close the objects
    Set rsRecordset = Nothing
    Set wksReport = Nothing
    Set wkbReport = Nothing
    'Show the message to user
    MsgBox "Done"
    Exit Function
'Error handler if query does not execute
Error_Query:
    MsgBox "Error: " & Err.Description, vbCritical
    Exit Function
End Function

Export Access Data to Excel using VBA Code follow below steps:-

1. Open an MS Access file
2. Press Alt+F11
3. Insert a Module (Insert>Module) from menu bar
4. Paste the code in the module

5. We also need to create a dummy table using Create>Table Design menu

6. Now add few fields in the table and save the table with tblDummyData name

7. Add dummy data in the table

8. Now add a new form in MS Access using Create>Form Design menu

9. Change the following properties of the form
Auto Center:            Yes
Record Selectors:     No
Navigation Buttons: No
Scroll Bars:               Neither
Pop Up:                    Yes