Solved

Excel - stop external references

Posted on 2000-04-11
6
484 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

634 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