Excel "Invisible" broken links

Published on
8,429 Points
2 Endorsements
Last Modified:

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:


    Dim nm As Name

    For Each nm In ActiveWorkbook.Names

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

            MsgBox nm.Name & " " & nm.RefersTo


        End If

    Next nm

End Sub

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