Full hyperlink path in Excel

Question

I have a column in an Excel file, and in it I only have values ​​such as "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 the file path extracted from the hyperlink in another column, and I did this 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 corresponding cell, from where I want to extract the address

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

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

I do not see the full path to the documents, can anyone help with this vba function?

PS: I already looked through the messages and did not find anything like it

thanks

Answer 1

Excel will change the address to a relative address when possible. To prevent this, you need to set the Hyperlink Base property. In Excel 2010, click File - Information, Show Advanced Properties, if necessary, and enter the C: hyperlink database.

If there is no base of hyperlinks, it is considered the way of the book. By setting it to C:, you force Excel to show the whole path, because it cannot create a path relative to C :. If you create your hyperlink database \ server \ folder1 \, you will get a relative path, because Excel can make a path regarding this.

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

Answer 2

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

Links are provided on the MSWord support page, however it also works for MSExcel (I tested it with MSExcel2010). Soon two solutions are presented: the first applies to all documents, and the second refers to one document.