Linked Table Manager: Need MS Access VBA code to change the file location of MS Excel file

Question

I have a 2013 MS Access database with multiple users in different locations. I have several excel files as Linked Tables. The Excel files are located on a share drive so all users can access the information. The problem we are experiencing is the share drive is slow so it takes long periods for our reports to load.

I would like VBA code, when the user opens the database, the share drive Excel files are automatically downloaded to the their local profile desktop. Each user profile of course is different so the Linked Table has to know to change the person profile name: "C:\Users\" & Environ("Username")& "\Desktop\Reports". The Link Table Manager will not allow Environ("Username") so it will have to done likely in VBA.

June7 June7 · Accepted Answer · 2019-07-12T18:34:43

Code for changing link path to an Excel sheet. This requires a link with worksheet to have already been established and link object exists in db.

Sub ChangeExcelLink()
Dim Tdf As DAO.TableDef
Dim Db As DAO.Database
Set Db = CurrentDb
Set Tdf = Db.TableDefs("link table name")
Tdf.Connect = "Excel 12.0;DATABASE=C:\Users\" & Environ("Username") & _
     "\Desktop\Reports\filename.xlsx;HDR=NO"
Tdf.RefreshLink
Set Tdf = Nothing
Set Db = Nothing
End Sub

If worksheet has column headers you want to retain, change HDR=No to HDR=Yes or remove that argument because Yes is default. If database requires password, use ;PWD=yourpassword.

Linked Table Manager: Need MS Access VBA code to change the file location of MS Excel file

1 Answers