Saving Values in Excel when using RTD

[Guest]

I have an Excel spreadsheet that gets up to date values from an external
source using RTD. At the end of the day, I save the sheet. If I open the
sheet and my external source is not available, I have a choice to update, or
not update the sheet. Even if I choose, DO NOT UPDATE, the values go to NA
(because the external data source is not there).

Is there a way to save the values in the sheet so that even though my
external source is not available, I can view the last values that were saved
in the sheet?

 

[Guest]

Menu:
Tools->Options->Calculation->Save External Link Values

 

[Guest]

Thank you for your repsonse. I tried your suggested fix prior to posting
here and the values are still not saved.

Any other thoughts would be appreciated.
Thanks

 

[Guest]

Nhench,

What BAC suggested, and you had already tried should have worked, so
something else is happening.

I still have a copy of an RTD sheet; it still has the final values even
though it is several years and even more miles removed from its source. It
this same sheet, I have macros that I used to use to turn updating on and off
from VBA, so I know that this can be done.

When you choose not to update when you open, this should turn off (un-check)
the “Tools|Options|Calculation|Update remote references†check box, and in
all likelihood it does. You need to look for whatever is turning updating
back on. It might be an On-Open macro that turns on updating despite your
choice to not update, or a macro that runs on some other trigger than On-Open.

Some things to try:
1. Look at the Update remote references check box the next time the sheet
goes NA on you and see if it is checked.
2. Look in “Tools|Macro|Visual Basic Editor†to see if there are any modules
of code attached to the workbook. Also right-click the worksheet tab (or
tabs) on which the RTD links exist and go to “View Code†to see if there is
an “On Change†event driven macro that turns updating back on, or just
updates the sheet.
3. Make sure you uncheck Update remote references before you save the sheet
(in addition to the save values that you already tried). This should not be
necessary because choosing not to update on open should turn it off then, but
try this anyway, just to make sure it is off.

In other words, check and make sure Update remote references turns off when
you choose not to update on startup, and then look for what is fighting you
to turn it back on.

For the sake of others having this same problem, please let us know if this
answers the question, or if further help or clarification is needed.
Thanks
SongBear

 

[Guest]

Thanks SongBear, You make an excellent point with macros turning update
functions back on. The vendor of the external applications has hooks into
the sheet- vba code that is pw protected, I would bet that is where the
problem is.

thank you again for your reply!