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”
Very, very usefull & helpfull.
Jednym słowem: miodzio :-)
Big thxs!
Beautiful. This code works fast and well! Thanks for posting it.
Thank you, this works great! How can I modify the code to have an additional column just for file name?
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