hyperlinks(1).address only returning a partial address

Question

I'm working on the following code, which will re-format a cell containing a link based on whether or not the link works when it is clicked:

Private Sub worksheet_followhyperlink(ByVal HL As HYPERLINK)
Dim linkReq As Object
Dim linkStatus As Integer

Application.ScreenUpdating = False
On Error GoTo linkError
Set linkReq = New MSXML2.XMLHTTP60
With linkReq
    .Open "GET", HL.address, False
    .Send
End With
linkStatus = linkReq.Status

If linkStatus = 404 Then HL.Parent.Interior.Color = rgbPink
If linkStatus <> 404 Then HL.Parent.Interior.Pattern = xlNone

If HL.Parent.Interior.Pattern = xlNone Then GoTo exitSub
Application.ScreenUpdating = True
MsgBox("Link is broken")

exitSub:
Application.ScreenUpdating = True
Exit Sub

linkError:
linkStatus = 404
Resume Next
End Sub

The code worked great yesterday! But now, it's returning everything as '404' and marking the cells pink, even if the links work. Debugging reveals that the value of HL.address is "folder/Document.pdf" instead of "https://website/folder/Document.pdf". This excel document is hosted on "https://website" through sharepoint.

The code not working because of the truncation.

Is there a way to extract a full url from an excel hyperlink, without truncation, regardless of the size of the url?

Answer 1

It seems that Excel is changing your hyperlinks adresses from absolute to relative.

You can try to force absolute adress by changing so called "Hyperlink Base". In order to do it go to: File > Info > Show All Properties > Hyperlink Base (on the right of screen) and change it to "x".

Reference from Microsoft Support site:

https://support.microsoft.com/en-us/help/903163/how-to-create-absolute-hyperlinks-and-relative-hyperlinks-in-word-docu

(Article presents solution for MS Word but it should work in MS Excel as well)

Hope that helps.

Answer 2

The range object in excel has a second property "Subaddress" . Use the Subaddress functionality to get the rest of the address like below

range.Hyperlinks(1).Address + range.Hyperlinks(1).SubAddress

In my case I had to add additional characters in between Address and Subaddress. Please make sure to check how the full URL can be formed using both Address and Subaddress