Spreadsheet 123 Excel 2003, XP, 2000, 97 FAQs: Extract hyperlink to Files

Question:  I have an Excel spreadsheet that contains hyperlink addresses to files. I tried extracting the hyperlink address for these files, however I'm not getting the complete Address. The complete Address should be:

C:\My Documents\Past Projects\Centennial\Program Status Report.xls

But I only get:

\..\..\Past Projects\Centennial\Program Status Report.xls

Is there a way to always get the complete hyperlink address?

Answer:  Below are two functions that you can include in your spreadsheet to extract the complete hyperlink address for either a file or a web address.

Function HyperLinkText(pRange As Range) As String

Dim ST1      As String
Dim ST2      As String
Dim LPath    As String
Dim ST1Local As String

If pRange.Hyperlinks.Count = 0 Then
  Exit Function
End If

LPath = ThisWorkbook.FullName

ST1 = pRange.Hyperlinks(1).Address
ST2 = pRange.Hyperlinks(1).SubAddress

If Mid(ST1, 1, 15) = "..\..\..\..\..\" Then
  ST1Local = ReturnPath(LPath, 5) & Mid(ST1, 15)
ElseIf Mid(ST1, 1, 12) = "..\..\..\..\" Then
  ST1Local = ReturnPath(LPath, 4) & Mid(ST1, 12)
ElseIf Mid(ST1, 1, 9) = "..\..\..\" Then
  ST1Local = ReturnPath(LPath, 3) & Mid(ST1, 9)
ElseIf Mid(ST1, 1, 6) = "..\..\" Then
  ST1Local = ReturnPath(LPath, 2) & Mid(ST1, 6)
ElseIf Mid(ST1, 1, 3) = "..\" Then
  ST1Local = ReturnPath(LPath, 1) & Mid(ST1, 3)
Else
  ST1Local = ST1
End If
 
If ST2 <> "" Then
  ST1Local = "[" & ST1Local & "]" & ST2
End If
 
HyperLinkText = ST1Local

End Function