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....
18 comments:
Thank-you! I was looking for this information all over the internet!!!
You're welcome. Thanks for reading and commenting!
Thanks for posting, but...I'm a bit confused...how does setting the 'hyperlink base' to C:\ PREVENT excel from turning absolute hyperlinks into relative hyperlinks? It sounds like this would CAUSE the corruption. I don't mean to doubt you, but the logic escapes me, (that's nothing unusual for me:-)
I'd like a little more reassurance, before trying your solution, since it would take me several weeks to rebuild my spreadsheet's hyperlinks if this went awry!! - BobW
Hi BobW, thank you for commenting.
I agree. I also don't understand the logic of how setting the hyperlink base to C:\ forces Excel to create links that have absolute paths, either. And I totally understand Skepticism. I was skeptical myself, so I tried it on a simple test case.
Just want to be clear that there are situations that you don't want to use this setting for. For example, suppose everything you link to resides in the same directory as the spreadsheet, and you want to ZIP that directory and everything in it and send it to someone. In this case, you'll want relative paths, so a hyperlink base of C:\ would be a bad choice.
You can test it easily enough with your own test cases. Just start two new spreadsheets called relative.xls and absolute.xls. In absolute.xls, set the hyperlink base to C:\ as described above. Then create one hyperlink in each spreadsheet. Save and close them, and then move them to another directory. Re-open each spreadsheet and examine the links. Which one do you prefer?
Good luck!
Thank you so much! I already had my links messed up 5 times and now hopefully they will stay as we want.
For those who use Office 2007, you can access the properties by going to Prepare under the windows button and clicking properties. This opens an bar on top the the spreadsheet. Click Document Properties and then Advanced Properties and follow the instructions above.
Good Luck!
You're welcome! And thank you for explaining how to access the document properties in Excel 2007. I have it at home, but I don't use it to link to other documents at home. I still use Excel 2003 at work -- I can't stand the ribbon.
A better option may be the HYPERLINK worksheet function. E.g. =HYPERLINK("\\UNC\full\path\to\file\abc.xls","Click here")
Sweeeeet! thanks.
Thank you, Kelsey. Yes, I think using the HYPERLINK function would be better for what we're trying to do. I can add that option to my clipboard manager so that when I paste the link, it can be the parameter to HYPERLINK. Thus the method would be just as easy for me as pressing Ctrl-K Ctrl-V. (I do a lot of links, sometimes.) As for my coworkers, they'll probably continue to change hyperlink base to c:\, though.
You're welcome, Sir Raja!
FYI
In Excel 2010:
File -> Info -> Show All Properties
Update Hyperlink Base in the list on the right
Thank you for your comment explaining how to find the hyperlink base setting in Excel 2010!
Thank you. Interesting article. In 2016 File-Info click on the arrow next to Properties and select Advanced Properties. I found that putting C:\ in the Hyperlink Base in 2016 does force to the UNC for all others except C:. Hyperlink function seems to be the answer.
In 2016, but I'd be more inclined to put I:\ or some arbitrary drive letter that's not likely to be used and then the full path is used even for the C: drive.
Thanks for all the continued commenting on this!
I especially like the idea of specifying a non-existing drive letter.
I still use Excel 2003 unless I need more than 65535 rows. And if I do need more rows, I use remote access to operate a computer that has a newer version, such as Excel 2010.
Thanks, I've also been searching for a solution of this problem for some hours. For other users, I've used this in Excel 2016. Appreciate it!
Thanks, but if like me you want to change an existing workbook I wrote a macro...
Sub RemoveRelativePath()
Dim relValue As String
Dim absValue As String
Dim sFormula As String
relValue = "http://sharepoint/" '- the path that Excel has updated to the you want to remove
absValue = "C:/" '- the path that you really wanted
sFormula = "=HYPERLINK(""" _
& Replace(Selection.Hyperlinks(1).Address, relValue, absValue) _
& """,""" _
& Selection.Hyperlinks(1).TextToDisplay _
& """)"
Selection.Formula = sFormula
Selection.Hyperlinks(1).Delete
End Sub
Thanks for that idea.
Usually the relative paths look something like this:
"..\..\..\Images\JobX\LotA\image.jpg"
"..\..\Scans\YYYY\MM\DD\scan123.pdf"
"..\Specs\CustN\ID123.pdf"
So the macro would have to know where in the file structure the file exists in order to determine the search/replace values.
It's certainly better than retyping them, and a fun coding challenge, too.
Post a Comment