SOLVED

Links to workbooks not in same folder are absolute when we want relative

New Contributor

Have a bunch of files in a Cloud environment which uses a Desktop client.

If, in an Excel cell, we link to an external workbook which is stored in the same folder, there's no problem. Anyone can then open the file and those links will update. And if Mike were to go to the cell with the link, he will see:

C:\Users\Mike\Cloud\2022\22 Some Branch\Some Sub-Branch\Filename.xlsx

Whereas I will see:

C:\Users\Rusty\Cloud\2022\22 Some Branch\Some Sub-Branch\Filename.xlsx

If, however, the external workbook being linked to is not in the same folder, we end up with absolute file paths. If Amy created a link to a file in some other folder, all three of us (Mike, Kyle and Amy) would see:

C:\Users\Amy\Cloud\2022\22 Some Branch\Some Sub-Branch\Filename.xlsx

Obviously, update links will fail when Mike & I open the file because we have no C:\Users\Amy in our paths.

If setting the hyperlink base to something is the solution, what would I use? Cloud\2022 would be common to the set of 100 files we are working with, but there are a ton of branches and sub-branches.

If that's not the answer, what is? I have scoured the internet and am not seeing a clear resolution to something like this.

2 Replies
best response confirmed by rustys9999 (New Contributor)
Solution
By design, links to workbooks that are in (a subfolder of) the same main folder are relative. All other links will be absolute. That being said, it should not be very hard to write some VBA code that updates the links to your username.

But in my example, the link that becomes absolute IS, I think in a subfolder of the same main folder.

C:\Users\Amy\Cloud\2022\22 Some Branch\filename-wth-link-in-it.xlsx

 

C:\Users\Amy\Cloud\2022\22 Some Branch\Subfolder\linked-to-file.xlsx