Codematic Excel developers logo
Codematic spreadsheet-excel development image

Rapid Application Development / Advanced Excel Development

 
Products for Excel
Commercial Products:
  - Classic Ribbon
  - Alt-FileSearch
  - Password Remover
Spreadsheet Quality Products
Free Products
Excel Development
Excel Development Index
Excel VBA
- VBA IDE (editor)
- VBA Training
- VBA Best Practice
- VBA Performance
- COM Performance
- VBA Security
Excel and Databases
Excel and Pivot Tables
Excel Add-ins
Worksheet Functions
Excel and xlls
Excel (in)security
Excel testing
Excel and .net
Excel External Links
Excel Developer Types
Professional Excel Development
Excel 2007
Excel 2010
Excel Development Archive
Spreadsheet Services
Spreadsheet Development
Spreadsheet Migration
Spreadsheet Maintenance
Spreadsheet Review
Spreadsheet Management
Resources
Excel User Confs
Consultant Profile
Book Reviews
Links
Other
Site Map

Excel External Link Manager

Link Manager by Codematic

Externally linked formulas (formulas that link other workbooks outside the one that hosts the formula) are an extremely powerful feature. They are also dangerous and have certain limitations and drawbacks. For more information see the External Link backgrounder here.

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.

 

 

Upcoming Events:

25 January 2012 - UK Excel Developer Conference - London


Products for sale:

AltFileSearch

Office 2007 FileSearch replacement logo

New information about the missing FileSearch feature in Office 2007 and details of our pragmatic solution (Current price GBP 30.00)


wsUnprotector

worksheet password remover logo

Instant Excel worksheet protection remover and password recovery (Current price GBP 15.00)


Classic Ribbon Tab

classic ribbon for office 2007 logo

Add Excel 97/2000/2002/2003 compatible menu structure to Excel 2007
(Current Price GBP 10.00)


 

Products coming soon:

Link Manager

(Find and control external links in Excel Workbooks)

Due by Q1 2111.

XLAnalyst Pro

(Excel VBA based spreadsheet auditing tool)

Due before the end of 2111.

  ;-)
This page was last reviewed on December 21, 2011

©Codematic Ltd 1999-2011