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
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’
VBA Code to Filter Data in Excel Here is an easy reference code which filters data in the sheet. In the code, we have filtered the data in three steps. Step 1: Remove existing filter from…
Random Rows Selector is an MS Excel based tool which can be used to pick random or stratified samples from a set of records available in the Excel. The tool is fully dynamic, it can support any data format in Excel.
Did you come across any requirement where you want the user to interact with a sheet only through VBA Form? Here is a simple code which can help you.
VBA Code to Find Last used Column or Row in Excel Sometimes as a developer, you need to take actions in Excel sheets based on last row or column. In Excel, there are two kinds…
VBA CODE TO HIDE MENU RIBBON IN MS ACCESS In MS Access, there are multiple ways to protect your code or tool from un-authorized access and edits. Some developers prefers to hide MS Access ribbons…
VBA Code to list Files in Folder To work on multiple files through VBA programming, you need to have VBA code that can list files in a folder. In this article we will learn three…
Thanks
Our pleasure 🙂
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
This is a great and useful function.
Thank you 🙂