19 Brindley Close
Oxford OX2 6XN
England
01865 552731

Link Manager

Purpose

The Link Manager utility is designed to help users or IT departments who want to move files which are referenced from Excel workbooks to another location.  For example, if network file structures are being re-designed and some files are to be moved from one server to another then links to those files from Excel workbooks would be broken.  In preparation for such a change Link Manager will scan a directory structure for Excel workbooks and identify where there are such links.  When the move is made the utility can be run again to correct the link sources. 

Installation

  • The current (prototype) version is 0.7g.  Download it here
  • Extract LinkMgr.xla from the zip file to a folder on your computer
  • You can choose whether to install Link Manager as an add-in so that it is available whenever you are running Excel or whether to just open it when you want to use it.
  • To install as an add-in, in Excel 2003 or earlier:
    • Tools > Add-Ins > Browse to where you saved LinkMgr.xla, Select LinkMgr.xla and click OK
  • To install as an add-in in Excel 2007
    • Office Button > Excel Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved LinkMgr.xla, Select LinkMgr.xla and click OK
  • To install as an add-in in Excel 2010
    • File > Options > Add-Ins > Manage: Excel Add-Ins > Go > Browse to where you saved LinkMgr.xla, Select LinkMgr.xla and click OK
  • To run it once without installing it as an add-in:
    • In Excel 2007: Office Button > Open > LinkMgr.xla
    • In other versions, File > Open > LinkMgr.xla
    • If you are warned about the presence of macros in the file, enable the content.

Use

  • LinkMgr adds a "Link Manager" menu item to the Tools menu in Excel (up to 2003) or in the Add-Ins ribbon (in 2007 and later)
  • Click the Link Manager menu item, and LinkMgr displays a dialog:
  • Select the options you want
    • The "Where do you want to look" checkboxes enable you to specifiy the usages you want to identify and potentially change.
    • "Files to look in" specifies with the use of wildcards the set of files to be scanned for links. 
    • "In directory" is the top level directory (or folder if you prefer) in which you want to scan the files.  You can optionally include sub-directories
    • "Changes" allows you to specify one or more text strings to look for and the strings that you want to use as replacements.  If you are just looking for one string (e.g. \\Server1\Folder1 ) you can specify it and the replacement directly in the dialog.
      For multiple changes you need to have previously created a 2-column table in a worksheet giving the strings to look for in the first column and the replacements in the second column.  You then select that table from the dialog:
    Part of the dialog with refedit box
    • The "Matching at start of text only" would normally be selected (to match "\\Server\Folder\Subfolder" for example), but you might want to match any occurrence of "\Folder\SubFolder" in which case you would unselect that option.
    • On the right side of the dialog you can specify passwords if you wish and select what you want Link Manager to do for you:
    • If you have workbooks that are password protected Link Manager will be unable to open them or to scan them completely.  If you check 'Ask for unspecified passwords' it will ask you to enter the password when it encounters such a workbook.  It remembers the passwords you have entered and tries them again if it finds another protected workbook.  If you know what passwords are likely to be needed you can enter them in the appropriate boxes on the dialog
    • In the Options section you have 4 choices:
      • "List without making changes" is a good starting point - it will list in a worksheet the occurrences it finds and that it would have changed if asked.
        It will also indicate any problems encountered (e.g. a workbook it couldn't open) in red.
      • "List occurrences and make changes" produces the same output as the previous option and also makes the requested changes. 
      • "List problems only and make changes" reduces the amount of output.  It would be wise to use this option only if you previously generated the list of changes that would be made.
      • "Make changes - no list".  It would be wise only to use this option if you had previously done a dummy run and knew exactly what was going to happen.
      • Make sure you have a backup before using any option that makes changes.  Or you can ask LinkManager to save a backup of any file it is about to change.
  • When you have selected the options you want and clicked OK, Link Manager searches the specified directory structure for files matching the file specification and tries to open each file in turn
  • It then looks for occurrences of the string(s) you have specified in the places you have asked it to look and optionally lists the occurrences and the effect of the change in a worksheet log.
  • At the end of the run it gives a report:

Warnings

  • If you are using the program to make changes you should make sure that there is a backup of the files that might be changed, in case of unforeseen consequences.
  • The program should be run in the most recent version of Excel that has been used to save any of the files. For example, if there are Excel 2007 format files (e.g. .xlsx or .xlsm) then the utility should be run in Excel 2007 or later.
  • If looking for references in VBA code you need to have trusted programmatic access to VBProjects. 
    • In Excel 2003 or earlier: Tools > Macro > Security > Trusted Sources > Trust Access to Visual Basic Projects
    • In Excel 2007: Office Button > Excel Options > Trust Center > Trust Center Setting > Macro Settings > Trust Access to the VBA project object model
    • In Excel 2010: File > Options > Trust Center > Trust Center Settings > Macro Settings > Trust Access to the VBA project object model
    • OR, in 2007 or 2010, if you have a Developer tab on the ribbon the same dialog can be reached by: Developer > Code: Macro Security
  • Changed workbooks may be saved with a different Calculation mode setting than they previously had.  This will only affect their behaviour if they are the first workbook loaded in an Excel session.

What's new?

  • Version 0.7g is signed using a current digital certificate and has an updated email address for help and problem reporting

Licensing

The program is provided as Freeware, for the use of the person downloading it and their organisation only.  It must not be resold or given to other organisations.  The software is provided without warranty. However we welcome your feedback.