Skip to content

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

G15th September 2015

C3 Comments

TExcel, Microsoft Office, Visual Basic

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:

  • 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

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

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

  • Written by Marek on 18 July 2017:

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

  • Written by Mike on 10 July 2018:

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

  • Written by Zainab on 5 November 2018:

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

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.