Extract hyperlink target from a cell in different workbook

Question

I have a workbook with a custom right click function that extracts cell values from another workbook depending on what the user chooses. It works very well, I just take in the cell's value from the other workbook. Some cells contain hyperlinks though, and I'd like to import the functional hyperlink, not the value of what's shown in the cell. For example, the following image contains a hyperlink in cell (Y216) of sheet BOS of the input workbook.:

This is an image of the cell I want to copy. It is indeed a hyperlink.

?application.Workbooks(2).Sheets("BOS").Range("Y216").value

returns MKB 70-203 Wicket Shear Pin Detection System, which is indeed correct.

But how do I take the hyperlink's destination? I tried several things including

?application.Workbooks(2).Sheets("BOS").Range("Y216").Hyperlinks.count

returns 0 even though you can see in the image that the hyperlink does have an address. In the same fashion the following sub doesn't enter the For Each because it counts 0 hyperlinks.

Sub HLtester()
    Dim HL As Hyperlink
    For Each HL In Application.Workbooks(2).Sheets("BOS").Range("Y216").Hyperlinks
        Debug.Print HL.Address
    Next
End Sub

Expected output would be the link's target J:\SOUM\3191.... as shown in image.

EDIT

If it's important the cell's formula is

=LIEN_HYPERTEXTE("J:\SOUM\3191 M - Old Hickory Dam\11_BOS_FT\02_FT_MECT\21-200 Headcover";"21-200 Headcover")

That's the =HYPERLINK function of French Excel, by the way. I guess in last resort I can take the formula and cut off the function parts to retrieve the link part?

Have you checked out Workbooks(2).Sheets("BOS").Hyperlinks to see if there are hyperlinks present on the sheet, if not in that specific range? And if they're present, can you look through them and verify one is located at Y216? — Mikegrann
@Mikegrann It returns no hyperlinks at all for the entire worksheet. Maybe there is something wrong with my hyperlinks? I tried them and they work. They were created with the =Hyperlink formula (not VBA). They all point to exterior references. debug.print .Formula gives =HYPERLINK("J:\SOUM\3191 M - Old Hickory Dam\11_BOS_FT\02_FT_MECT\HM3191 00 MEF 32-202 F DA-01.pdf","HM3191 00 MEF 32-202 F DA-01.pdf") So I guess I can just trim the formula around my target in worst case scenario... — David G
well, I think that explains it. I'll dig around for it, but I'm fairly sure I've run into this before: hyperlinks created through the worksheet function are treated differently than inserted hyperlinks, and only the latter shows up in the Hyperlinks collection. Looks like you're going to have to manually extract the argument from Range.Formula — Mikegrann

Sgdva Sgdva · Accepted Answer · 2016-08-02T15:21:46

Your command works for me, I don't know why you set the range if you want to loop through all the hyperlinks in the sheet -neither why you set as application. workbooks-, anyways, this worked fine for me:

Sub HLtester()
    Dim HL As Hyperlink
    For Each HL In Sheets("Sheet1").UsedRange.Hyperlinks
        Debug.Print HL.Address
    Next
End Sub

You may get it as well within range methods with the following

ActiveCell.Hyperlinks(1).Address

You may get more info here

Edit:
Probably the count is wrong because of the "application.workbook", try to declare it as a variable instead of using it all over the code

Sub HLtester()
    Dim HL As Hyperlink
    Dim WBAnalyzed As Workbook: Set WBAnalyzed = Workbooks("MyWB.xlsm")
    For Each HL In WBAnalyzed.Sheets("Sheet1").UsedRange.Hyperlinks
        Debug.Print HL.Address
    Next
End Sub

Edit 2:
This is the approach suggested when the hyperlink it's given by its formula

Sub test()
    On Error Resume Next 'means no formula
    x = Evaluate(Range("A1").Formula)
    x1 = Sheets("Sheet1").UsedRange.Hyperlinks.Count
    Debug.Print x
    Debug.Print x1
End Sub

PS: I saved my variable declaration -just cause-, but, you should always have a neat control for them and use option explicit at the beginning of the module.

Extract hyperlink target from a cell in different workbook

1 Answers