VBA: Create a list of all external data references in Excel

Use this simple Visual Basic for Applications script to list every external link within an Excel workbook.

Sometimes it’s necessary to link multiple Excel workbooks together in order to analyse data. The problem that this creates is that the two workbooks become thereafter inseparable. Should one file path or name change, the links become broken. Particular challenges also arise if the link to external data is difficult to locate, most particularly in larger data sets.

A dialogue box will usually appear when a workbook contains links to other sources:

The following VBA script will insert a new worksheet in an Excel workbook and list every instance where a link to external data exists. The script works by trawling every worksheet for .xl somewhere within a formula. In essence, the script will detect links to the following Excel file types:

• Excel workbooks (.xlsx)
• Excel 97-2003 workbooks (.xls)
• Excel binary workbooks (.xlsb)
• Excel macro-enabled workbooks (.xlsm)
• Excel templates (.xltx)
• Excel macro-enabled templates (.xltm)
• Excel 97-2003 templates (.xlt)

Instructions

Step 1

Open the Excel sheet that needs to be queried.

Step 2

Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.

Step 3

In the Visual Basic for Applications toolbar, go to Insert > Module

Step 4

Insert the following code into the module:

Option Explicit

Sub ExternalLinks()

    Dim Wks             As Worksheet
    Dim rFormulas       As Range
    Dim rCell           As Range
    Dim eLinks()        As String
    Dim Cnt             As Long
    Dim ws              As Worksheet

    If ActiveWorkbook Is Nothing Then Exit Sub
    
    'Deletes sheet if it already exists
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Sheets("External Links").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    
    'Checks cells for formulae that indicate external links (contain .xl)
    Cnt = 0
    For Each Wks In Worksheets
        On Error Resume Next
        Set rFormulas = Wks.UsedRange.SpecialCells(xlCellTypeFormulas)
        On Error GoTo 0
        If Not rFormulas Is Nothing Then
            For Each rCell In rFormulas
                If InStr(1, rCell.Formula, ".xl") > 0 Then
                    Cnt = Cnt + 1
                    ReDim Preserve eLinks(1 To 4, 1 To Cnt)
                    eLinks(1, Cnt) = ThisWorkbook.Name
                    eLinks(2, Cnt) = rCell.Parent.Name 'rCell.Address(, , , True)
                    eLinks(3, Cnt) = rCell.Address(, , , False)
                    eLinks(4, Cnt) = "'" & rCell.Formula
                End If
            Next rCell
        End If
    Next Wks
    
    'Create and format new worksheet
    If Cnt > 0 Then
        Worksheets.Add before:=Worksheets(1)
        Worksheets(1).Name = "External Links"
        Range("A1").Resize(, 4).Value = Array("Workbook", "Worksheet", "Cell", "Formula & Location")
        Range("A2").Resize(UBound(eLinks, 2), UBound(eLinks, 1)).Value = Application.Transpose(eLinks)
        Columns("A:D").AutoFit
    Else
        MsgBox "No external links were found within the workbook.", vbInformation
    End If
    
End Sub

Step 5

Press F5 to run the macro. A new sheet will be created, called “External Links”, which will contain a list of every external link in the workbook.

Comments

4 responses to “VBA: Create a list of all external data references in Excel”

On 18 July 2017, Marek wrote:

Very, very usefull & helpfull.
Jednym słowem: miodzio :-)
Big thxs!

Reply

On 10 July 2018, Mike wrote:

Beautiful. This code works fast and well! Thanks for posting it.

Reply

On 5 November 2018, Zainab wrote:

Thank you, this works great! How can I modify the code to have an additional column just for file name?

Reply

On 26 October 2023, Frapozze wrote:

Maybe could be usefull .. create a collection with all single links

Function GetExternalLinks() As Collection
Dim wSheet As Worksheet
Dim RngFormulas As Range, iCell As Range
Dim iFormula As String
Dim cIni As Integer
Dim sLock As Boolean
' Clean Collection
Set GetExternalLinks = New Collection
' check all Worksheets
For Each wSheet In ThisWorkbook.Sheets
sLock = wSheet.ProtectContents
If sLock Then wSheet.Unprotect "11111" ' 0 Then
cIni = InStr(1, iFormula, "'") + 1
If cIni > 1 Then
iFormula = Mid(iFormula, cIni)
cIni = InStr(cIni, iFormula, "]")
If cIni > 0 Then
iFormula = Replace(Replace(Left(iFormula, cIni), "]", ""), "[", "")
GetExternalLinks.Add iFormula, iFormula
End If
End If
End If
Next iCell
End If
If sLock Then wSheet.Protect "11111" ' <- your Password here (if any)
Next wSheet
On Error GoTo 0
End Function

Reply

Have Your Say

Δ