access-link-to-an-excel-spreadsheet-with-dao

Access - Link To An Excel Spreadsheet With DAO

This macro links to an Excel spreadsheet with DAO.

Access

  • 202
  • 89
  • 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!

! Please notice that the data cannot be edited from within the access database.


Mock data kindly provided by Mockaroo.

Advertisement

Code
Option Compare Database

'==========================================
' Link To Excel Spreadsheet With DAO
'==========================================
Sub linkToExcelDAO()
    ' Set variables
    Dim db As Database
    Dim tbl As TableDef
    ' Set database
    Set db = CurrentDb
    ' Set table
    Set tbl = db.CreateTableDef("tblCustomersExcel")
    ' Connect to excel sheet
    tbl.Connect = "Excel 12.0;DATABASE=F:\VBAmacros\Access\MOCK_DATA.xlsx"
    ' Set source sheet
    tbl.SourceTableName = "data$"
    ' Append table
    db.TableDefs.Append tbl
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!