VBA Code to Export Access Recordset to Excel

learn advanced excel

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.

Excel Tricks

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

Excel Tips

To use this code in your MS Access tool, 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

learn excel

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

excel online courses

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

Microsoft excel tutorial

7. Add dummy data in the table

Excel course online free

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

Online Excel Training

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

Free Excel Course

10. Add a Button from Design menu

Learn Excel Online

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

Excel Classes Online

12. Create an Event Procedure of On Click event

Excel Tips

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

MS Excel Tutorial

Excel Videos

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

Free Excel Tricks

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

Free Excel Tips

16. Click on the ‘Export Data into Excel’

Excel Tricks ExcelSirJi

Excel Tips ExcelSirJi

Comments
  • Sheng Tan says:

    Hi,
    I got an error about Data Type Mismatch

    • Hi Sheng,

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

      Regards
      ExcelSirJi Team
      Kailash

Leave a Reply

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