External Link Reporter: See Every Workbook Reference Before You Break Anything

TL;DR: Before you strip external links, you need to know what you’re breaking. This macro scans the entire workbook, finds every unique external file reference, and builds a report sheet showing which sheets reference which files — complete with formula counts and link status. No modifications to any formulas. The report sits alongside your data for review before you commit to stripping anything.

#Prerequisites & Setup

What you’ll need:

• Excel 2016+ (desktop)
• A workbook containing formulas that reference external workbooks (the [ in =[Budget.xlsx]Sheet1!A1)
• The workbook should be saved — unsaved workbooks can’t have external links

Limitations:

• This macro is non-destructive. It reads formulas but never modifies them. Safe to run on any workbook.
• Only detects cell-formula links to other Excel workbooks. Does not detect DDE links, OLE links, or add-in references.
• Does not scan named ranges, chart data sources, or data connections. Use Excel’s native Data → Edit Links dialog for those.
• Broken links (source file moved or deleted) still show up in the report — a broken link is still a link.
• Protected sheets with hidden formulas will show those formulas in the report if SpecialCells can access them. If a sheet is fully protected, formulas on that sheet may be skipped.

#The Macro

Option Explicit

Sub ReportExternalLinks()
    ' ── External Link Reporter ─────────────────────────
    ' Scans every formula on every sheet for external
    ' workbook references ([filename.xlsx]). Builds a
    ' report on a new "External-Links" sheet showing
    ' every unique external path, which sheets reference
    ' it, how many formulas, and whether the link is
    ' live or broken.
    '
    ' Non-destructive — reads formulas, never writes.
    ' Safe to run on any workbook at any time.
    ' ────────────────────────────────────────────────────

    ' ── Configuration ──────────────────────────────────
    Const OUT_SHEET As String = "External-Links"
    Const MAX_FORMULA_SCAN As Long = 50000  ' Safety limit

    ' ── State management ───────────────────────────────
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    ' ── Variables ──────────────────────────────────────
    Dim links As Variant
    Dim ws As Worksheet
    Dim rpt As Worksheet
    Dim cell As Range
    Dim formulaCells As Range
    Dim outRow As Long
    Dim i As Long, j As Long
    Dim linkPath As String
    Dim sheetList As String
    Dim totalFormulas As Long
    Dim linkStatus As String
    Dim sheetsWithLink As New Collection
    Dim sheetCount As Long
    Dim emptyCount As Long
    Dim uniqueLinks As Long

    ' ── Error handling ─────────────────────────────────
    On Error GoTo CleanUp

    ' ── Step 1: Get all external link sources ──────────
    links = ThisWorkbook.LinkSources(xlExcelLinks)

    If IsEmpty(links) Then
        MsgBox "No external workbook links found in this file.", _
               vbInformation, "No Links Found"
        GoTo CleanUp
    End If

    uniqueLinks = UBound(links)

    ' ── Step 2: Create report sheet ────────────────────
    On Error Resume Next
    Application.DisplayAlerts = False
    ThisWorkbook.Worksheets(OUT_SHEET).Delete
    Application.DisplayAlerts = True
    On Error GoTo CleanUp

    Set rpt = ThisWorkbook.Worksheets.Add( _
        Before:=ThisWorkbook.Worksheets(1))
    rpt.Name = OUT_SHEET

    ' ── Step 3: Write headers ──────────────────────────
    rpt.Range("A1:E1").Value = Array( _
        "External Path", "Referenced Sheets", _
        "Formula Count", "Status", "Notes")
    rpt.Range("A1:E1").Font.Bold = True
    rpt.Range("A1:E1").Interior.Color = RGB(50, 50, 50)
    rpt.Range("A1:E1").Font.Color = vbWhite

    ' ── Step 4: Scan each link across all sheets ───────
    outRow = 2

    For i = LBound(links) To UBound(links)
        linkPath = links(i)
        sheetList = ""
        totalFormulas = 0
        sheetCount = 0

        ' Reset collection for this link
        Set sheetsWithLink = New Collection

        For Each ws In ThisWorkbook.Worksheets
            If ws.Name = OUT_SHEET Then GoTo NextSheet

            On Error Resume Next
            Set formulaCells = ws.Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo CleanUp

            If Not formulaCells Is Nothing Then
                Dim localCount As Long
                localCount = 0

                For Each cell In formulaCells
                    ' Check if this formula contains the link path
                    If InStr(1, cell.Formula, linkPath, vbTextCompare) > 0 Then
                        localCount = localCount + 1
                    End If

                    ' Safety limit — prevent infinite scan on huge sheets
                    If localCount >= MAX_FORMULA_SCAN Then Exit For
                Next cell

                If localCount > 0 Then
                    sheetsWithLink.Add ws.Name & " (" & localCount & ")", _
                                       ws.Name
                    totalFormulas = totalFormulas + localCount
                    sheetCount = sheetCount + 1
                End If
            End If

NextSheet:
        Next ws

        ' ── Build the sheet list string ─────────────────
        If sheetCount = 0 Then
            sheetList = "(no formulas found)"
        Else
            For j = 1 To sheetsWithLink.Count
                sheetList = sheetList & sheetsWithLink(j)
                If j < sheetsWithLink.Count Then sheetList = sheetList & ", "
            Next j
        End If

        ' ── Determine link status ───────────────────────
        Dim testPath As String
        testPath = linkPath
        On Error Resume Next
        If Dir(testPath) <> "" Then
            linkStatus = "LIVE"
        Else
            linkStatus = "BROKEN"
        End If
        On Error GoTo CleanUp

        ' ── Write the row ───────────────────────────────
        rpt.Cells(outRow, 1).Value = linkPath
        rpt.Cells(outRow, 2).Value = sheetList
        rpt.Cells(outRow, 3).Value = totalFormulas
        rpt.Cells(outRow, 4).Value = linkStatus

        ' Status-specific formatting
        If linkStatus = "BROKEN" Then
            rpt.Cells(outRow, 4).Interior.Color = RGB(254, 202, 202)
            rpt.Cells(outRow, 4).Font.Bold = True
            rpt.Cells(outRow, 5).Value = _
                "Source file missing or moved. Link value is frozen."
        Else
            rpt.Cells(outRow, 4).Interior.Color = RGB(220, 252, 231)
            rpt.Cells(outRow, 4).Font.Bold = True
        End If

        ' ── Notes for specific patterns ─────────────────
        If totalFormulas = 0 Then
            rpt.Cells(outRow, 5).Value = _
                "Link registered but no formulas reference it. " & _
                "Check named ranges or chart data sources."
            rpt.Cells(outRow, 5).Font.Color = RGB(180, 83, 9)
        End If

        ' Alternate row shading
        If outRow Mod 2 = 0 Then
            rpt.Range("A" & outRow & ":E" & outRow). _
                Interior.Color = RGB(248, 248, 250)
        End If

        outRow = outRow + 1
    Next i

    ' ── Step 5: Format report ──────────────────────────
    With rpt
        .Columns("A").ColumnWidth = 55
        .Columns("B").ColumnWidth = 50
        .Columns("C").ColumnWidth = 16
        .Columns("D").ColumnWidth = 10
        .Columns("E").ColumnWidth = 48
        .Range("A1").Select
        ActiveWindow.FreezePanes = True
    End With

    ' ── Step 6: Summary ────────────────────────────────
    Dim brokenCount As Long, liveCount As Long
    brokenCount = Application.CountIf(rpt.Range("D2:D" & outRow), "BROKEN")
    liveCount = Application.CountIf(rpt.Range("D2:D" & outRow), "LIVE")

    MsgBox "Found " & uniqueLinks & " unique external link(s)." & vbCrLf & _
           "  LIVE:    " & liveCount & vbCrLf & _
           "  BROKEN:  " & brokenCount & vbCrLf & vbCrLf & _
           "Report created on '" & OUT_SHEET & "' sheet.", _
           vbInformation, "External Link Report"

CleanUp:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic

    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbCritical, "Macro Error"
    End If
End Sub

#How It Works

#LinkSources: Excel already knows the answer

Excel maintains an internal registry of every external file this workbook references. The LinkSources method returns that list directly — no scanning required to find the filenames:

links = ThisWorkbook.LinkSources(xlExcelLinks)

The xlExcelLinks argument filters to only OLE and DDE links (i.e., formula references to other Excel workbooks). The result is a one-dimensional array of full file paths, e.g. \\server\share\2025\Budget.xlsx. If there are no links, the array is empty — the macro reports zero and exits cleanly.

#Why scan per-link instead of per-cell

The macro could have scanned every cell once and built the report from a dictionary. That’s faster. But building per-link means the report maps directly to the “Edit Links” dialog the user already knows — one row per file, not one row per cell. The preparer who sees “Budget.xlsx: 14 formulas across 3 sheets” can decide whether to strip that file or keep it. A cell-level report would show 14 rows for the same file, making it harder to answer the strategic question: “Should I break this link?”

#Two detection passes

The macro first uses LinkSources to get unique file paths — this is fast and reliable. Then it loops each sheet’s formula cells to count which formulas reference which path. The SpecialCells(xlCellTypeFormulas) call narrows the scan to formula-only cells, skipping blank cells and constants:

Set formulaCells = ws.Cells.SpecialCells(xlCellTypeFormulas)

If a sheet has no formulas (e.g., a data-only import sheet), SpecialCells returns Nothing and the sheet is skipped entirely. This makes the scan fast even on large workbooks — blank columns and data entries are never examined.

#Live vs. Broken: Dir() as a quick file test

The Dir() function checks whether the linked file exists at its stored path:

If Dir(testPath) <> "" Then
    linkStatus = "LIVE"
Else
    linkStatus = "BROKEN"
End If

This is a best-effort check. If the file is on a network share that’s currently unmapped, Dir() will report it as broken even though it technically exists. The report flags this as BROKEN but the Notes column doesn’t shame you for it — broken is broken from Excel’s perspective, regardless of why.

#Why the report goes as the first sheet

Set rpt = ThisWorkbook.Worksheets.Add( _
    Before:=ThisWorkbook.Worksheets(1))

The report is inserted as sheet 1 so it’s the first thing anyone sees when opening the file. This is intentional: if you’re handing this workbook to a partner or client, the external-link inventory should be the greeting card. It answers the question “what does this file depend on?” before anyone has to ask it.

#Handling links with no formula matches

Occasionally LinkSources returns a path, but no cell formula contains it. This happens when the link exists in a named range definition, a chart data source, or a PivotTable cache. The report flags this with a note:

If totalFormulas = 0 Then
    rpt.Cells(outRow, 5).Value = _
        "Link registered but no formulas reference it. " & _
        "Check named ranges or chart data sources."

The preparer now knows to use Excel’s native Data → Edit Links dialog to break these, since they’re not cell-formula links the macro can audit.

#Safety limit on large sheets

Const MAX_FORMULA_SCAN As Long = 50000

A sheet with 100,000 formula cells all referencing the same external file would produce a technically correct but meaningless report row. The safety limit caps the scan per file per sheet. In practice, a tax workpaper rarely has more than a few thousand formula cells on a single sheet — this limit exists as a guard, not a governor.

#Adapt It