Excel VBA Procedures to Break All External References in Workbook

The following Excel VBA procedures may be used to break all external references and links in the active workbook. The BreakExternalReferences() procedure will change all formulas containing references to external workbooks into the resulting value of that formula. The DeleteExternalNames() procedure will delete all defined names that contain external workbook references. Again, any formulas containing references to the defined name will be converted to the resulting value of that formula.

Option Explicit

Sub BreakExternalReferences()
    Dim arLinks As Variant
    Dim i As Long

    arLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

    If IsArray(arLinks) Then
        For i = LBound(arLinks) To UBound(arLinks)
            ActiveWorkbook.BreakLink Name:=arLinks(i), Type:=xlLinkTypeExcelLinks
        Next i
    End If
End Sub

Sub DeleteExternalNames()
    Dim objDefinedName As Object

    For Each objDefinedName In ActiveWorkbook.Names
        If InStr(objDefinedName.RefersTo, "[") > 0 Then
            objDefinedName.Delete
        End If
    Next objDefinedName
End Sub