Sub ReplaceExternalReference()
Dim s As Worksheet, c As Range, D As Name
Dim Links As Variant, l As Variant
Dim i As Integer, n As Integer, Lref As String
Dim ncell As Integer, nname As Integer
Const SepChar As String * 1 = "\"
ncell = 0
nname = 0
Links = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(Links) Then Exit Sub
Debug.Print
For Each l In Links
Debug.Print "Checking: ", l
'First remove path info from link
i = 1
Do While i <> 0
n = i
i = InStr(i + 1, l, SepChar)
Loop
Lref = Mid(l, n + 1)
'find link in cell formula and format conditions
For Each s In ActiveWorkbook.Worksheets
Set c = s.UsedRange.Find(Lref)
If Not (c Is Nothing) Then 'found link ?
Debug.Print s.Name, c.Address 'then print location
c.Formula = c.Value 'and replace ref with cell value
ncell = ncell + 1
End If
Next s
'find link in names collection
For Each D In ActiveWorkbook.Names
If InStr(1, D.RefersTo, Lref) Then 'found link ?
Debug.Print D.Name, D.RefersTo 'then print location
D.Delete 'and delete the name
nname = nname + 1
End If
Next D
Next l
Debug.Print "--- Ready ---"
MsgBox ncell & " cell formulas replaced and " & nname & " names deleted."
End Sub
