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’
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.
VBA Tools To Create Folders In VBA, you can use MkDir function to create folders in your system or shared drive. To make your work simple, we bring a free Excel VBA tool to create…
Excel VBA Tool To Get File Properties Here is one more interesting VBA tool from the ExcelSirJi team. File Properties Tool is an Excel VBA tool that gets the following properties of the file. File…
In MS Access, the best way to create a multiuser tool is to divide your solution. One part acts as interface and other one acts as database. You can have multiple copies of the interface distributed to users which are connected to central MS Access database saved at common shared drive. To connect the interface to database, you can use link table feature (Access>External Data>Import & Link) available in MS Access. Below is a commonly required VBA code which helps the developers to re-link MS Access linked tables when the database is renamed or moved to other location
This tutorial shows you three easy ways to add hyperlinks in Excel. You will learn how to insert, change, and remove hyperlinks in your worksheets. It also explains how to fix links that don’t work.
Hyperlinks are often used on the internet to move between websites. In Excel, you can create links like that too. You can make a link to another cell, a different sheet, or even another workbook. You can also link to open a new Excel file or start an email message. This guide will show you how to do all of this in Excel 2016, 2013, 2010, and older versions.
Working with huge data is always exciting and challenging. From 2007 version onward, Excel is supporting more than a million rows in each worksheet. One of the common problems with huge data is “Duplicates” and the bigger problem is to identify and remove these duplicates. In this article, we will be sharing 4 ways to delete duplicate records from your data.
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 🙂