VBA Code to Export Access data to Excel

VBA Code to Export Access data to Excel

Table of Contents

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.


Export Access Data To ExcelPin

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()

Export Access Data To Excel

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]).

Export Access Data To Excel_AdodbPin

I’m sure you work on lots of Excel data, MS Access and VBA, so I Strongly Recommend you to Join the below Ultimate Excel VBA Userform with Access Database! Its a worth Investment in Yourself! DO IT NOW!

Build Your Own Real World Program, Learn Real Excel VBA(Macros), Exceed The Limits of Excel with Complete Project

Anson Antony

Anson Antony

Anson is a contributing author and founder at ASKEYGEEK.com. Learning anything new has always been his passion, ASKEYGEEK.com is an outcome of his passion for technology and business. He has got a decade of versatile experience in Business Process Outsourcing, Finance & Accounting, Information Technology, Operational Excellence & Business Intelligence. During the tenure, he had worked for organizations like Genpact, Hewlett Packard, M*Modal and Capgemini in various roles and responsibilities. Outside business and technology, he is a movie buff who spends hours together watching and learning Cinema and a Film Maker too!

3 Responses

  1. Thank you for the vba code. But it worked for once. I’m getting the following error “Error no. 3027. Cannot update. Database or object is read only

  2. I want to append to the spreadsheet monthly. Data has to go into specific format. I thought there is command that has row/column arguments. Once used a workspace to loop through records stepping the cell address. was I dreaming? Thanks

Leave a Reply

Your email address will not be published.

Launching soon...

E-book price at Amazon will be $14.99 

A Practical Guide To Online Business
Congratulations!
You Made It,
Don't Close!

Enter your chance to win our Premium Membership License for FREE! ( Normal Price $199/Year)

Thank you for your visit!

This popup won’t show up to you again!!!

Premium Membership Giveaway - Popup
small_c_popup.png

Let's have a chat

Fill out the form below to consult with us

*Please check your Inbox or Junk Folder for the Confirmation Email. You must confirm your Company email address before we could revert.

Share to...