<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Excel "Invisible" broken links

Published on
8,329 Points
2,129 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
1 Comment
LVL 18

Expert Comment

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

Ravi.
0

Featured Post

Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month