How to avoid SharePoint system changing the local URLs of Excel?

Question

When I'm uploading excel files into SharePoint system all the relative URLs of the excel file is getting changed.

I've tried unchecking "Update links to other documents when calculating this workbook" but that didn't help me!

Any solutions for this will be appreciated! Thanks.

Answer 1

To prevent Excel from updating local/relative links to SharePoint when saving OneDrive-synced-to-SharePoint files locally:

Excel Options > Trust Center > Trust Center Settings > External Content > "Prompt user on automatic update for Workbook Links" or "Disable automatic update of Workbook Links"

It worked for me, links to files in the same folder keeps pointing to my C:\ drive, even if the folder is synced with OneDrive to a SharePoint site. I have also tested that when files A and B are open, with A linked to B, if I save B with another name, let's say B2, link is correctly updated in A.

If somehow you got a link updated because you had the "Enable automatic update for all Workbook Links (not recommended)" option selected, and if Excel is too long to update the link with the Data > Edit Links tool, you can still manually update it like this while the file is not open:

1. unzip your Excel file, you should get a folder with the same name
2. find the relation file for the link: \xl\externalLinks\_rels\externalLink1.xml.rels
3. change the relative link to a url encoded path after the Target parameter without changing anything else (with notepad.exe for example) :

<Relationship TargetMode="External" Target="file:///C:\Users\AAA\fichier%20étude.xlsx" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/externalLinkPath" Id="rId1"/>

(only the space character seemed to be encoded in my case)

4. re-zip the content of the folder (and NOT the folder itself, this is very important!)
5. rename the zip file to something different from your original file name (typically add something at the end") and change the .zip extension to the same extension that your original file (.xlsx or *.xlsm generally)

Answer 2

Links to external workbooks are created in a relative manner whenever possible. This means that the full path to the source data file is not recorded, but rather the portion of the path as it relates to the linked workbook. So when we click on the links in a file stored in SharePoint site, it will find the linked files in the site instead of the files in client.

Learn more about Relative link in Excel.

If you want to create a hyperlink with the absolute path, use Hyperlink function.

=HYPERLINK("<excel file location>","<text to display>")

Answer 3

OneDrive >>> Settings >>> Office >>> uncheck "Use Office applications to sync Office files that I open."

This fixed all our problems with hyperlinks - both files and folders.