The Way Most People Check Links
When opening a file with external links, we are presented with a banner notification stating that the file contains links to external data, and do we wish to allow those links to pull the content.
We can click the Enable Content button, allowing this to take place.
To check the links, where they point and their status, we can select Data (tab) -> Queries & Connections (group) -> Edit Links.
In the Edit Links dialog box, we see a list of the links with the following information:
- The source file that the link points towards
- The type of link (workbook or worksheet)
- How the link updates (automatically or manually)
- The status of the link (“Source is Open”, “OK”, “Warning”, “Unknown”, etc.)
Although the Source column displays the name of the linked file, you must click each link, one at a time, to see its location.
Generating a List of External Links
What if you could type a command like the following and generate a list of the links:
=GetLink
Using the Dynamic Arrays Calculation Engine, we are presented a horizontal list (spill range) of all external references.
Because this is difficult to read, we can orient the list vertically by wrapping the formula in a TRANSPOSE function.
=TRANSPOSE(GetLink)
This “trick” is brought to us by veteran contributor Bob Ulmas (Excel MVP – 25 years).
Bob combined the new, soon to be released Dynamic Arrays with the old Excel 4.0 Macros.
These “hacks” come from page 92 of Bob’s book, “This isn’t Excel, it’s Magic!”.
Working with Excel 4.0 Macros
Excel 4.0 was equipped with a library of fully-functional macros. Although these macros were introduced roughly 30 years ago, they remain present in all the Excel versions that followed.
Let’s look at two practical examples of combining Dynamic Arrays with Excel 4.0 Macros:
- Create a list of all external links in a file
- Create a list of all files in a specific folder
List All External Links
If we look at the following file, we see an example of a cell with a link to a cell in a different Excel file.
To utilize one of the built-in Excel 4.0 macros, we must create a name in the Name Manager that references the macro with a user-friendly name.
To generate a list of all external links in a file, perform the following steps:
- Select Formulas (tab) -> Defined Names (group) -> Name Manager
- In the Name Manager dialog box, click “New…”
- In the New Name dialog box, we will name the reference “GetLinks” and reference the LINKS macro by entering “=LINKS()” in the “Refers to” field.
- Click OK to close the Name Manager dialog box.
To create the list of external links, select a cell and enter the following formula:
=GetLinks
NOTE: For the above to work, you must be using the new calculation engine of Excel.
If you don’t have the new engine, you can use this formula instead: =INDEX(GetLinks,ROW(A1)) and then drag down. To avoid the #REF error, wrap it inside the IFERROR function.
As we witnessed earlier, to make the list easier to read, we can wrap the above formula inside the TRANSPOSE function to create a vertically-oriented list.
=TRANSPOSE(GetLinks)
This allows us to more easily keep track of our links and whether they are pointing to the correct workbooks.
Updating the Links
What happens if we add, update, or remove a link from the macro-generated list?
Because these are macros, they do not execute (update) unless requested.
To update the list of external links by initiating a full sheet recalculation, press the key sequence…
Generating a List of Files in a Defined Folder
To generate a list of all files in a defined folder, perform the following steps:
- Create a folder reference in a cell, such as the following in cell B3 on a sheet named Sheet1:
C:\Users\LG\Desktop\Confidential
- Select Formulas (tab) -> Defined Names (group) -> Name Manager
- In the Name Manager dialog box, click “New…”
- In the New Name dialog box, we will name the reference “GetFiles” and reference the FILES macro by entering “=FILES(Sheet1!$B$3)” in the “Refers to” field.
- Click OK to close the Name Manager dialog box.
If we select a cell and type the formula…
=GetFiles…it fails with a #N/A error message.
The reason this fails is that we need to supply the FILES macro with search parameters.
If we wish to list all files in a folder, we can add a “slash-asterisk” to the end of the path pointer in cell B3.
C:\Users\LG\Desktop\Confidential\*
The asterisk is a wildcard that denotes “any file”.
To get the list to read vertically, we wrap the formula in a TRANSPOSE function.
=TRANSPOSE(GetFiles)
NOTE: Remember to press CTRL-ALT-F9 to update the list.
If we want to see the results in sorted order, wrap the formula in a SORT function.
=SORT(TRANSPOSE(GetFiles))
If we wish to only see Excel workbooks, we can update the search parameter to “\*xlsx”.
C:\Users\LG\Desktop\Confidential\*xlsx
A Reminder About Saving Files
Because these files are using macros, you must remember to save the files with a “.XLSM” file extension for Macro Enabled Workbooks.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
