Skip to content

Dear Internet Explorer user: Your browser is no longer supported

Please switch to a modern browser such as Microsoft Edge, Mozilla Firefox or Google Chrome to view this website's content.

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:

An Excel dialogue box indicating that a workbook contains external links.
An Excel dialogue box indicating that a workbook contains external links.

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:


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

Screen capture of VBA
Inserting a module in Visual Basic for Applications.

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)
        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.



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

On 18 July 2017, Marek wrote: Hyperlink chain icon

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


On 10 July 2018, Mike wrote: Hyperlink chain icon

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


On 5 November 2018, Zainab wrote: Hyperlink chain icon

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


On 26 October 2023, Frapozze wrote: Hyperlink chain icon

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


Have Your Say

The following HTML is permitted:
<a href="" title=""> <b> <blockquote cite=""> <code> <em> <i> <q cite=""> <strike> <strong>

Comments will be published subject to the Editorial Policy.