Get rid of links cannot be updated message

Microsoft Excel 2003 (full product)
February 11, 2010 at 10:11:36
Specs: Windows XP
I have multiple workbooks, each workbook contains one cell that links to a master workbook. They update correctly, even when I try to update the links the status says "ok." So, if the link works properly in each file, why do I keep getting the "one or more links cannot be updated" message??
No, it is not difficult to just hit continue each time I open a workbook but I run a macro that pulls one sheet from each of these workbooks. Thus, I have to sit and watch the macro run so I can hit "continue" a hundred times as it opens and closes the workbooks. Any thoughts?

See More: Get rid of links cannot be updated message

Report •

February 11, 2010 at 10:40:35
Have you looked at your named ranges to see if you have references to named ranges that no longer exist? I've heard this can cause the problem.

In addition, John Walkenbach offers some advice on where links are hidden in workbooks. Charts and named ranges are often the root cause of the problem.

Report •

February 11, 2010 at 10:46:54
The links work properly, it just references one cell in another workbook. I copied and pasted all the data into a new workbook, re-linked the cell, then hit save-as and wrote over the original file. When I re-opened the book, the links updated and the error message was gone. I don't know what is different in my original files but I can't do all of that copy and pasting for a hundred books... Can you think of any settings that I may need to change?

Report •

February 11, 2010 at 11:21:05
I understand that the links work properly - at least the ones that you know about.

What I was offereing was a suggestion related to finding links that you don't know about, such as, as I said, references to named ranges that no longer exist.

Did you try the tip I suggested from John Walkenbach's site?

Report •

Related Solutions

February 11, 2010 at 11:31:18
Yes, I deleted everything in the Define:Names option to no avail...

Report •

February 11, 2010 at 12:01:16
Any charts in the workbook?

Report •

February 11, 2010 at 12:09:32
Not that I know of... It is a very simple worksheet but I didn't create it, is there a way to check and see if there are charts?
Also, there are no hidden sheets or workbooks and I do not know what "custom dialog" is. I assume there isn't any of this either but mayber there is a way to check?

Report •

February 11, 2010 at 12:14:36
Other things I tried
1.) I broke the one link (that I am aware of), saved and re-opened. No message.
I re-linked, saved, re-opened. Got the message again...

2.) Do not get the message when the Source workbook is open, only when it is closed.

3.) Ensured the Auto-calculate feature is checked in both sheets

Report •

February 11, 2010 at 12:39:02
re: 2.) Do not get the message when the Source workbook is open, only when it is closed.

How did you relink it?

Has the source workbook moved from it's original location?

Report •

February 11, 2010 at 12:47:29
Sorry, those were 2 separate things that I tried, I re-linked by typing "=" then selecting the cell from the other book. The source book is open when I re-link it, no it hasn't moved since the links were created.
#2 should read: If I open source book, then open destination book, the message does not appear. If I open destination book but the source book is not open, I get the message.

Report •

February 11, 2010 at 12:52:05

Can you post your link by copying it from the formula bar, with the source workbook closed.


Report •

February 11, 2010 at 12:59:22
is this what you are requesting?
='S:\Hallmark\Weekly-CL\[C-A1.xls]Central - Weekly'!$I$4

Is there somewhere I can attach the workbooks so you can see what is happening?

Report •

February 11, 2010 at 13:03:33
Last questions, then I think I'm done. Sorry!

You may know this, but I don't know, so I'll throw it out...

Typically when you create a link to an open workbook, you'll see the workbook name, sheet name and cell reference after the = sign. Once you close the source workbook, the link should automatically update to include the full path to the source.

So, is that happening? If not, then the link obviously can't be updated unless the source book is open. (I'm guessing that this is not the problem.)

Assuming the full path to the file is contained in the link, is it possible that the workbook doesn't have permission to access the source workbook when it's closed? Or maybe it has trouble following the path, maybe because of some drive name alias or broken network link?

As a test, have you tried saving the source workbook to a different location - someplace "nearby" - to see if the path to source file is the problem?

I'm just shooting in the dark here...

Report •

February 11, 2010 at 13:06:34 this something new that just started happening. Did the links always work in the past and it's only recently that you started getting the errors?

Report •

February 11, 2010 at 13:20:32
Good suggestion! The links do update to the full pathway... I tried moving the source to a different file (even a different network drive) then re-linking, but get the same message.
This is something that has always occured in these books however I have another set of books where a few are getting the message. Nothing has changed, but in 4 out of 100 books, the message started popping up! It's so strange!

I really appreciate all your help, I am at a loss!

Report •

February 11, 2010 at 13:30:56

The link looks OK and I tried it by making the same path on my drive S: and creating the file C-A1.xls.

The link works OK, but although I don't get the '...cannot be updated' message I do get a prompt to Update the link - even if the prompt for updates has been turned off in Links - Startup prompt.
The Update prompt does not appear if the linked file is already open.

Interestingly the startup prompt performs correctly in Excel 2007.

Sorry - this doesn't get you any further forward, but at least there doesn't seem to be anything fundamentally wrong with the link.

Have you looked at the links from VBA.

Create a 'do nothing' program in VBA.

Sub LinkTest()
s = Range("A1").Text
End Sub

Single step the macro with f8 to highlight the s= line.
Now add a watch - right-click anywhere in the macro code and select Add Watch...
Enter ActiveWorkbook.LinkSources - click OK
Expand the + in the Watch window against ActiveWorkbook.LinkSources

Is there just the one link.


Report •

February 11, 2010 at 13:39:12
That's not a "do nothing" program....this is a "do nothing" program:

Sub LinkTest()
End Sub

Sorry...couldn't resist! ;-)

Report •

February 11, 2010 at 13:39:33
Just the one link, here is what comes up...
: ActiveWorkbook.LinkSources(1) : "S:\Hallmark\Weekly-CL\C-A1.xls" : Variant/String : Module1.LinkTest

I find it interesting that if I copy and paste everything into a new workbook then write over the old one, I do not get the message. It has to be a setting! Grrrrrr....

Anyway, out of curiosity, what does Add Watch do?

Report •

February 11, 2010 at 14:24:19
Go here to read about the Watch Window and other VBA debugging tips.

Report •

February 11, 2010 at 14:45:28
Hi DerbyDad03,

In the program's defense:

'It did nothing in particular and did it very well'

(with acknowledgments to Messrs. Gilbert and Sullivan).

Report •

February 11, 2010 at 14:49:39

If it turns out to be the only solution - to create a whole new series of replacement workbooks - the task can probably be automated.


Report •

February 11, 2010 at 14:54:27
Great, I think I changed something. Now when I hit Update links, I have to go back and re-select the file it is linked to. Almost like I hit 'change source" instead of "update values." After this, the status says "value updated from source...." instead of just saying "ok." How do I get it back to just updating the links instead of making me select the file all over again?

Report •

Ask Question