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

I knew about all the others...but conditional formatting .....slipped right though...thansk for the resolve

0 comments

Anonymous

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!!

0 comments

Anonymous

In my case the problem arose when I copied in 2 protected sheets. I later found that they were also referencing a hidden sheet and when I unhid the extra sheet and then moved all 3 sheets to my workbook the problem was no longer present as the 2 protected sheets were still able to reference the previously hidden sheet now copied into the workbook.

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