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