Solved

Excel 2013 Change all link sources

Posted on 2015-02-05
7
265 Views
Last Modified: 2016-02-12
we have workbooks with links to a number of other workbooks all have the same layout.  The master may have ten or more links and the formula in each cell references each of the linked files in a sum.

we have a vba method to ask for new path and then change the link sources with changelink method but for each link changed the sheet updates all formulas. so therefore the formulas are updated in each of 4000 cells for each of the ten or more changes resulting in a very long run time.  

Is it possible to change all the link sources before the formulas are updated once only
0
Comment
Question by:GMConway
7 Comments
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 166 total points
Comment Utility
Hi,
could you perhaps share the VBA how you update the links and how your process is executed - step by step?
I am not sure if this fit into your process, but you might be able to set the automatic update to manual using
Application.Calculation = xlCalculationManual

Open in new window

HTH
Rainer
0
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 166 total points
Comment Utility
You haven't given too much detail about what you are doing.

Are you using using a formula based system, such as:

=HYPERLINK(mypath,"Open")

So you only have to change one cell in Excel, and all the hyperlinks change.

If I'm not being clear, have a look at the video on this cataloging CD page to see how this works in action.

If this is the case, then your problem is the calculation in the other files? Is this right.

If so, then I would suggest a change of where you have this data.

Let's say you have your workbook (workbook A) linking to another workbook (workbook B). Workbook B has lots of calculations.

Instead, have your source a separate workbook with NO calculations (workbook C). Then workbook A can be linked to workbook C, as can workbooks B and D, E, F. This way, when you change workbook C without having to have the calculations from workbook B.

In essence: Have a central source which feeds all the other workbooks.
0
 
LVL 59

Accepted Solution

by:
Saurabh Singh Teotia earned 168 total points
Comment Utility
One of the things that which you can do.. is when you open the workbook in this workbook you can add this in the code:-

ThisWorkbook.UpdateLinks = xlUpdateLinksNever

Open in new window


and then when your code is run and it has update the formulas then you can do...

ThisWorkbook.UpdateLinks = xlUpdateLinksAlways

Open in new window


Or while starting your code you can set the update link properties to what i just mentioned...

Saurabh...
0
 

Author Comment

by:GMConway
Comment Utility
Thanks for the info posted, I will definetly try the ThisWorkbook.UpdateLinks = xlUpdateLinksNever  property to see if this helps.  unfortunately the excel is budgetry info which is sensitive so cant be shared.

Basically we have a series of excel work books where the sheet provides figures in 4 columns for 800 rows of different budget codes. in the master we have the same sheet but the cell valies are

= <link1>!C10 + <Link2>!c10_<link3>!c10_<link4>!C10_<Link5>!C10 +....   where ther may be upto 100 links in the formula summarised in the master.  So in the link list we have  a list of upto 100 worksheet links.

The linked workbooks are stored in a DM system so have to be downloaded to the local machine before the master is opened to allow the links to update on calculation of the master.

I have code to ask the user for a download folder and this then goes through the links and downloads all the files to the location given so then I have to update the links using the workbook.changelink method to update the source location but I found that this meant that if done in a for each loop on the linksource array when you changed the first link excel would go through and change that link reference in each cell then come back to update the next one etc.

I have now found that idf I work the other way round and update the formula in each cell directly then the link sources get updated automatically so I pass the usedrange into an array set calculation state to manual and work through each formula doing a text replace on the paths of the links and then set calculation to automatic and call a update links afterwards.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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