I'm new here and could really use a little help! I have a source xlsx with many docx linked to it. All files are in the same folder. I'd like to copy/paste this folder into a new location and have the source path to .xlsx automatically update to new location. Unfortunately, this simple idea does not have a simple fix (that I have found).
I've installed the macro from note 3 of "Word Fields and Relative Paths to External Files" by macropod (sorry, new user, can't post link to forum yet) into my word doc but it is not working. (I'm using windows 10 with MS 365 for business, all applicable files are in same folder)
Here are my steps:
1. Open docx and save as docm, import the macro, save docm
2. close all word and excel files.
3. copy files to new location
4. open moved docm, this prompts the following errors:
"Sorry, Excel can't open two workbooks with the same name at the same time." (pops up twice, then)
"Run-time error '6083':
Objects in this document contain links to files that cannot be found.
The linked information will not be updated."
(click debug) goes to VBA code line: ".SourceFullName = Replace(.SourceFullName, OldPath, NewPath)"
As part of my troubleshooting I've got it to barely work for small docs.
If, after I move the files, I open the moved xlsx file first, then the moved docm, it prompts the same error:
"Sorry, Excel can't open two workbooks with the same name at the same time." but this time it pops up MANY times (20+, seems dependent on # of links)
If I can click through all the popups the macro is effective and the link paths are updated. Unfortunately, some of the bigger docs have hundreds of links and I just can't click through all the error boxes.
It seems like it's so close, maybe I just have a word or excel option toggled differently than default? Any help is really appreciated, it would be so helpful if it worked better!