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 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
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
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
15. Done, now right click on the form and select Open
16. Click on the ‘Export Data into Excel’
Download Practice File
Excel VBA Course : Beginners to Advanced
We are offering Excel VBA Course for Beginners to Experts at discounted prices. The courses includes On Demand Videos, Practice Assignments, Q&A Support from our Experts. Also after successfully completion of the certification, will share the success with Certificate of Completion
This course is going to help you to excel your skills in Excel VBA with our real time case studies.
Lets get connected and start learning now. Click here to Enroll.
Secrets of Excel Data Visualization: Beginners to Advanced Course
Here is another best rated Excel Charts and Graph Course from ExcelSirJi. This courses also includes On Demand Videos, Practice Assignments, Q&A Support from our Experts.
This Course will enable you to become Excel Data Visualization Expert as it consists many charts preparation method which you will not find over the internet.
So Enroll now to become expert in Excel Data Visualization. Click here to Enroll.
Use Automated Excel Utility Tools to increase productivity
Hope you are enjoying learning Excel with us, if you want any support related to this article, please do comment else you can ask questions in Excel Community
Our pleasure 🙂
I got an error about Data Type Mismatch
The error seems to be related to the query you are trying to Export. Please check your query to resolve the issue.
This is a great and useful function.
Thank you 🙂