Excel 2013 Change all link sources
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
ASKER
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_<l
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.