Excel "Invisible" broken links

Published on
8,315 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

1 Comment
LVL 18

Expert Comment

by:Ravi Agrawal
Voting Yes, Will try your Code when I see this error,


Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month