Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel - stop external references

Posted on 2000-04-11
6
Medium Priority
?
490 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Hire Technology Freelancers with Gigs

Work with 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

Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

782 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