Hyperlinks and Network Shares

The other day I wanted to insert a hyperlink to another spreadsheet.

I hardly ever put Hyperlinks into Spreadsheets – I almost always link to a web page. This time, however, I wanted to link to a document on the file server.

Insert – Hyperlink – Double click the file – done! “Wow! that was easy” I thought, then promptly attached and e-mailed the spreadsheet to the customer.

“Great spreadsheet” was the feedback, “but the hyperlink is broken – it’s pointing to my C: drive!”

It appears as though hyperlinks are stored by relative path (rather than absolute path) when the linked document is on the same Network Share. His e-mail program had saved the attached spreadsheet to C:\Temp before opening it, so the hyperlink also looked to the C: drive.

When I hover my mouse pointer over a hyperlink, the tool tip reveals the true path.

Here is me hovering over the hyperlink from my computer:

Here is my customer hovering over the hyperlink from their computer:

Fixed it – Here is my customer hovering over the fixed hyperlink from their computer:

What’s the difference between the first and last hyperlink?

The last hyperlink is actually a formula which forces an absolute path:

=HYPERLINK("Z\:SpecialFile.xls")

10 thoughts on “Hyperlinks and Network Shares

  1. one drawback to this is the customer needs to have the network drive assigned to the same drive letter.

    I would use the hyperlink formula too but instead of the Z: I will rewrite \MyFile…

  2. I’ve seen posts in the newsgroups that say if you set the hyperlink base (file|properties|summary tab) to something like C: (it should always exist???), that the links aren’t changed.

    But I like the worksheet formula version better. I’ve never had any trouble with that.

  3. How can you change the hyperlink from an absolute to a relative? My hyperlinks seem to be getting corrupted I have rewritten them, but sooner or latter they change from relative to absolute and stop working.

  4. The above is great if you have hyperlinks just in cells. I have the problem with hyperlinks being ‘corrupted’ when used with i.e. a shape in excel 2007.

    Can anyone help with that? Thanks!!!

  5. I’m frustrated. I’m using Excel 2007 and I’m inserting a hyperlink that will direct the user to go to another worksheet in the spreadsheet, but when I e-mail it to someone or try to access it from another computer the hyperlink does not work. The hyperlink is pointing to my local drive with my user id. I only want the hyperlink to point the worksheet, so I can e-mail the spreadsheet out and the hyperlink to work on any computer. Right now, I can only use the spreadsheet on my local drive.

    It appears that the hyperlink automatically creates the hyperlink to the c: drive. How do you change the hyperlink to only point to the worksheet with no reference to a temp file on the local drive?

  6. Thanks much for the tip. Worked great. I use J. Walkenbach’s PUP utilities to create an index when I”m giving a client a large number of files. works great but the drive gets hard coded into the hyperlink. so when the index and related files get moved to a thumb drive (for the client), the hyperlinks fail. what i will do now is just ask them to enter a drive number and then adjust the hyperlink formula to make the index work on the drive letter associated with the thumb drive.

  7. What if it’s not a specified file, but rather a location on your network?
    IE: In a document control spreadsheet, I had over 5,000 hyperlinks to each individual policy, procedure, work instruction and form. Each hyperlink ended up “broke” and referred to my c:/drive. I refused to remake them because I did not know how to prevent it from happening again and it took 2 full days to make all of them (not to mention my cramped hands from all those clicks directing the link!!)… instead, I made hyperlinks to where the file is stored.
    IE: M/QMS/Document Control/Current Documents/Work Instructions/

    Thank you!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.