Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel 2013 Change all link sources

Posted on 2015-02-05
7
Medium Priority
?
299 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 664 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 664 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 672 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 49

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

597 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