I have studied all the Excel help available and followed the advice, but I have encountered an external link in an Excel 2010 workbook that simply will not break. No matter how many times I select "Break Link," this zombie is always there. I have tried deleting range names that came from the source file, to no avail. I "break the link" in the destination file, save, close the file, reopen it -- and the link is still there. I have tried adjusting the options every which way, no luck. How do I absolutely, positively, completely and forever, send this link to Davy Jones's locker?

Dear Long_John_Silver I had a similar issue and in addition to Raju S Das' recommendation, I have also checked the data validations for various fields; this is where I found some zombie links that were not required anymore. Maybe you can find some in your file too and remove them: Select the cells where you expect the zombie data validation (I could narrow it down to one section on a specific worksheet) In the Data Tools section of the Data ribbon, select Data Validation from the Data Validation dropdown Check if there is any list validation with a reference to a linked file location. Hope this helps. Best regards, Roger

How to break an external link that won't break

Anonymous

These are good suggestions for places to look. Still, it begs the question: *Shouldn't clicking the "Break Links" button look for all of these and clear them at once?*That is, the button says "Break Links," so that is what it should do.

0 comments

Anonymous

This was the ONLY solution that worked for me! Once I changed the source to the file I was actually working in I had to save the file then close it. When I reopened it the edit links option was finally greyed out! Thank you SOOOOO Much! This was driving me batty.

0 comments

Anonymous

Links can also be found in charts, and not just the source data but even the titles! They can also be found on objects, e.g., a combobox whose ListFillRange property is a range in another workbook.

Here's some code I wrote to find all links. I hope you can copy-and-paste it OK! It creates a tab-delmited text file in the active workbook's folder with a list of all the links from formulas, names, charts and objects.

Const LinkRefPattern1$ = "*.xl??]*", LinkRefPattern2$ = "*.xl?]*"

Public Sub AllLinks()

Dim Rg As Range, Rg1 As Range, WS As Worksheet, Nm As Name, Ch As Chart, cs As ChartObject

Dim Obj As Object, Sh As Shape, F%, FN$, Flag As Boolean, TestStr$

Close

F% = FreeFile(): FN$ = ActiveWorkbook.FullName & " Links.txt": Open FN$ For Output As #F%

Print #F%, "Type"; vbTab; "Worksheet"; vbTab; "Source"; vbTab; "Target"

If Application.Workbooks.Count > 2 Then ' Including this workbook

MsgBox "Please close all but the one workbook in which to find links.", vbExclamation

Exit Sub

End If

For Each Nm In ActiveWorkbook.Names ' Name links

If Nm.RefersToLocal Like LinkRefPattern1$ Or Nm.RefersToLocal Like LinkRefPattern2$ Then

Flag = True: Print #F%, "Name"; vbTab;

If TypeName$(Nm.Parent) <> "Workbook" Then Print #F%, Nm.Parent.Name;

Print #F%, vbTab; Nm.Name; vbTab; "'"; Nm.RefersToLocal

End If

Flag = AllChartLinks(F%, Ch)

' Formula links

Set Rg1 = WS.Cells.Find(LinkRefPattern1$, LookIn:=xlFormulas, LookAt:=xlWhole)

If Not (Rg1 Is Nothing) Then

Set Rg = Rg1

Do

If Rg.HasFormula Then Print #F%, "Formula"; vbTab; WS.Name; vbTab; Rg.AddressLocal(False, False); vbTab; "'"; Rg.FormulaLocal

Set Rg = WS.Cells.FindNext(Rg)

If Rg Is Nothing Then Exit Do

Loop Until Rg.Address = Rg1.Address

End If

Set Rg1 = WS.Cells.Find(LinkRefPattern2$, LookIn:=xlFormulas, LookAt:=xlWhole)

If Not (Rg1 Is Nothing) Then

Set Rg = Rg1

Do

If Rg.HasFormula Then Print #F%, "Formula"; vbTab; WS.Name; vbTab; Rg.AddressLocal(False, False); vbTab; "'"; Rg.FormulaLocal

Set Rg = WS.Cells.FindNext(Rg)

If Rg Is Nothing Then Exit Do

Loop Until Rg.Address = Rg1.Address

End If

For Each cs In WS.ChartObjects ' Links in chart objects on worksheets

Flag = AllChartLinks(F%, cs.Chart, WS)

For Each Sh In WS.Shapes ' Shape links

TestStr$ = "": TestStr$ = Sh.ControlFormat.LinkedCell