Solved

Excel - stop external references

Posted on 2000-04-11
6
473 Views
Last Modified: 2011-04-14
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 ?
0
Comment
Question by:Praetor
6 Comments
 
LVL 4

Expert Comment

by:Noggy
ID: 2704814
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
 
LVL 22

Expert Comment

by:ture
ID: 2706848
Praetor:

Have you this one checked? You should!

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

Ture Magnusson
Karlstad, Sweden
0
 

Author Comment

by:Praetor
ID: 2707480
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 22

Expert Comment

by:ture
ID: 2707536
Even though you have checked 'Ask to update automatic links' ???

Strange!

/Ture
0
 
LVL 6

Expert Comment

by:bkpchs237
ID: 2819097
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
 
LVL 1

Accepted Solution

by:
Thibault earned 20 total points
ID: 2836850
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

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now