CODEWITHSUNDEEP
excelvbahyperlink
Andrei Doanca
Andrei Doanca

Reputation: 251

Full Hyperlink path in Excel

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

Upvotes: 6

Views: 19295

Answers (2)

Mauro Brambilla
Mauro Brambilla

Reputation: 1

A solution is indicated here: https://support.microsoft.com/en-us/help/903163/how-to-create-absolute-hyperlinks-and-relative-hyperlinks-in-word-documents

The link drives to MSWord support page however it works also for MSExcel (I verified it with MSExcel2010). Shortly two solutions are presented: the first applies to all documents while the second applies to a single document.

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33175

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.

Upvotes: 7

Related Questions