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.

How to Export Access Data to Excel using VBA Code?
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
    'Export data to Excel sheet
    wksReport.Cells(2, 1).CopyFromRecordset rsRecordset
    'Auto fit Excel columns to adjust as per data
    '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
    MsgBox "Error: " & Err.Description, vbCritical
    Exit Function
End Function
How to Export Access Data into Excel using VBA Code?

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

10. Add a Button from Design menu

11. Change the following properties of the control
Name:     cmdExport
Caption:  Export Data into Excel

12. Create an Event Procedure of On Click event

13. Click on ‘…’ to create the procedure in VBA screen

14. Add the following code in the click event procedure
Call Module1.ExportToExcel

15. Done, now right click on the form and select Open

16. Click on the ‘Export Data into Excel’

Download Practice File

You can also practice this through our practice files. Click on the below link to download the practice file.

Recommended Articles

Similar Posts


  1. Sheng Tan says:

    I got an error about Data Type Mismatch

    1. Hi Sheng,

      The error seems to be related to the query you are trying to Export. Please check your query to resolve the issue.

      ExcelSirJi Team

  2. This is a great and useful function.

Leave a Reply

Your email address will not be published. Required fields are marked *