Extract Link Address From (insert)hyperlink Or Hyperlink Formula

less than 1 minute read

This method that will return the hyperlink text whether it has been created by a formula, or by the Insert/Hyperlink method.

If the former, we merely have to parse the formula; if the latter, we need to iterate through the hyperlinks collection on the worksheet.

The formula will return nothing if there is no hyperlink in cell_ref; change to suit.

' ## Get the formula url from hyperlink/formula or the insert/hyperlink method
Function LinkLocation(rng As Range) ' vars Dim sFormula As String, sAddress As String Dim L As Long Dim sHyperlink As Hyperlink, rngHyperlink As Hyperlinks ' cell formula sFormula = rng.Formula ' gets starting position of the file path, also acts as a test if ' there is a formula L = InStr(1, sFormula, "HYPERLINK(""", vbBinaryCompare) ' tests for hyperlink formula and returns the address, if a link ' then returns the link location. If L > 0 Then sAddress = Mid(sFormula, L + 11) sAddress = Left(sAddress, InStr(sAddress, """") - 1) Else Set rngHyperlink = rng.Worksheet.Hyperlinks For Each sHyperlink In rngHyperlink If sHyperlink.Range = rng Then sAddress = sHyperlink.Address End If Next sHyperlink End If ' boom, got the hyperlink address LinkLocation = sAddress End Function

Tags: files, text

Categories: vba

Updated: November 07, 2018

Twitter Facebook LinkedIn

Leave a Comment