VBA: Create a list of all external data references in Excel
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)
Open the Excel sheet that needs to be queried.
Press Alt + F11 or otherwise go to the Developer ribbon and choose the “Visual Basic” icon if the Developer Tab is enabled.
In the Visual Basic for Applications toolbar, go to Insert > Module
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
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.