Jaa

External links in Excel files

  • Artikkeli

Topic

 

This is a post that contains a tool for analyzing the external links in an Excel Workbook.

The tool is intended to provide information regarding the linked workbooks, the cells from a workbook that contain external links, as well as the target worksheets and cells.

It will generate an XML report like this:

<?xml version="1.0"?>

<workbook name="Testing.xls" numberOfLinks="4">

  <link>XXXX.xlsm</link>

  <link>YYYY.xlsm</link>

  <link>ZZZZ.xlsm</link>

  <link>TTTT.xlsm</link>

  <oldLinks>

    <oldLink>C:\Test\Sites\XXXX.xlsm</oldLink>

    <oldLink>C:\Test\Sites\YYYY.xlsm</oldLink>

    <oldLink>C:\Test\Sites\ZZZZ.xlsm</oldLink>

    <oldLink>C:\Test\Sites\TTTT.xlsm</oldLink>

  </oldLinks>

  <reference toWb="XXXX.xlsm" fromSheet="Sheet1" toSheet="Sheet2">

    <cell from="$G$10" to="S$32"/>

    <cell from="$H$10" to="T$32"/>

…

The input workbook can be both XLS and XLSX/XLSM format (Office Automation is used).

You also have the option to replace the external links in a workbook by specifying a path where the target workbooks are located and then pressing the "Fix" button. This is very useful for changing the location of a workbook with lots of external links. (see https://support.microsoft.com/kb/328440)

To access all functionalities, open the file in Excel 2007 and choose the second tab from the ribbon. There is a single button there.

You can freely modify the VBA code (and I'll be very thankful if you provide feedback and/or bug reports).

Enjoy!

Download

 

AnalyseLinks.xlsm

Comments

  • Anonymous
    September 18, 2014
    Thanks very much... this came in very handy :)
  • Anonymous
    April 23, 2015
    Thank you for this!Works perfectly!