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’
In this article we will learn about VBA code to get computer name. Excel VBA, or Visual Basic for Applications, is a programming language that can be used to automate tasks within the Microsoft Excel…
In this article we are going to show you how you can delete file or files using a single line of VBA code. 1. Delete a specific file from the folder, 2. Delete specific type of files from the folder, 3. Delete all files from the folder
Outlook Bulk Email Tool is an Excel and Outlook based tool which helps you to send or draft email in bulk right from Excel. It reads the recipient details from Excel sheet and uses Outlook installed on your system to generate emails. The tool supports To, Cc, Subject, Email Body, Attachment, HTML Table in Email Body.
Here we are coming with one more exciting post which can help you to solve very basic but very important problems while writing VBA codes.
Merge Excel Files From last few months, we have been receiving frequent requests from users for a VBA tool which can consolidate Excel files from a folder. So here we come with one more free…
VBA code that will sum cells by its color through excel function. This code will really help in making the analysis and presentation better.
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 🙂