TheWaterbug
New Member
- Joined
- Feb 4, 2016
- Messages
- 15
- Office Version
- 2021
- 2019
- 2016
- 2013
- Platform
- Windows
- MacOS
Suppose I have ListOfLinks.xlsx that has formulaically-generated hyperlinks to directories and subdirs in a big directory tree like:
The wrinkle is that ListOfLink.xlsx lives outside of BigDirectory, and BigDirectory may get moved or renamed by a customer. So I need a way for the user to point to BigDirectory and restore the validity of the links. I was able to do this by putting Root.xlsx at the root of BigDirectory, e.g. BigDirectory\Root.xlsx, and then ListOfLinks.xlsx has one cell that links to Root.xlsx, e.g. D5 contains =[Root.xlsx]Sheet1!$A$1.
If Root.xlsx exists, but is NOT open, I can use FORMULATEXT(D5) to get the full path to wherever BigDirectory lives today, e.g. /share/user/stuff/BigDirectory/ and then append my text list of dirs and subdirs, and all the links work.
If BigDirectory gets moved or renamed, Excel properly prompts the user to find Root.xlsx, and upon Edit Source: Change Source: Close, everything works again.
But only while Root.xlsx is NOT open. As soon as I open Root.xls, the path disappears from FORMULATEXT (and from the actual formula bar) and then there's no way to extract the path. Note that I can't use a relative paths "down" from where Root.xlsx is, because ListOfLinks.xlsx may be anywhere, so I need to know where Root.xlsx exists in the file structure.
Is there a way to do this without VBA?
- BigDirectory\
- ACMERocketCars\
- Products\
- Customers\
- MarvinMartianArmaments\
- Products\
- Customers\
- Mars\
- Earth\
- Jupiter\
- ACMERocketCars\
The wrinkle is that ListOfLink.xlsx lives outside of BigDirectory, and BigDirectory may get moved or renamed by a customer. So I need a way for the user to point to BigDirectory and restore the validity of the links. I was able to do this by putting Root.xlsx at the root of BigDirectory, e.g. BigDirectory\Root.xlsx, and then ListOfLinks.xlsx has one cell that links to Root.xlsx, e.g. D5 contains =[Root.xlsx]Sheet1!$A$1.
If Root.xlsx exists, but is NOT open, I can use FORMULATEXT(D5) to get the full path to wherever BigDirectory lives today, e.g. /share/user/stuff/BigDirectory/ and then append my text list of dirs and subdirs, and all the links work.
If BigDirectory gets moved or renamed, Excel properly prompts the user to find Root.xlsx, and upon Edit Source: Change Source: Close, everything works again.
But only while Root.xlsx is NOT open. As soon as I open Root.xls, the path disappears from FORMULATEXT (and from the actual formula bar) and then there's no way to extract the path. Note that I can't use a relative paths "down" from where Root.xlsx is, because ListOfLinks.xlsx may be anywhere, so I need to know where Root.xlsx exists in the file structure.
Is there a way to do this without VBA?