Yann de Champlain
asked on
I can't paste hyperlinks created from a formula.
I can't paste hyperlinks created from a formula.
I need to paste hyperlink values into a new column.
I created the Hyperlinks using the hyperlink function. I actually need to have the hyperlink column pasted without the formula in it.
It does not seems to work.
If I do a Paste Special, Values, Operation: None.
The result of this is that I only get the "Friendly name" pasted. I loose the hyperlink.
The goal of this is to paste the hyperlink column into a Sharepoint List of type "Link".
I need to paste hyperlink values into a new column.
I created the Hyperlinks using the hyperlink function. I actually need to have the hyperlink column pasted without the formula in it.
It does not seems to work.
If I do a Paste Special, Values, Operation: None.
The result of this is that I only get the "Friendly name" pasted. I loose the hyperlink.
The goal of this is to paste the hyperlink column into a Sharepoint List of type "Link".
ASKER
Thanks a lot!
One part missing though, d'you know how to keep the "Friendly name" in the object?
Tank you.
One part missing though, d'you know how to keep the "Friendly name" in the object?
Tank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow it worked great thanks!
Public Sub CreateHyperlinkValues()
Dim Cell As Range
Dim URL As String
For Each Cell In Selection
URL = GetHyperlinkURL(Cell)
Cell.Clear
Cell.Parent.Hyperlinks.Add
Next Cell
End Sub
Public Function GetHyperlinkURL( _
ByVal Cell As Range _
) As String
' Extract the hyperlink URL, if any, from the specified cell.
Dim Value As String
Dim Pos As Long
' Look for a simple hyperlink URL in the cell
If Cell.Hyperlinks.Count = 1 Then
GetHyperlinkURL = Cell.Hyperlinks(1).Address
Else
' Look for a HYPERLINK function
If Left(Cell.Formula, 11) = "=HYPERLINK(" Then
Pos = InStr(Cell.Formula, ",")
If Pos = 0 Then
Pos = InStr(Cell.Formula, ")")
End If
Value = Mid(Cell.Formula, 12, Pos - 12)
' Using the Evaluate function resolves both a constant value ("www.site.com") and a cell reference (A1)
GetHyperlinkURL = Application.Evaluate(Value
End If
End If
End Function
Kevin