If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
Need vb method to handle: Links to 'AAA.xls were not updated because...
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. |
#3
|
|||
|
|||
Cant use BeforeClose event to recalculate
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. |
#4
|
|||
|
|||
Need vb method to handle: Links to 'AAA.xls were not updated because...
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 |
#5
|
|||
|
|||
Need vb method to handle: Links to 'AAA.xls were not updated because...
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 |
#6
|
|||
|
|||
Need vb method to handle: Links to 'AAA.xls were not updated because...
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 |
#7
|
|||
|
|||
Need vb method to handle: Links to 'AAA.xls were not updated because...
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 |
Thread Tools | |
Display Modes | |
|
|