Solved

Excel 2013 Change all link sources

Posted on 2015-02-05
7
273 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
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 46

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
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 the scrolling table in Microsoft Excel using the INDEX function.
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…

863 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

25 Experts available now in Live!

Get 1:1 Help Now