Hi,
We're moving our Excel files to a new server in a new directory structure. Unfortunately, all of our Excel files created by the accounting department use links to worksheets in other files which are all over the place, i.e. we're moving from \\oldserver\share\finance\ to \\newserver\share\finance\accounts\
To give a picture of the situation, there are over 11,000 Excel files, a lot of which contain 3 to 30 links to other files. The vast majority of these have so many dependencies that if one contains broken links, hundreds of others will be missing data, so they all need updating at the same time.
I've started cobbling together a macro that will trawl through each one and update the links. I've started to build this using help from the following two forum threads ...
Recursive directory file search (that works with Excel 2007/2010):
http://www.mrexcel.com/forum/showthread.php?p=1228168#1253882
Mass changing of Excel links (hyperlinks, not connections):
http://www.dbforums.com/microsoft-excel/1074340-mass-changing-excel-links.html
I'm about 20% of the way to solving the problem via a macro, however I'm wondering if there's a better solution going forward, rather than just updating the links with the new folder paths .. ?
Is there a way of getting Excel to link to other Excel files/worksheets via a connection file that contains file/folder paths? i.e. Change the folder path in a single location, then all Excel files will use this to point to the correct path? This seems to make much more sense than individually entering the file path in every single Excel file, which later will need to be updated again next directory or server change.
Any help on this is much appreciated !
Cheers
Andy
We're moving our Excel files to a new server in a new directory structure. Unfortunately, all of our Excel files created by the accounting department use links to worksheets in other files which are all over the place, i.e. we're moving from \\oldserver\share\finance\ to \\newserver\share\finance\accounts\
To give a picture of the situation, there are over 11,000 Excel files, a lot of which contain 3 to 30 links to other files. The vast majority of these have so many dependencies that if one contains broken links, hundreds of others will be missing data, so they all need updating at the same time.
I've started cobbling together a macro that will trawl through each one and update the links. I've started to build this using help from the following two forum threads ...
Recursive directory file search (that works with Excel 2007/2010):
http://www.mrexcel.com/forum/showthread.php?p=1228168#1253882
Mass changing of Excel links (hyperlinks, not connections):
http://www.dbforums.com/microsoft-excel/1074340-mass-changing-excel-links.html
I'm about 20% of the way to solving the problem via a macro, however I'm wondering if there's a better solution going forward, rather than just updating the links with the new folder paths .. ?
Is there a way of getting Excel to link to other Excel files/worksheets via a connection file that contains file/folder paths? i.e. Change the folder path in a single location, then all Excel files will use this to point to the correct path? This seems to make much more sense than individually entering the file path in every single Excel file, which later will need to be updated again next directory or server change.
Any help on this is much appreciated !
Cheers
Andy
