External links in Excel files


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 http://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 (2)

  1. phill says:

    Thanks very much… this came in very handy 🙂

  2. Milan says:

    Thank you for this!

    Works perfectly!

Skip to main content