Tom’s Tutorials For Excel: Finding and Deleting Phantom Links
In my 20 years working with Excel, I have not yet seen a single case when this message appeared and there was not a link; Excel is very good at recognizing an existing link. So, how do you find a link when you have looked throughout your workbook and cannot locate what Excel says is there?
Here are some tactics in no particular order for searching and destroying phantom links:
Right-click on any sheet tab, left-click Select All Sheets, press Ctrl+F and in the Find what field enter
The idea is to look through all sheets for formulas referencing external workbooks, so be sure to unhide any hidden sheets with this tactic.
Look closely at your list of defined names, not just in the name box but in the Define Name dialog, and make sure you do not have any named ranges refering to an outside Workbook. In Excel version 2003 or before, from the worksheet menu click Insert > Name > Define. In version 2007 or 2010, from the Ribbon click the Formulas tab, and in the Defined Names section click Name Manager.
Open a new workbook, create a link to it, and save that workbook. Now go to Edit > Links (version 2003) or the Name Manager (version 2007 or 2010), click the Edit button, and use the “Change Source” to refer the link to the new workbook. Save again and then delete the link you created.
If you have pivot tables or charts, they may contain source data that is derived from outside workbooks and hence being the links culprit. Take a look at the chart series and the pivottable source ranges.
A few macros:
Sub DeleteLinks() 'thanks Hans Herber Dim varLink As Variant, intCounter As Integer varLink = ActiveWorkbook.LinkSources(xlExcelLinks) If Not IsEmpty(varLink) Then For intCounter = 1 To UBound(varLink) ActiveWorkbook.ChangeLink Name:=varLink(intCounter), newname:=ThisWorkbook.Name Next intCounter End If End Sub Sub ExternalLinkDelete() 'thanks Hans Herber Dim cell As Range For Each cell In activeworksheet.UsedRange If Left(cell.Formula, 1) = "=" And InStr(cell.Formula, "[") > 1 Then cell.Value = cell.Value Next cell End Sub Sub RemoveAllHyperLinks() Dim Sh As Worksheet For Each Sh In Worksheets Sh.Cells.Hyperlinks.Delete Next Sh End Sub
Good luck ! The above tactics are by no means a comprehensive list. With persistence you will find the link(s), maybe with the above suggestions, or from your own ideas or suggestions by others.