We have a set of spreadsheets that are linked for YTD and Performance information and calculations. There are 17 "stores" which fill in their monthly data and forward to the office for consolidation. The new months are copied into a master file of all stores. These files are linked to a YTD file. They all reside in a common folder. It seems when the folder is moved or copied the links do not get updated and therefore contain the old file references which are no longer available (change of computer or file location). This seems especially to occur if the set of files is sent by email and therefore packaged as zip files. EDIT LINKS straightens them out but takes an iterative execution (one per store). Is there any way to prevent this and if not a more global way to EDIT all links?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Links not updating when files are moved or emailed
Answers
Honestly, if Edit Links works consistently for this task and it is only 17 files, I would stick with that.
It might also work to have all files in the same directory and to then have them all open at the same time and refresh the links.
You could also just change the process by which you send the files and aggregate the data and make it so the workbooks are not linked but just use them to import the data inot the main file, or use Office online and have only 1 main source for all of the files or you could try Google Sheets.
Sending linked files is often a pain, unfortunately.