Full Hyperlink path in Excel

Question

I've got a column in an Excel file, and in it I have only values like 'ReleaseDoc' but they are all hyperlinks to something like this:

\\server1\folder1\subFolderA\subFolderB\SubFolderC\RealseaseDoc.doc

Now, what I want to do is to have in another column the path for the file, extracted from the hyperlink, and I've done that with this macro function

Function HLink(rng As Range) As String
If rng(1).Hyperlinks.Count Then HLink = rng.Hyperlinks(1).Address
End Function

in each cell I call this function with the appropriate cell from where I want to extract the address

The problem is that what I get displayed is something like this:

../../../SubFolderB/SubFolderC/RealeasesDoc.doc

I don't see the full path to the docs, can anyone help with this vba function?

PS: I've already searched the posts and found nothing like this

Thank you

Answer 1

Excel will change the address to a relative address whenever it can. To prevent this, you need to set the Hyperlink Base property. In Excel 2010, click File - Info, Show More Properties if necessary, and enter a Hyperlink Base of C:.

If there is no Hyperlink Base, it's assumed to be the path of the workbook. By setting it to C:, you force Excel to show you the whole path because it can't create a path relative to C:. If you make your Hyperlink Base \server\folder1\, you will get a relative path because Excel can make a path relative to that.

So if you want to guarantee full paths in Address, make the Hyperlink Base property a different share than the workbook or a different local drive than the workbook.