Export Access data to Excel using VBA (with Images & Code)
What is it?
This VBA code helps you to export data in Access Table to Excel in one click.
Why is it?
You can upload or export Access data to Excel without even opening the Excel Workbook.
How to Export Access data to Excel
Step 1: Open the Access DB and got to VBA code builder (Alt + F11) and Open a New Module.
Step 2: Copy and Paste the below code in it.
Sub exportToXl() On Error GoTo ErrorHandler Dim dbTable As String Dim xlWorksheetPath As String xlWorksheetPath = "C:\" ‘Mention the xlWorkbook path xlWorksheetPath = xlWorksheetPath & "xlWorkbookName.xlsx" ‘Replace the ‘xlWorkbookName.xlsx’ with your workbook name dbTable = "tblMaster" ‘Replace ‘tblMaster’ with the table in the Access DB from which you want to export the data DoCmd.TransferSpreadsheet transfertype:=acExport, spreadsheettype:=acSpreadsheetTypeExcel12, tablename:=dbTable, FileName:=xlWorksheetPath, hasfieldnames:=True ErrorHandlerExit: Exit Sub ErrorHandler: MsgBox "Error No: " & Err.Number & ";Description: " & Err.Description Resume ErrorHandlerExit End Sub
Step 3: Click the Run button or F5 or the Command button to which you have associated this code!
In case you are assigning the code to a Command button make sure the code is within the Private/Public Sub_Event(); Something like the below
Private Sub exportToXl_Click()
Access DB records have been successfully uploaded to Excel, Open your Excel workbook and check the data.
Note: Make sure ‘Microsoft ActiveX Data Objects Library’ is enabled from the Tools – References (use latest version [6.1 as of this post]).