The head banging caused by this one will help flatten out any bumps on your noggin.
You modify a hyperlink in an Excel 2007 worksheet in a workbook, changing the target to a URL of a web location such as a SharePoint library, list, or list item like a Word document. Upon save, the web hyperlink is modified with a different path appended to the beginning of the http: or https: full link. If the file location specified in the appended text is a local file path, the slashes in the web hyperlink are also reversed.
By design, Excel includes a method by which to set a hyperlink base location.* When a user specifies such a hyperlink base (such as “G:MyDrive/MyFolder”), they are then able to create a hyperlink by providing only the file name of a file within that location. Upon save, Excel will append the hyperlink base to the beginning of the file name. If the user provides a fully qualified path in a hyperlink, the append action does not occur, and the link is saved as typed.
At least, this is the expected behavior. In our case, no hyperlink base was specified, but Excel was behaving as if it had a hyperlink base of “C:\MyLocalFolder”. Additionally, if there had been a hyperlink base, the fully qualified web link provided should have caused an override of the append behavior, but this was not the case. Excel repeatedly changed “Https://MySharePointServer/MyTeamSite/MyList/Forms/AllItems.aspx”
which of course made the link unusable.
I suspect that at one time, the Excel workbook did have the hyperlink base in place, but that when the base was deleted, one or more “behind the scenes” settings were not updated, causing the odd behavior.
To resolve the issue, we provided a new hyperlink base to “reset” the hyperlink base settings. We used the path to the home page of our SharePoint intranet, but I suspect any hyperlink base, whether to the file system or web, would work by causing Excel to reset the hyperlink base settings. Excel then properly ignored the hyperlink base when given the fully qualified full link to the SharePoint web object.
Thanks to http://www.excelforum.com/excel-general/594179-my-hyperlinks-are-being-changed-when-i-hit-save-in-excel.html for hints on the cause.
*To view or edit the document properties in an Excel 2007 workbook: (File>Prepare> Properties) A document information panel appears. Select Advanced Properties from the dropdown arrow by Document Properties. The hyperlink base is on the Summary tab.