I rarely insert hyperlinks into an Excel spreadsheet. So every time I do, I get burned by Excel's predilection for turning Absolute paths into Relative paths.
Here's the scenario...
I'll use Excel to create a flat file database on my local hard drive. I'll include links to the file server on the network using UNC paths, such as \\File-Server\Share-Name\Path2File\MyFile.ext. (The Ctrl-k keystroke is really handy for creating hyperlinks, BTW.)
After building the table by adding fields in columns, adding formatting, and entering a few records in rows for testing, I'll consider the database to be production ready. At this point I'll upload it to the same file server that my links point to.
The next time a user opens the network version of that spreadsheet, Excel will convert those carefully-entered absolute links to relative links. So if the spreadsheet resides in \\File-Server\NewShare\Reports\Databases\LG.xls, the links to MyFile.ext turn into this: ..\..\..\Share-Name\Path2File\MyFile.ext. They work just fine while the spreadsheet remains in that location.
But suppose the user saves the database and then e-mails it someone else in the organization, or copies it to his or her local hard drive. The links are broken. Furthermore, there's no easy way to fix them. Search and Replace (Ctrl-h) won't find the ..\..\..\ in the hyperlinks. You'll have to either fix them manually or write a program to do it. And then the same thing could happen again, anyway.
But there is (thankfully) a trick to keep Excel from bastardizing your carefully designed links. But it's a preventive measure -- you have to know about the trick before you get stuck with the link conversion.
The trick is to open the File Properties dialog. Choose the Summary tab and enter C:\ in the text box next to the label that reads "Hyperlink base:". Be sure to OK your way out and save. These instructions are for Excel 2000 and 2003. Later versions probably have the same properties dialog, but the way to open it might be different.
Really, it's best to do this before entering any hyperlinks. Ideally, Excel would have an Option for this. But alas, all I could find was a checkbox to enable Excel to prompt for workbook properties.
Hopefully, by writing this post, I've seared this information into my brain so that the next time I'm tempted to enter a hyperlink, I'll remember to set this property.
Hmmm, it just occurred to me to code this property using VBA....
