Hyperlinks (1) .address returns only a partial address

Question

I am working on the following code, which will reformat the cell containing the link, based on whether 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 

Today the code has proven itself! But now it returns everything as "404" and marks the cells pink, even if the links work. Debugging shows 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 via sharepoint.

The code does not work due to truncation.

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

Answer 1

Excel seems to change your hyperlinks to addresses from absolute to relative.

You can try to create an absolute address by changing the so-called "hyperlink database". To do this, go to: File> Information> Show all properties> Hyperlink Base (to the right of the screen) and change it to "x".

Link to Microsoft support site:

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

(the article presents a solution for MS Word, but it should work in MS Excel as well)

Hope this helps.

Answer 2

A range object in Excel has a second Subaddress property. Use the secondary address function to get the rest of the address as shown below

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

In my case, I had to add extra characters between the address and the subaddress. Be sure to check how you can form a complete URL using both the address and the subaddress.