Excel automatically and randomly changes workbook relative links to absolute links.

Copper Contributor

Hi everyone,

 

I am working with sevral large workbooks that are linked together (total over 110 Mb of data). Periodically, I do backups trying to copy all workbooks together to another drive hoping that all workbook links will follow and refer to the appropriate files in the same drive (relative links). Recently, Excel started randomly changing my relative links to absolute links by adding the mention "file:///" whenever a workbook link appears in a formula.

 

We are talking about hundred thousands of formulas and different links that I can't change manually. Once in a while it works (copying the files all together to a different drive or forder) maintains the relative link but sometimes it doesn't and then, the links change to absolute links in the copied and in the original files.

 

Any idea ?

 

Thanks,  

6 Replies
Same thing is happening with our Excel files. my users download the excel file locally and all the links to content in SharePoint libraries change to "..\..\..\..\..\:w:\t\CILearningServices\Content\EdXjYZ7J3R9Lgo6N25e2rrMBA8EPk3WBUUVXWrZZokiA4Q?e=YWrAWE". This breaks the links. I've got hundreds of links within my teams various excel files that aren't working. If the Excel file is opened from SharePoint and the link is clicked, it works. When downloaded, the links to SharePoint content break.
We make links to SharePoint content using the copy link feature and using the option of "people with permissions" or "everyone at 'my company'.
@Denis675, if you find an answer, please share it back here. Thanks :)
Well, they don't change to one single link, the SharePoint links lose the front part of their URL. It's like it becomes relative instead of absolute.

@Denis675 

Two possible solutions to prevent the links from breaking. Note, these solutions will not fix any links that have already broken.

 

1. Disable 'update links on save'

Patrick2788_0-1673990278501.png

 

2. If all the files are pointing to the same directory, you could try setting a hyperlink base:

Work with links in Excel - Microsoft Support See: Set the base address for links in a workbook

Thanks @Patrick2788 for the very useful options tip. I have now made that setting change for the document I fixed about 60 links on and it seems to be working as expected. I love the base address feature and don't remember when that came into Excel. I thought it would work for me, but I found 4 other links to content with different base URLs. :o
Thank you @Patrick2788 for your answer. When you mention broken links, do you consider a link that starts (in a VLOOKUP formula for example) with "file:///D:..." as a broken link ? If so, are you aware of any possible solution to fix those links ?

Thank you @MickThornton for all your input as well !
'broken' being - you click the link and get an error that says something like file could not be opened. Direct hyperlinks to files on the network and internet has been an issue for a long time.