When an engineering workbook starts showing missing-source warnings, it is usually not enough to check only formulas. External links can hide in chart series, defined names, pivot caches, data connections, validation rules, and conditional formatting. This VBA module scans the current workbook and creates a clear report sheet without asking you to load the missing file.
Downloadable module file: ExcelBrokenLinkScanner.bas
What this macro checks
- Cell formulas with external workbook paths or Excel error text.
- Workbook and worksheet defined names.
- Embedded charts and chart-sheet series formulas.
- Hyperlinks, data connections, pivot caches, data validation, and conditional formatting formulas.
- Excel link sources returned by
Workbook.LinkSources.
How to use it
- Open the workbook you want to check.
- Press
ALT + F11. - Insert a new module.
- Paste the full code below.
- Run
ScanWorkbookForExternalLinks.
The macro creates a sheet named _Link_Report with sheet name, cell or chart object, extracted target, status, and source formula.
Important note
The macro does not open missing source workbooks and does not update links. Local paths are checked using file existence only. Web links are listed but not tested.
Complete VBA code
Option Explicit
' ToolsForEngineers.com
' Excel external link and error scanner
' Scans the active workbook only. It does not open source files or update links.
Public Sub ScanWorkbookForExternalLinks()
Dim wb As Workbook
Dim reportWs As Worksheet
Dim r As Long
Dim oldCalc As XlCalculation
Dim oldScreen As Boolean
Dim oldAlerts As Boolean
Set wb = ActiveWorkbook
If wb Is Nothing Then Exit Sub
oldScreen = Application.ScreenUpdating
oldAlerts = Application.DisplayAlerts
oldCalc = Application.Calculation
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
Set reportWs = GetOrCreateReportSheet(wb, "_Link_Report")
PrepareReport reportWs
r = 2
ScanWorkbookLinkSources wb, reportWs, r
ScanWorkbookNames wb, reportWs, r
ScanWorksheets wb, reportWs, r
ScanEmbeddedCharts wb, reportWs, r
ScanChartSheets wb, reportWs, r
ScanConnections wb, reportWs, r
ScanPivotCaches wb, reportWs, r
If r = 2 Then
reportWs.Cells(r, 1).Value = 1
reportWs.Cells(r, 2).Value = "Workbook"
reportWs.Cells(r, 5).Value = "Result"
reportWs.Cells(r, 7).Value = "No external links or obvious link-related errors found"
r = r + 1
End If
FinishReport reportWs, r - 1
CleanExit:
Application.Calculation = oldCalc
Application.DisplayAlerts = oldAlerts
Application.ScreenUpdating = oldScreen
reportWs.Activate
Exit Sub
CleanFail:
On Error Resume Next
Application.Calculation = oldCalc
Application.DisplayAlerts = oldAlerts
Application.ScreenUpdating = oldScreen
MsgBox "Scan stopped: " & Err.Description, vbExclamation, "External link scanner"
End Sub
Private Sub ScanWorkbookLinkSources(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim links As Variant
Dim i As Long
On Error Resume Next
links = wb.LinkSources(Type:=xlExcelLinks)
On Error GoTo 0
If IsEmpty(links) Then Exit Sub
For i = LBound(links) To UBound(links)
AddReportRow wsReport, r, "Workbook", "", "LinkSources", "Excel external link", _
CStr(links(i)), LinkStatus(CStr(links(i))), CStr(links(i))
Next i
End Sub
Private Sub ScanWorkbookNames(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim nm As Name
Dim target As String
For Each nm In wb.Names
If ContainsExternalReference(nm.RefersTo) Or ContainsExcelErrorText(nm.RefersTo) Then
target = ExtractLinkTarget(nm.RefersTo)
AddReportRow wsReport, r, "Workbook", "", nm.Name, "Defined name", _
target, LinkStatus(target), nm.RefersTo
End If
Next nm
End Sub
Private Sub ScanWorksheets(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim ws As Worksheet
Dim rng As Range
Dim c As Range
Dim nm As Name
Dim h As Hyperlink
Dim fc As FormatCondition
Dim target As String
For Each ws In wb.Worksheets
If ws.Name <> wsReport.Name Then
Set rng = Nothing
On Error Resume Next
Set rng = ws.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not rng Is Nothing Then
For Each c In rng.Cells
If ContainsExternalReference(c.Formula) Or ContainsExcelErrorText(c.Text) Then
target = ExtractLinkTarget(c.Formula)
AddReportRow wsReport, r, "Worksheet", ws.Name, c.Address(False, False), "Cell formula", _
target, LinkStatus(target), c.Formula
End If
Next c
End If
For Each nm In ws.Names
If ContainsExternalReference(nm.RefersTo) Or ContainsExcelErrorText(nm.RefersTo) Then
target = ExtractLinkTarget(nm.RefersTo)
AddReportRow wsReport, r, "Worksheet", ws.Name, nm.Name, "Sheet defined name", _
target, LinkStatus(target), nm.RefersTo
End If
Next nm
For Each h In ws.Hyperlinks
target = h.Address
If Len(target) > 0 Then
AddReportRow wsReport, r, "Worksheet", ws.Name, h.Parent.Address(False, False), "Hyperlink", _
target, LinkStatus(target), target
End If
Next h
Set rng = Nothing
On Error Resume Next
Set rng = ws.UsedRange
On Error GoTo 0
If Not rng Is Nothing Then
ScanValidationFormulas rng, wsReport, r, ws.Name
ScanConditionalFormatting rng, wsReport, r, ws.Name
End If
End If
Next ws
End Sub
Private Sub ScanValidationFormulas(ByVal rng As Range, ByVal wsReport As Worksheet, ByRef r As Long, ByVal sheetName As String)
Dim c As Range
Dim f1 As String
Dim f2 As String
Dim target As String
On Error Resume Next
For Each c In rng.Cells
Err.Clear
f1 = c.Validation.Formula1
If Err.Number = 0 Then
f2 = c.Validation.Formula2
If ContainsExternalReference(f1) Or ContainsExternalReference(f2) Then
target = ExtractLinkTarget(f1 & " " & f2)
AddReportRow wsReport, r, "Worksheet", sheetName, c.Address(False, False), "Data validation", _
target, LinkStatus(target), f1 & " " & f2
End If
End If
Next c
On Error GoTo 0
End Sub
Private Sub ScanConditionalFormatting(ByVal rng As Range, ByVal wsReport As Worksheet, ByRef r As Long, ByVal sheetName As String)
Dim fc As FormatCondition
Dim target As String
On Error Resume Next
For Each fc In rng.FormatConditions
Err.Clear
If ContainsExternalReference(fc.Formula1) Or ContainsExternalReference(fc.Formula2) Then
target = ExtractLinkTarget(fc.Formula1 & " " & fc.Formula2)
AddReportRow wsReport, r, "Worksheet", sheetName, rng.Address(False, False), "Conditional formatting", _
target, LinkStatus(target), fc.Formula1 & " " & fc.Formula2
End If
Next fc
On Error GoTo 0
End Sub
Private Sub ScanEmbeddedCharts(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim ws As Worksheet
Dim co As ChartObject
For Each ws In wb.Worksheets
If ws.Name <> wsReport.Name Then
For Each co In ws.ChartObjects
ScanOneChart co.Chart, wsReport, r, ws.Name, co.Name
Next co
End If
Next ws
End Sub
Private Sub ScanChartSheets(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim ch As Chart
For Each ch In wb.Charts
ScanOneChart ch, wsReport, r, ch.Name, ch.Name
Next ch
End Sub
Private Sub ScanOneChart(ByVal ch As Chart, ByVal wsReport As Worksheet, ByRef r As Long, ByVal sheetName As String, ByVal chartName As String)
Dim s As Series
Dim target As String
On Error Resume Next
For Each s In ch.SeriesCollection
If ContainsExternalReference(s.Formula) Or ContainsExcelErrorText(s.Formula) Then
target = ExtractLinkTarget(s.Formula)
AddReportRow wsReport, r, "Chart", sheetName, chartName & " / " & s.Name, "Chart series", _
target, LinkStatus(target), s.Formula
End If
Next s
On Error GoTo 0
End Sub
Private Sub ScanConnections(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim cn As WorkbookConnection
Dim txt As String
Dim target As String
For Each cn In wb.Connections
txt = ""
On Error Resume Next
txt = cn.OLEDBConnection.Connection
If Len(txt) = 0 Then txt = cn.ODBCConnection.Connection
If Len(txt) = 0 Then txt = cn.TextConnection.Connection
On Error GoTo 0
If ContainsExternalReference(txt) Or Len(txt) > 0 Then
target = ExtractLinkTarget(txt)
AddReportRow wsReport, r, "Workbook", "", cn.Name, "Data connection", _
IIf(Len(target) > 0, target, cn.Name), LinkStatus(target), txt
End If
Next cn
End Sub
Private Sub ScanPivotCaches(ByVal wb As Workbook, ByVal wsReport As Worksheet, ByRef r As Long)
Dim pc As PivotCache
Dim txt As String
Dim target As String
On Error Resume Next
For Each pc In wb.PivotCaches
txt = ""
txt = CStr(pc.SourceData)
If Len(txt) = 0 Then txt = CStr(pc.Connection)
If ContainsExternalReference(txt) Then
target = ExtractLinkTarget(txt)
AddReportRow wsReport, r, "Workbook", "", "PivotCache", "Pivot source", _
target, LinkStatus(target), txt
End If
Next pc
On Error GoTo 0
End Sub
Private Function ContainsExternalReference(ByVal textValue As String) As Boolean
Dim s As String
s = LCase$(textValue)
ContainsExternalReference = _
(InStr(1, s, ":[\\/]") > 0) Or _
(InStr(1, s, "\\") > 0) Or _
(InStr(1, s, "http://") > 0) Or _
(InStr(1, s, "https://") > 0) Or _
(InStr(1, s, ".xls") > 0) Or _
(InStr(1, s, ".csv") > 0) Or _
(InStr(1, s, "[") > 0 And InStr(1, s, "]") > 0)
End Function
Private Function ContainsExcelErrorText(ByVal textValue As String) As Boolean
Dim s As String
s = UCase$(textValue)
ContainsExcelErrorText = _
InStr(1, s, "#REF!") > 0 Or _
InStr(1, s, "#VALUE!") > 0 Or _
InStr(1, s, "#NAME?") > 0 Or _
InStr(1, s, "#N/A") > 0
End Function
Private Function ExtractLinkTarget(ByVal textValue As String) As String
Dim re As Object
Dim m As Object
Dim p As String
Set re = CreateObject("VBScript.RegExp")
re.Global = False
re.IgnoreCase = True
re.Pattern = "((?:[A-Za-z]:\\\\|\\\\\\\\)[^'\[]*)\[([^\]]+)\]"
If re.Test(textValue) Then
Set m = re.Execute(textValue)(0)
ExtractLinkTarget = m.SubMatches(0) & m.SubMatches(1)
Exit Function
End If
re.Pattern = "(https?://[^\s,'""\)]+)"
If re.Test(textValue) Then
ExtractLinkTarget = re.Execute(textValue)(0).SubMatches(0)
Exit Function
End If
re.Pattern = "((?:[A-Za-z]:\\\\|\\\\\\\\)[^\s,'""\)]+)"
If re.Test(textValue) Then
ExtractLinkTarget = re.Execute(textValue)(0).SubMatches(0)
Exit Function
End If
re.Pattern = "\[([^\]]+)\]"
If re.Test(textValue) Then
ExtractLinkTarget = re.Execute(textValue)(0).SubMatches(0)
Exit Function
End If
ExtractLinkTarget = ""
End Function
Private Function LinkStatus(ByVal target As String) As String
Dim fso As Object
Dim t As String
t = Trim$(target)
If Len(t) = 0 Then
LinkStatus = "External reference found"
Exit Function
End If
If LCase$(Left$(t, 7)) = "http://" Or LCase$(Left$(t, 8)) = "https://" Then
LinkStatus = "Web link - not checked"
Exit Function
End If
Set fso = CreateObject("Scripting.FileSystemObject")
If InStr(1, t, "\") > 0 Or InStr(1, t, ":") > 0 Then
If fso.FileExists(t) Or fso.FolderExists(t) Then
LinkStatus = "OK - local path exists"
Else
LinkStatus = "Missing file or folder"
End If
Else
LinkStatus = "Workbook name only - path not available"
End If
End Function
Private Sub AddReportRow(ByVal ws As Worksheet, ByRef r As Long, ByVal areaName As String, ByVal sheetName As String, _
ByVal locationText As String, ByVal sourceType As String, ByVal target As String, _
ByVal statusText As String, ByVal sourceText As String)
ws.Cells(r, 1).Value = r - 1
ws.Cells(r, 2).Value = areaName
ws.Cells(r, 3).Value = sheetName
ws.Cells(r, 4).Value = locationText
ws.Cells(r, 5).Value = sourceType
ws.Cells(r, 6).Value = target
ws.Cells(r, 7).Value = statusText
ws.Cells(r, 8).Value = Left$(sourceText, 32000)
r = r + 1
End Sub
Private Function GetOrCreateReportSheet(ByVal wb As Workbook, ByVal sheetName As String) As Worksheet
On Error Resume Next
Set GetOrCreateReportSheet = wb.Worksheets(sheetName)
On Error GoTo 0
If GetOrCreateReportSheet Is Nothing Then
Set GetOrCreateReportSheet = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
GetOrCreateReportSheet.Name = sheetName
End If
End Function
Private Sub PrepareReport(ByVal ws As Worksheet)
ws.Cells.Clear
ws.Range("A1:H1").Value = Array("No.", "Area", "Sheet", "Cell / Object", "Source Type", "Extracted Link", "Status", "Formula / Source Text")
ws.Rows(1).Font.Bold = True
ws.Rows(1).Interior.Color = RGB(222, 235, 247)
End Sub
Private Sub FinishReport(ByVal ws As Worksheet, ByVal lastRow As Long)
With ws
.Columns("A:H").AutoFit
.Columns("H:H").ColumnWidth = 70
.Columns("H:H").WrapText = True
.Range("A1:H" & Application.Max(lastRow, 1)).AutoFilter
.Activate
.Range("A2").Select
ActiveWindow.FreezePanes = True
End With
End Sub