Need vb method to handle: Links to 'AAA.xls were not updated because...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave B - 14 Oct 2003 15:29 GMT

Excel 2000, Windows NT

We have automated files that run at night with no user to click on an error
message. Since we have shared files, often someone will change something in
a file while in Manual calculation and then save the file. The automated
files that link to the file will stop with the message: " Links to 'AAA.xls'
were not updated because 'AAA.xls' was not recalculated before it was last
saved. To update links with the current values in 'AAA.xls', click OK. "
etc.

I know I can put a 'calculate before save" code in every file (probably the
best method) but I was wondering if I could put vb code in the file trying
to link to this (not calcualted before save) file to handle the error (link
to current file) and move on.

Reply to this Message

David Hager - 14 Oct 2003 15:41 GMT

You can run an application level BeforeClose event from an add-in to force
calculation in every workbook on close.

David Hager
Excel FMVP

> Excel 2000, Windows NT
>
[quoted text clipped - 10 lines]
> to link to this (not calcualted before save) file to handle the error (link
> to current file) and move on.

Reply to this Message

Dave B - 14 Oct 2003 16:36 GMT

We have very complex user and file groups whereas we don't want to
recalculate the entire file before save, although we want the updated cells
available to other files without creating an error message. Display alerts
= false will not work because this error occurs before auto_open and before
workbooks_open.
Thanks for the input. Any other ideas?

> You can run an application level BeforeClose event from an add-in to force
> calculation in every workbook on close.
[quoted text clipped - 22 lines]
> (link
> > to current file) and move on.

Reply to this Message

Bill Manville - 16 Oct 2003 14:28 GMT

> Since we have shared files, often someone will change something in
> a file while in Manual calculation and then save the file. The automated
> files that link to the file will stop with the message: " Links to 'AAA.xls'
> were not updated because 'AAA.xls' was not recalculated before it was last
> saved.

The person doing this must have "don't recalculate before save" set as well as
manual calculation for this to occur.

I wonder if they need that setting or whether it is accidentally set that way;
I can see little virtue in saving a workbook with incomplete calculation when
other workbooks need to link to it - it will need to be recalculated at some
point before the information in it can be relied upon.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Reply to this Message

Dave B - 17 Oct 2003 19:21 GMT

Thanks Bill,

I really respect your opinions and look for answers by you because I know
they are good.

All these guys are MIT graduates (and much better programmers than I but
they are busy doing Bond Market Analysis). None could come up with a
solution and said "I'd be their hero" if I could solve it. They say they do
have good reasons NOT to recalculate the entire workbook before saving. I
too wonder why the workbooks can't be recalculated before save.

Dave

> > Since we have shared files, often someone will change something in
> > a file while in Manual calculation and then save the file. The automated
[quoted text clipped - 13 lines]
> MVP - Microsoft Excel, Oxford, England
> No email replies please - respond to newsgroup

Reply to this Message

Albert - 11 Nov 2003 03:10 GMT

One valid case is if the source workbook has too many formula
references (more than 65K?) so that Excel cannot determine whether the
workbook has been calculated and always displays "calculate" in the
status bar (see MS knowledge base article Q243495). I have such a
workbook which I am referencing from another and the second workbook
continually claims the first one was not recalculated before saving.
This is very annoying and I wish I could just turn the message off.

> Thanks Bill,
>
[quoted text clipped - 33 lines]
> > MVP - Microsoft Excel, Oxford, England
> > No email replies please - respond to newsgroup

Reply to this Message

Albert - 11 Nov 2003 04:48 GMT

I have found a solution to the problem. Try this:

In Excel, select Edit | Links, click "Startup Prompt" button, select
second option "Don't display alert, don't update". Now add the
following VB code:

Private Sub Workbook_Open()
Application.DisplayAlerts = False
ExcelLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(ExcelLinks) Then
For i = LBound(ExcelLinks) To UBound(ExcelLinks)
ActiveWorkbook.UpdateLink Name:=ExcelLinks(i)
Next i
End If
Application.DisplayAlerts = True
End Sub

This seems to work for me in a set of test workbooks. UpdateLink
generates and error if the update fails (which you can further trap to
display an appropriate message).

> Thanks Bill,
>
[quoted text clipped - 33 lines]
> > MVP - Microsoft Excel, Oxford, England
> > No email replies please - respond to newsgroup

Reply to this Message