How to Change a Linked Excel File in Access Using VBA
Question details
The user wants to programmatically change the file path of a linked Excel table within a Microsoft Access database using VBA, without using the Linked Table Manager.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Reconnecting a linked Excel table (XLSX) to a new file location dynamically using VBA code.
- Observed behavior
- The user needs to update the DAO TableDef Connect property and refresh the table link to point to a different Excel spreadsheet file.
Verify the exact file path of your new Excel (.xlsx) file and ensure you have a backup of your Access database before executing any new VBA code.
Use DAO TableDef to Update the Connect Property
This method updates the linked table's connection string programmatically using Data Access Objects (DAO), offering a reliable way to avoid automating the Linked Table Manager.
By modifying the TableDef.Connect property and calling the RefreshLink method, Access seamlessly connects to the new Excel file. This approach is highly efficient for databases that need to dynamically switch source files at runtime.
In Microsoft Access, press ALT + F11 to launch the Visual Basic for Applications (VBA) editor.
From the top menu, click on 'Insert' and select 'Module' to create a blank script window.
Type the variable declarations to handle your database objects: Dim db As DAO.Database and Dim tdf As DAO.TableDef.
Assign the current database and target table to your variables: Set db = CurrentDb and Set tdf = db.TableDefs("Sheet2") (replace "Sheet2" with your actual linked table name).
Set the new file path by modifying the connection string: tdf.Connect = "Excel 12.0 Xml;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=C:\Temp\Contacts.xlsx".
Apply the changes by running tdf.RefreshLink. Remember to release your objects by setting them to Nothing at the end of the script.
Manage Spreadsheets Efficiently with WPS Office
While managing linked tables in Access requires complex VBA scripting, editing and organizing your actual spreadsheet data shouldn't be difficult. WPS Office is a free, powerful alternative to Microsoft Office that provides excellent tools for all your spreadsheet tasks.
- 1. Visit the Official Website: Go to the WPS Office website on your preferred web browser.
- 2. Download the Installer: Click the 'Free Download' button to get the lightweight installation file.
- 3. Install and Open: Run the installer, open WPS Spreadsheet, and easily edit your XLSX files without any compatibility issues.
Frequently Asked Questions
Can I update multiple linked Excel tables at once using VBA?
Yes, you can loop through the db.TableDefs collection, check if the Connect property contains the word 'Excel', and dynamically update the file paths for all linked spreadsheet tables in a single VBA routine.
Why do I get an error when running the RefreshLink method?
Errors during tdf.RefreshLink usually occur if the new file path in the 'DATABASE=' string is incorrect, the Excel file is currently locked by another user, or the specified worksheet name no longer exists in the target file.
Do I need to add specific references to my VBA project for DAO?
Modern versions of Microsoft Access include the Microsoft Office Access database engine Object Library by default. If your script fails to recognize DAO objects, open the VBA editor, navigate to Tools > References, and ensure the 'Microsoft Office 16.0 Access database engine Object Library' (or similar version) is checked.
