how to copy 2350 hyperlink full paths to any column in a worksheet ?
Hi all,
First of all, thank for the help I received before.
I have another question :
I have an excel worksheet, with about 2350 entries. All of them have an
hyperlink in column A. I need to find a way to copy ONLY THE HYPERLINKS FULL
PATHS of all 2350 cells to column D, NOT THE CELLS CONTENT !!
The reason is that from this column D, I can generate playlists without problem.
In my search for a solution, I came across following UDF which did not work
and gave an error. First of all, I'm not sure if this UDF will solve my problem
an secondly, if it does, how can I repare it ?
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
Thanks for any reply !
Kontiki
|