Excel "Invisible" broken links

Published:
Updated:

Getting a 'This workbook contains one or more links that could not be updated'

error when opening a particular worksheet? Even if the links status is "OK" when you try to update it manually?


The broken link error might be "hidden" in "named range" that was in a other spreadsheet, then moved, copied over or deleted. This tends to happen a lot if you use Names (either by typing directly in the box left of the formula bar or using Menu: INSERT>NAME>DEFINE&) and have merged sheets for different workbooks into one.


Save a backup, just in case... and run this VBA code to clean up then Names Collection of the workbook:


Sub DEL_BORKEN_LINKS_IN_NAMES()

    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

        If nm.RefersTo Like "*REF!*" Then

            MsgBox nm.Name & " " & nm.RefersTo

            ActiveWorkbook.Names(nm.Name).Delete

        End If

    Next nm

End Sub

2
2,272 Views

Comments (1)

CERTIFIED EXPERT

Commented:
Voting Yes, Will try your Code when I see this error,

Ravi.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.

Get access with a 7-day free trial.
You Belong in the World's Smartest IT Community