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

We tried breaking the links manually - luck We ran this VBA code to force the break - usually works but didn't this time Sub BreakLinks() 'Updateby20140318 Dim wb As Workbook Set wb = Application.ActiveWorkbook If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then For Each link In wb.LinkSources(xlExcelLinks) wb.BreakLink link, xlLinkTypeExcelLinks Next link End If End Sub Then checked in the Name Manager - empty for the columns/rows that were populated Then checked Conditional Formatting - empty again Then checked for Data Validation - also empty Repeated steps 3-5 and on step 5 got the warning "The selection contains more than one type of validation.". I clicked OK to erase current settings, repeated step 1 (links still in the Edit Links window); saved the workbook and closed it; opened it again and the links had disappeared!!

How to break an external link that won't break

Anonymous

A general solution

Select your whole data set on that sheet

Open Data Ribbon

Click Data Validation

Excel will generate this message "This selection contains some cells without Data Vlaidation Setting. Do you want to extend Data Validation to these cells?"

Click No

Click OK

Saves time on looking for that needle in someone elses spreadsheet.

Gary

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