Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel - stop external references

Posted on 2000-04-11
6
Medium Priority
?
486 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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
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 40 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

722 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