<

Excel "Invisible" broken links

Published on
8,368 Points
2,168 Views
2 Endorsements
Last Modified:
Approved

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
Author:Leon822
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Get 7 days free