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