Solved

Excel 2013 Change all link sources

Posted on 2015-02-05
7
291 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
[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
7 Comments
 
LVL 44

Assisted Solution

by:Rainer Jeschor
Rainer Jeschor earned 166 total points
ID: 40593139
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
ID: 40593149
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
ID: 40593154
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
ID: 40594811
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 47

Expert Comment

by:Martin Liss
ID: 40660520
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

Office 365 Training for Admins - 7 Day Trial

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.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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