access-import-data-from-excel-spreadsheet-into-access-database-with-docmd

Access - Import Data From Excel Spreadsheet Into Access Database With DoCmd

This macro imports data from MS Excel spreadsheet into MS Access Database with DoCmd.

Access

  • 158
  • 91
  • 0
  • 0
Add to collection
© VBAmacros.net2020

1 Open MS Access.

2 Create a blank database.


3 Go to Database Tools tab > click on "Visual Basic" button or hit Alt + F11.


4 Go to Insert tab > click on "Module" or hit M.


5 Copy the VBA code from below.

6 Paste the code in the newly created module.


7 Go to Run tab > click on "Run Sub/UserForm" or hit F5.


8 That's it!

Mock data kindly provided by Mockaroo.

Advertisement

Code
Option Compare Database
'==========================================
' Import Data From Excel With DoCmd
'==========================================
Sub importDataFromExcelDoCmd()
    ' Set variables
    Dim strTableName As String
    Dim strFileName As String
    Dim blnHasHeadings As Boolean
    ' Set data
    strTableName = "tblCustomers"
    strFileName = "F:\VBAmacros\Access\MOCK_DATA.xlsx"
    blnHasHeadings = True
    ' Import data
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, strTableName, strFileName, blnHasHeadings
End Sub
Advertisement


Comments
Sort by:
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
VBAmacros.net VBAmacros.net
Code was successfully copied!
VBAmacros.net VBAmacros.net
Please sign in!
VBAmacros.net VBAmacros.net
You've already voted!