Strip External Links: Remove Every Broken Workbook Reference Before Sending the File

TL;DR: You’re about to email the final workpaper to the client, and Excel pops up: “This workbook contains links to other data sources.” You have no idea which of the 30 sheets has the link, and “Edit Links” just shows Budget.xlsx with a #REF! status. This macro scans every formula, finds every external link, lists them for your review, and — with your confirmation — replaces them all with current values.

#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 before running — the macro is destructive and irreversible

Limitations:

• This macro is destructive. It replaces formulas with values. There is no undo. Save a backup first.
• Only replaces values, not named ranges, chart references, or data connections. Use Excel’s native Data → Edit Links dialog for those.
• Does not handle external references in conditional formatting, data validation rules, or named range definitions — only cell formulas.
• Cells replaced with .Value retain their number formatting from the original formula. If the source workbook used different formatting, the value inherits the destination cell’s format.

#The Macro

Option Explicit

Sub StripExternalLinks()
    ' ── Strip External Links ───────────────────────────
    ' Scans every formula on every sheet for external
    ' workbook references (the "[" character). Lists all
    ' matches in a MsgBox, asks for confirmation, then
    ' replaces each formula with its current value.
    '
    ' Cells replaced with .Value (not .Value2) to
    ' preserve date and currency formatting.
    ' ────────────────────────────────────────────────────

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

    ' ── Variables ──────────────────────────────────────
    Dim ws As Worksheet
    Dim cell As Range
    Dim linkList As String
    Dim linkCount As Long
    Dim sheetCount As Long
    Dim maxPreview As Long
    Dim i As Long
    Dim affectedCells As Collection

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

    Set affectedCells = New Collection
    linkList = ""
    linkCount = 0
    sheetCount = 0
    maxPreview = 15  ' Max cells shown in the confirmation MsgBox

    ' ── Scan every sheet for external references ───────
    Dim wsHadLinks As Boolean
    Dim wsLinkCount As Long

    For Each ws In ThisWorkbook.Worksheets
        wsHadLinks = False
        wsLinkCount = 0

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

        If Not formulaCells Is Nothing Then
            For Each cell In formulaCells
                If cell.Formula Like "*[*]*" Then
                    ' Found an external link
                    affectedCells.Add cell
                    wsHadLinks = True
                    wsLinkCount = wsLinkCount + 1
                    linkCount = linkCount + 1

                    ' Build a preview of the first few cells
                    If linkCount <= maxPreview Then
                        linkList = linkList & "  • " & ws.Name & "!" _
                            & cell.Address(False, False) & " → " _
                            & Format(cell.Value, "#,##0.00") & vbCrLf
                    End If
                End If
            Next cell
        End If

        If wsHadLinks Then
            sheetCount = sheetCount + 1
        End If
    Next ws

    ' ── No links found — exit early ────────────────────
    If linkCount = 0 Then
        MsgBox "No external workbook references found in any formula.", _
               vbInformation, "No Links Found"
        GoTo CleanUp
    End If

    ' ── Show preview and ask for confirmation ──────────
    Dim msg As String
    msg = "Found " & linkCount & " external link(s) across " _
        & sheetCount & " sheet(s)." & vbCrLf & vbCrLf

    If linkCount <= maxPreview Then
        msg = msg & "Affected cells:" & vbCrLf & linkList
    Else
        msg = msg & "First " & maxPreview & " of " & linkCount _
            & " affected cells:" & vbCrLf & linkList & vbCrLf
        msg = msg & "  ... and " & (linkCount - maxPreview) _
            & " more." & vbCrLf
    End If

    msg = msg & vbCrLf & "Replace all these formulas with their " _
        & "current values?" & vbCrLf & vbCrLf _
        & "⚠ This cannot be undone. Save a backup first."

    Dim answer As VbMsgBoxResult
    answer = MsgBox(msg, vbYesNo + vbExclamation + vbDefaultButton2, _
                    "Confirm — Strip External Links")

    If answer <> vbYes Then
        MsgBox "Cancelled. No changes were made.", _
               vbInformation, "Cancelled"
        GoTo CleanUp
    End If

    ' ── Replace formulas with values ───────────────────
    Dim replaced As Long
    replaced = 0

    For i = 1 To affectedCells.Count
        Set cell = affectedCells(i)
        If Not cell Is Nothing Then
            cell.Value = cell.Value  ' Retains number format
            replaced = replaced + 1
        End If
    Next i

    ' ── Report results ─────────────────────────────────
    MsgBox "Replaced " & replaced & " external link(s) " _
        & "across " & sheetCount & " sheet(s) with current values.", _
        vbInformation, "Done"

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

#The [ character is the signature of an external link

Every external workbook reference in Excel contains a [ bracket — it wraps the source filename. An internal reference looks like =Sheet1!A1. An external reference looks like =[Budget.xlsx]Sheet1!$A$1. The [ character always appears, even when the link is broken:

If cell.Formula Like "*[*]*" Then

Like with wildcards is the simplest VBA pattern match — no regex, no external objects. The asterisks mean “zero or more characters, then [, then zero or more characters.” If [ appears anywhere in the formula string, it’s an external link.

#Collect first, destroy later

The macro gathers all affected cells into a Collection before showing the confirmation dialog. This is deliberate: if a formula change cascades into other cells, scanning and replacing in the same loop could miss links that depend on cells already converted.

Set affectedCells = New Collection
' ... scan loop adds each cell ...
affectedCells.Add cell
' ... confirmation ...
For i = 1 To affectedCells.Count
    cell.Value = cell.Value
Next i

By scanning completely first and replacing after the user has reviewed the list, the macro guarantees you see the full picture before making any changes.

#.Value, not .Value2

.Value preserves date and currency formatting from the cell’s number format. .Value2 returns a raw double with no date context — a January 15 date becomes 45366 and a currency cell drops its symbol. Using .Value means your numbers still look like numbers after the conversion:

cell.Value = cell.Value  ' Date stays a date, currency stays currency

#Why the confirmation shows cell addresses before destroying

The MsgBox lists up to 15 affected cells so you can spot-check before committing. If you expected 3 links on a fixed-assets schedule but the preview shows 47 links on 8 different sheets, something’s wrong — maybe a departed preparer built a side-model linked to an archived file. The preview gives you a chance to abort, open the file, and investigate before permanently flattening the formulas.

#Broken links and live links — same treatment

The macro doesn’t distinguish between a broken link (#REF!) and a live link. Both contain [ in the formula and both get replaced with the current displayed value. A broken link is already showing an error — converting it to #REF! as a value at least removes the warning prompt. A live link makes a clean conversion. Either way, the workbook no longer nags the recipient.

#Adapt It