Fixing and/or Removing Excel Links

Linking Formulas are one of the most useful features of Microsoft Excel. These formulas allow you to share or ‘re-use’ data contained in one workbook with another workbook. The following is a textbook example of a linking formula: =[Book2]Sheet1!A3. In English this translates to “Show me the text and/or value that is being displayed in cell A3 on Sheet1 in the Excel file named Book2.” In a network environment the name of the file can include the full path name to the file, I:\HR\Budget\2014\[Book2]Sheet1!A3, for example.

What happens, however, if someone moves the file to a new location, renames the file or any portion of the path, or worse, deletes the file? The short answer is that Excel displays an error message that your linking formula no longer works. How you fix the problem depends on what was done.

Starting with the 2007 version Excel installs with automatic updating of linking formulas turned off by default. If you get the following error message when opening a workbook containing linking formulas you will know the setting for you machine.

Links_SecurityWarning

Clicking on the Options button will display the following dialog box.

Links_SecurityWarning2

Obviously you have to check the lower radio button and then the OK button if you want to use linking formulas.

Assuming that you have automatic updating turned on, the following dialog box will appear whenever there is a problem with a linking formula.

Links_BrokenLinkWarning

If you click the Continue button the workbook will open normally, but the linking formula will not update. The linking formula will remain intact. You may want to choose this option if you are temporarily disconnected from the network. Clicking the Edit Links button will display the normal Excel File Open dialog box. You can then point to the new location for the file, or the new file name. The linking formula will be adjusted to the new location and/or name, retaining the same cell address.

In the worse case scenario, the file with the source data has been irretrievably deleted. In this situation you want to break the link, otherwise Excel will persist in displaying the previous error message each time that the workbook containing the linking formula is opened. To begin the process of breaking the link click the Edit Links icon on the Data tab of the ribbon.

Links_OnRibbon

This will display the following dialog box.

Links_EditLinks

Click the Break Link button, which displays the following warning.

Links_BreakingLinksWarning

As the warning states, this will convert the formula to its current value, similar to using Paste Special==>Values. Should you need the linking formula you would then have to re-create it once the source workbook is re-created.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd