Hi,
I have an annoying problem with a new excel tool just developed. There is a back-end source excel file on Sharepoint that is linked to a "client" excel file that links back to that source spreadsheet on Sharepoint. There is an annoying problem that if the client is copied to a local file on a user's device that is not synced with their OneDrive or Sharepoint, then all is good, and the links back to the Sharepoint file work perfectly. If, however, that file is placed in a directory which then syncs with Sharepoint or Onedrive, then the links get changed to a relative path and breaks, giving an error message. I've tried everything, including adding an entry in the Info -> Advanced Properties -> Hyperlink base which I saw recommended somewhere, but still no resolution. I can see that the path to the source is updated from the central location on Sharepoint to something like ...-my.sharepoint.com/personal/... etc, so it's trying to resolve it in the new location as a relative link. I cannot for the life of me figure out how to make the original link path stick (i.e. make an "Absolute link" that remains as such in all cases, rather than reverting to a "Relative Link" which seems to be happening. In all cases, I am opening the file with the desktop app and not the web UI version.
I have by way of a simple example created and attached two excel files called Source and Destination. The Destination file references a cell in the Source excel file. The simplest way to reproduce the issue is when I copy this Destination file to a location on my Onedrive (just using Copy on Sharepoint), the problem occurs and the link to the Source file on Sharepoint gets pointed by excel to a URL that contains ....-my.sharepoint.com/personal/..... This happens also if you download and open the file from a folder that is being synced with Sharepoint - once the file has synced to the cloud.
We're on Office365 and the Excel version is Version 2102 (Build 13801.21050)
If anyone had any experience in this or knows what to suggest, then I'd be delighted.
Bookmarks