Data Import & Export

How to Automatically Update Excel External Workbook Links by Date

Nimra Malik Sep 30, 2026 868 views

Question details

The user wants to automate the process of updating an external Excel workbook link used in a sales chart by simply entering a specific date into cell B4.

How to Automatically Change an Excel External Workbook Link by Date

Product: Excel
Device & OS: not provided
Scenario: Retrieving daily sales data from multiple external workbooks that are systematically named by date, to be consolidated into a main dashboard or chart.
Observed behavior: Currently, the workbook link is being manually updated every day to match the new date's file name, and the goal is to make this data retrieval dynamic based on user input.

Before you start

Ensure all your daily external workbooks follow a strict, predictable naming convention (e.g., 'Sales_20231001.xlsx') and are saved in the exact same folder directory.

Solution 1Recommended

Use a VBA Macro for Closed Workbooks

Create a reliable automated script to update workbook links without needing to open the source files.

Using Visual Basic for Applications (VBA) is the most robust way to dynamically update external links, especially because it works perfectly even when the target external workbooks are closed.

Open the VBA Editor

Press Alt + F11 to open the Visual Basic for Applications (VBA) editor in your main Excel workbook.

Add a Worksheet Change Event

In the left Project Explorer pane, double-click the sheet name containing cell B4. Select 'Worksheet' from the left dropdown and 'Change' from the right dropdown to generate the event code.

Write the link update code

Add a condition: If Target.Address = "$B$4" Then. Inside the condition, construct your new file path string using the date in B4. Use the ActiveWorkbook.ChangeLink method to replace the old link with the new dynamically generated string.

Save as a Macro-Enabled Workbook

Click 'File' > 'Save As' and choose 'Excel Macro-Enabled Workbook (*.xlsm)' to ensure the macro runs automatically when the date changes.

Enable Macros: Ensure that macro execution is enabled in your Excel Trust Center settings so the Worksheet_Change event can trigger successfully.

Solution 2

Use the INDIRECT Function to Build Dynamic Links

Quickly construct dynamic links using a formula, best suited for when the external source workbooks remain open.

Solution 3

Use Power Query for Dynamic Data Retrieval

Create a robust data connection that handles dynamic paths and updates external links seamlessly upon refresh.

Efficient Spreadsheet Management

Automate and Manage Your Data Seamlessly with WPS Office

WPS Office provides a highly compatible spreadsheet tool that supports advanced formulas, dynamic data referencing, and VBA macros, allowing you to automatically update external workbook links without hassle.

1. Open your spreadsheet in WPS Office: Launch WPS Spreadsheet and open your main chart workbook along with your external data sources.
2. Apply dynamic formulas: Use the INDIRECT function integrated seamlessly within WPS to reference your date cell and dynamically build the target file paths.
3. Utilize WPS Macros for automation: If you are working with closed workbooks, activate the Developer tab in WPS Spreadsheet to write and run macro scripts that automate link updating based on cell changes.

Fully compatible with Microsoft Excel workbook formats (.xlsx, .xlsm, .xls)Supports advanced referencing functions like INDIRECT and TEXTBuilt-in VBA editor (in applicable versions) to fully automate dynamic link updatesLightweight application that handles complex data processing smoothly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my INDIRECT formula return a #REF! error when linking to an external workbook?

The INDIRECT function requires the external workbook to be open in Excel. If the source workbook is closed, the application cannot resolve the dynamic text string into an active cell reference, resulting in a #REF! error. To fix this, you must either keep the source file open or switch to a VBA or Power Query method.

Can I automatically change external links based on a date without using macros?

Yes, you can use Power Query to pull data dynamically from external workbooks without needing VBA. By setting the date cell as a parameter in Power Query, the data connection updates when you hit 'Refresh All'. Alternatively, you can use the INDIRECT function if the source files remain open.

How do I ensure my date cell format matches the external file names?

You should use the TEXT function within your formula or VBA code to standardize the date format. For instance, using TEXT(B4, "yyyymmdd") ensures that a date entered as 10/01/2023 is formatted exactly as '20231001', which will seamlessly match a file named 'Sales_20231001.xlsx'.

Excel & Data Problems