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

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.

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

David Hager
Excel FMVP

"Dave B" Not.Available wrote in message
...
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.

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?


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

David Hager
Excel FMVP

"Dave B" Not.Available wrote in message
...
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.

Dave B wrote:
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

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


"Bill Manville" wrote in message
...
Dave B wrote:
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

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.


"Dave B" Not.Available wrote in message ...
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


"Bill Manville" wrote in message
...
Dave B wrote:
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

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).


"Dave B" Not.Available wrote in message ...
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


"Bill Manville" wrote in message
...
Dave B wrote:
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