How to a get Address (Link) from cell where Hyperlink dynamically generated (by formulas)?

Question

In excel, you can link a hyperlink (to a cell, to a drawing etc)

We can say that these links are divided into two types:

1. Hard Link This feature is implemented either by directly editing a cell or an object, i.e. by right-clicking and linking the link (to a place in the document, to a web page, etc.), or in VBA by calling the Add function of the Hyperlinks object

So this link is visible in the cell properties, and! visible in the Hyperlinks collection

2. A dynamically generated hyperlink is a situation where a cell has a formula in the spirit of

=HYPERLINK(B2;A2)

A2 contains the display name, for example "Trade Minipigs" and B2 contains the actual address

For the user, everything works about the same as in the case of item 1, but it is no longer visible in the Hyperlinks collection

BUT! let's assume that the formula relies on cells on another sheet, they are still somewhere, with a cloud of checks and other things, in general complex, and we need to send someone this sheet with the final links (but not the sheets to which these generated links refer)

In this case, if you copy the sheet to a new file, the cell references in the formula will correct and point to the file from which the sheet was copied

It is clear that the recipient does not have such a file and these links will not work for him (however, in the part of the hyperlink itself, it does not work even in place, but the part associated with the display name works as expected)

The "Copy and paste" (values) operation will not help, because in this case the formula will be calculated in the part of the display name, but the resulting link will not be inserted (the same will happen when the link between the new file and the old one is broken)

That's what it is, that the" value " for the cell in which this formula is just a display name, but not a hyperlink, and it is also not in the cell properties The cell property hyperlink is a hard hyperlink

I think for sure where to the depths of the Excel object model this link is available After all, when you hover the cursor over such a cell, then yes, and the window pops up this hyperlink. However this is obvious.

Is it possible to somehow extract this generated link by software, so that later it can be bound to the desired location by the Add function of the Hyperlinks object?

Answer 1

I found a way to copy this, or rather get the address of such a dynamic link. It consists in the fact that the cell must be copied to Word (what a joy - with this operation, Word calculates the actual address of the link and turns it into a fixed one), then check the Hyperlinks collection of the Word object already

Of course, it works quite slowly in this form, but if you want, you can improve it, for example, make the wdApp object static and not create/destroy it every time, this will speed up the work very decently if you need to process a lot of cells.

Tested on Excel/Word 2019 (and don't forget to connect Microsoft Object Library

Function GetLink(r As Long, c As Long) As String
    
    Dim wdApp As Word.Application
    Dim wdDoc As Word.Document
    
    If Cells(r, c).Value = Empty Then
      GetLink = ""
      Exit Function
    End If
    
    Cells(r, c).Copy
    
    Set wdApp = CreateObject("Word.Application")
    wdApp.Documents.Add
    Set wdDoc = wdApp.Documents(1)
    
    wdApp.Visible = False
    wdDoc.Range.PasteExcelTable False, False, False
    
    If wdDoc.Hyperlinks.Count = 0 Then
        GetLink = ""
      Else
        GetLink = wdDoc.Hyperlinks(1).Name
     End If
    wdDoc.Close (wdDoNotSaveChanges)
    wdApp.Quit (wdDoNotSaveChanges)
    
End Function

Answer 2

To find all Hyperlinks that are added via formula, you can use the find-function in VBA. The following routine will loop over all Hyperlink-formulas and call a subroutine 'replaceHyperlink':

Sub replaceHyperlinks(Optional ws As Worksheet = Nothing)
    If ws Is Nothing Then Set ws = ActiveSheet
    
    Dim firstHit As Range, hit As Range
    Set hit = ws.Cells.Find(What:="=Hyperlink", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    Do While Not hit Is Nothing
        Call replaceHyperlink(hit)
        Set hit = ws.Cells.FindNext(after:=hit)
    Loop
End Sub

Now it gets tricky, we need to create a function that fetches the address(url) and the text from the Hyperlink-formula. Fetchting the text is easy, you can grab it with the Value2-property. For the address, I guess there is no other way than to analyze the formula text. The following routine does this for 3 simple cases:
- The url is in quotes ("https\\www.stackoverflow.com")
- url is a cell reference (to the same sheet), eg B2.
- url is a cell reference to another sheet (eg Sheet2!B2)

It will fail if the url is itself created by a formula (eg "https:\\" & B2).

Having URL and Text, the formula of the cell will be replaced with text and a real hyperlink is created:

Sub replaceHyperlink(cell As Range)
    Const FormulaStart = "=HYPERLINK("
    If UCase(Left(cell.formula, Len(FormulaStart))) <> FormulaStart Then Exit Sub
    
    Dim formula As String, url As String, p As Long, text As String
    ' Search for the link address
    formula = Mid(cell.formula, Len("=Hyperlink(") + 1)
    p = InStr(formula, ",")
    If p > 0 Then
        formula = Left(formula, p - 1)
    Else
        formula = Left(formula, Len(formula) - 1)
    End If
    
    If Left(formula, 1) = """" And Right(formula, 1) = """" Then
        url = Mid(formula, 2, Len(formula) - 2)
    ElseIf InStr(formula, "!") = 0 Then
        url = cell.Parent.Range(formula)
    Else
        url = Evaluate(formula)
    End If
    
    text = cell.Value2
    cell.Value = text
    cell.Hyperlinks.Add Anchor:=cell, Address:=url, textToDisplay:=text
End Sub

Update If the formula to get the url is more complicated, maybe you could write this formula part temporarily into the cell. After doing that, the Value2-property should resolve the formula to the url. Replace the last lines to

text = cell.Value2            ' Save the friendly text
cell.formula = "=" & formula  ' Write the URL-part temporarily into cell as formula
url = cell.Value2             ' Get the result of that temp. formula 

cell.Value = text
cell.Hyperlinks.Add Anchor:=cell, Address:=url, textToDisplay:=text