Rapid Application Development / Advanced Excel Development

Excel External Link Manager

Link Manager by Codematic

This page discusses our approach to working effectively with external links whilst minimising the negative aspects.

Changing links

One of the big problems with external links is that it makes it difficult to move files around on the network. Links tend to be relative, unless they are across drives. This means that if either the source file or the destination file are moved the links can end up broken.

For simple cases where a workbook just links to 1 or 2 others, the standard Excel link tools are perfectly adequate, especially in later versions.

Edit>>Links in Excel 97-2003 and Data Tab>>Edit Links in 2007 (both disabled if the workbook has no links) launch the following dialog:

This dialog provides access to most link management features via the buttons on the right.

For complex linked workbooks, or large scale network changes (eg to a more controlled structure as part of an access rights revamp) a more automated link management approach is useful.

The link manger link report lists all Excel links split between file path and file name. This makes it simple to change either the path or the name or both. Once all changes have been made in the table, click update and all the links in the original workbook are changed in one go.

Externally Linked Formulas (and other things)

One frequent question/complaint about links is 'How do I find which cells are linked to external workbooks?'

Codematic Link Manager finds them all, not just those in visible formulas, hidden sheets, hidden cells, defined names, charts, drawing objects, everything is searched for possible links to external workbooks. Double click the cell address to look at the formulas or object in place, click the green arrow in the toolbar to come back to the report.

(This feature is only available in the commercial version)

Breaking External Links

With no tools to help an unexpected External Links warning can be a real headache.

People are often advised to use the break links feature from the Excel Edit Links dialog to remove these unwanted links. That is very dangerous advice.

Breaking a link overwrites the formula with its value. It is essential to the integrity of the model to ensure it is safe and appropriate to do that. Many times that is the exact wrong thing to do. If a link has been picked up accidentally it is important to find where it is, and what it should be. Often it should be linked to a cell in the current workbook, rarely should it be blindly replaced with its current value - especially if that is currently an error.

For this reason we recommend using the Link Manager to find all the links and then deal with each as appropriate. The Link Manager can be used to point external links back to the current workbook by setting the replacement path and name to those of the current workbook.

The linked workbook report can also be useful documentation to understand which workbooks the current one depends on. Some clients have inserted the report sheet into the workbook to which it refers as a form of documentation.

You can dopwnload the free demo version of the Codematic Link Manager here.