Killing External links

[jcaulfield]

I have Excel 2016. Occasionally I have a difficult time breaking external links. My process is as follows: Data - Edit Link - Break link. If link is not broken then I delete any "named" ranges referencing an external files, and then Data - Edit Link - Break link again. Most of the time this works, but sometimes I can not find and break link. I even search Macros for any external links without success. Thoughts?

 

[Yongle]

Does this help?

Sub BreakExternalLinks()
    Dim ExternalLinks As Variant, x As Long
    
    With ActiveWorkbook
        On Error Resume Next
           ExternalLinks = .LinkSources(Type:=xlLinkTypeExcelLinks)
        On Error GoTo Handling
            For x = 1 To UBound(ExternalLinks)
                .BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
            Next x
    End With
Handling:
End Sub

Original source of code (now modified) was www.TheSpreadsheetGuru.com/The-Code-Vault

 

[kakiebos]

Halleluja,

This actually worked for me just now. I've been struggling with breaking links from old files forever.

Going to keep this code in my personal workbook.

 

[Seti]

Thanks Yongle. I had a similar issue and I couldn't find any links in the usual places (range names, object values, data validation, I had no charts). But using your code resolved my issue in a second. I don't know where it found the links, but I can see if I can find them at a later date for my own sanity.

 

[Yongle]

@Seti - glad the code also fixed your problem

I don't know where it found the links, but I can see if I can find them at a later date for my own sanity

Sometimes the cause of the issue cannot be determined
- I have experienced phantom links (Excel hanging onto the memory of a link that is categorically not in the workbook) .... perhaps a minor corruption or whatever!

Have you checked for links in
- shapes
- formulas in conditional formatting rules