Excel hyperlink to a SharePoint list, library, or document is modified upon saving, and your changes are not retained.

 The head banging caused by this one will help flatten out any bumps on your noggin.

Symptoms:

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.

Cause:

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”

to

 “C:\MyLocalFolder\ 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.

Resolution:

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.

Advertisements

About Joy Lavigne (Adkins)

SharePoint Administrator for a mid-sized organization. Frequent speaker at SharePoint Saturday events. Teller of terribly corny jokes. View all posts by Joy Lavigne (Adkins)

3 responses to “Excel hyperlink to a SharePoint list, library, or document is modified upon saving, and your changes are not retained.

  • Ray Williford

    Joy,

    I have a similar problem only it is with Word:

    If I put in a hyperlink in a Word (Office 2010 if it matters) document such as:

    Go to this link:
    http://company.com/mysharepointsite/anotherdocument.doc

    then, put the document in Sharepoint at “mysharepointsite” as firstdocument.doc. The link works fine, it finds “anotherdocument.doc” at that site.

    If you can help, please let me know. I have posted the question on both Word and Sharepoint forumns, but no responses yet.

    Now, save firstdocument.doc to myfolder on the local hard disk. Then, open the copy in myfolder, and the hyperlink now looks like:

    file:///c:\myfolder\anotherdocument.doc

    What the hey?? Why did Word do that? Or, was it SharePoint that did it? Note that in the case above, the hyperlink base in the properties of the file is null.

    • Joy Earles

      This sounds like an issue with relative vs. absolute hyperlinks. Word by default updates your links on save, so that, for instance, you don’t copy a file from the S: Share drive to the C: local drive and accidentally leave hyperlinks/bookmarks to the original document or related documents on S:. I don’t time today to truly check if that is the issue, but look at this KB article (http://support.microsoft.com/kb/903163) and see if it sounds like the issue you’re having. There’s a general Word setting you can turn off to stop the automatic updating. If it is behaving as if there is a base set, but there is not one, try the trick from this blog where you set an arbitray hyperlink base, save your changes, and then remove the hyperlink base and save again.

      • Ray Williford

        Thanks for reply, I checked the KB article again and I definitely am putting in absolute hyperlinks. Tried the set base, save, clear base, save again, and the problem is not resolved. As for the automatic update settings, I found two: one in the Advanced/General for update automatic links at open and the other in same section/Web Options to update on save. I cleared both and tried again. The links still get changed from http://… to file:\\\c:… when I save the document from Sharepoint to local drive.

        This is a real puzzle. Can you try it at your end? Maybe this only affects US users 😉

        Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: