Excel - stop external references

I have a worksheet with references to external sources. I want to disable the data being updated everytime. How do I do it ? I've tried going into tools-options-calculations and deselecting the update remote references and save external link values. Then I reload my worksheet and it updates everything all again. How do I stop it doing this ?
PraetorAsked:
Who is Participating?
 
ThibaultConnect With a Mentor Commented:
It is quit easy to do it using Visual Basic for Application (VBA) :

There is a Visual Basic methode called 'AskToUpdateLinks' that you should set to false.
It applies to the application object and would look like :
Excel.application.AskToUpdateLinks = false
=> this will prevent from asking you to update links.

Then if you want then to update some links but not the others, do so :
Excel.application.ActiveWorkbook.UpdateLink Name:=Excel.application.ActiveWorkbook.LinkSources
Where Name: is the link you wanna update.(This example updates every links)

Good luck !
0
 
NoggyCommented:
You mean that you don't want to break the links entirely i.e. you may still want to update wrt the remote references occasionally?

For this:
1. Goto Edit|Links...
2. Select the Links you don't want to update (one at a time)
3. Select the Update Manual option button.
4. Whenever you do want to update the data, just go into the same dialogue and press Update Now.

Is this OK for you?
0
 
tureCommented:
Praetor:

Have you this one checked? You should!

Tools - Options - 'Edit' tab - Ask to update automatic links

Ture Magnusson
Karlstad, Sweden
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
PraetorAuthor Commented:
I've done all those things. Still when I load my worksheet I have to go to edit and undo reference update. It's a real pain as there is about 50 update references I have to undo. There must be a way to stop it doing this ??
0
 
tureCommented:
Even though you have checked 'Ask to update automatic links' ???

Strange!

/Ture
0
 
bkpchs237Commented:
You didn't mention which version of Excel you were in, but it works the same in E5, E7, E97 and E2K.

Open Excel.
File, Open, choose your file,
prompt for update links, select No,
go to Tools, Options, Edit tab,
uncheck "Ask to update automatic links"
hit the OK button.

Then save your file.

File, Close,
File, Open, choose your file,
you should no longer be prompted for the update.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.