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’
How to use VBA to open Workbook in Excel? There are few VBA codes which are commonly used by every developer. One of them is giving an option to user to browse a file. Below is a…
Have you ever got into situation in office where you need to count the cells in Excel sheet with specific color? If yes then you can use following code which counts the number of cells…
To ensure that your VBA project works smoothly without any error, you need to put lot of error handlers and validations. Here is one of them, below code can be used to check if the given path is valid or not. You just need to provide the path of the file to the function and it will check if the file exist at the location. If it returns True that means the file is available at the path, if it returns False that means it is invalid path or the file does not exist at the location.
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.
Custom Calendar Control for MS Access MS Access by default provides inbuilt functionality to pick dates using calendar control; however it lacks few basic functionalities which makes selecting a date bit difficult. For example, if…
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 🙂