The "update links" and "save link values" should already be the default
settings, but yes as you surmised, they are workbook settings and not global
"all Excel files" settings.
"Sometimes if I happen to slip up and open the MAIN spreadsheet which reads
other spreadsheets prior to opening the ones it looks at, Excel 2007 states
it is updating links towards the bottom right and basically freezes the
program.
I know my spreadsheets have alot of links but what amazes me is that MY
dinosaur program of LOTUS 1-2-3 does not run into the same problem and it has
spreadsheets with just as many links."
In a way it's probably the advantages of being a 'dinosaur' --- it's not
filled with many features that probably most of the users never utilize (such
as all of the net-centric and group collaboration abilities). But, as you
noted, the slowdowns can even take place in Excel 2003. I had a project a
while back where I *THOUGHT* I was being really slick by not creating links
to data in other workbooks by setting up dynamic formulas to bring the data
in, convert to values and place where needed in the workbook. I found that
Excel was maintaining DDE links anyhow, even when the formulas referencing
the other workbooks were deleted and it was killing performance. I
eventually rewrote that whole section to open the other workbooks, import the
data directly and close the other workbooks when I was done with them - big
improvement in performance.
YOU ARE NOT the only one to encounter these issues. Performance has been an
issue with Excel 2007 since the day it was released. You should see what
happens if you happen to use charting/graphing and try to change anything on
a chart/graph!
As far as the "update links?" prompt - I'd go ahead and activate it. Then
if you know the other workbooks have not been altered since the last time you
used the main one, you can say NO and perhaps get into the use of it quicker.
Or even set calculation to manual and not update links, and wait until
you're ready to print/review results to have it update the links and
recalculate the workbook.
Here's a short macro that you could add to the workbook that will update the
links in it and recalculate it:
Sub BringUpToDate()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
Application.Calculate
MsgBox "Workbook Links and Calculations have been updated."
End Sub
Carlos said:
I'm using Excel 2007 and Excel 2003 but it seems like the problem is more
pronounced with 2007.
I do not have the spreadsheets set up in Automatic Calc so it is odd that it
takes so long.
When I go to Excel Options, I've have selected the following:
Under "When calculating this workbook:"
-Update links to other documents
-Save external link values
I just noticed that the above section indicates THIS WORKBOOK. Is there a
way to have all spreadsheets default to the settings within Excel Options?
Under General:
There is a option "Ask to update automatic links"...........I don't have
this selected but I suppose this prompts you prior to opening the spreadsheet
if I would like to update automatic links. Am I correct ?
Sometimes if I happen to slip up and open the MAIN spreadsheet which reads
other spreadsheets prior to opening the ones it looks at, Excel 2007 states
it is updating links towards the bottom right and basically freezes the
program.
I know my spreadsheets have alot of links but what amazes me is that MY
dinosaur program of LOTUS 1-2-3 does not run into the same problem and it has
spreadsheets with just as many links.
I would find it hard to believe nobody else has encountered the issues I'm
seeing. It makes using Excel 2007 VERY inefficient.
Please let me know if I should activate the other option which prompts if I
want links to be updated?
The only concern is that initially I would be ok if the links are NOT
updated but if I wanted to Calculate the spreadsheet..............I would
then want the spreadsheet to update LINKS prior to recalculating the
spreadsheet.
Thanks for all your help!!!
Carlos
:
What version of Excel are you using? I''ve found Excel 2007 to be much
slower than previous versions of Excel in performing such operations,
especially when Calculation is set to Automatic - seems that it wants to
recalculate the entire workbook with every cell's change in value.
As for automatically updating:
Excel 2003 and earlier: Tools --> Options --> [Calculation] tab, check the
box next to "Update remote references"
Same thing in Excel 2007: Office Button --> [Excel Options] button, then in
the "Advanced" group, scroll down about 3/4 of the way and in the "When
calculating this workbook" bunch you'll find the "Update links ..." item, and
in the next group there's also an item to ask if you want to be prompted
about whether or not to update them when the workbook is opened.
Hope this helps at least a little.
:
We are very puzzled about the amount of time it is taking to open Excel
spreadsheets containing links to other spreadsheets. We have been attempting
to program all our reports into Excel and find that Lotus 1-2-3 from 1995
opens spreadsheets MUCH quicker.
We've tried to find an Excel set-up that would help open the spreadsheets
quicker but no luck.
Another thing we've noticed is that our spreadsheets will not update the
links to other spreadsheets after hitting the Calculate button unless we have
all the linked spreadsheets open also.
Example: Spreadsheet A picks up figures from spreadsheet B, C, and D. If we
attempt to open Spreadsheet A, it will take us anywhere from 2 minutes to an
hour to open if spreadsheets B, C, and D are NOT open.
This does not happen in LOTUS 1-2-3 which has a setting to "update links
upon opening".
The ONLY way for us to open spreadsheet A quickly is to FIRST open
spreadsheets B, C, and D.
Also, if these other spreadsheets are NOT open then the figures in
Spreadsheet A would NOT update.
PLEASE HELP!!!!
Thanks
