HYPERLINKS converting

[RomulusMilea]

Hello all,
My name is Romulus Milea and I work with Solectron Romania as a C.F.T. Product engineer. My Excel-related issue is that I have a list with almost 2000 hyperlinks created with CTRL + K command and I just want to convert each of them into a hyperlink created with HYPERLINK function.

Please help us to obtain a VBA code which can automatically solve our issue.

Thank you very much in advance and we look forward to reading your answer.

Regards,
Romulus.

P.S. You are the best !!!

 

[Yogi Anand]

On 2002-05-12 21:51, RomulusMilea wrote:
Hello all,
My name is Romulus Milea and I work with Solectron Romania as a C.F.T. Product engineer. My Excel-related issue is that I have a list with almost 2000 hyperlinks created with CTRL + K command and I just want to convert each of them into a hyperlink created with HYPERLINK function.

Please help us to obtain a VBA code which can automatically solve our issue.

Thank you very much in advance and we look forward to reading your answer.

Regards,
Romulus.

P.S. You are the best !!!

I know you are looking for a VBA solution. However, here is an approach using a formula.
Let us say you currently have a hyperlink in cell B9 -- now to convert that into a Hyperlink Function, you can use the following formulas:

=HYPERLINK(B9)

and

="=hyperlink("""&B9&""")"
Then EDIT|COPY and EDIT|PASTE_SPECIAL to convert it to a value.

You may want to explore if this approach works for you.

Regards!

 

[Colo]

Hi RomulusMilea, I made a vba sample for you.
Please copy this into a standard module and run this macro.

<pre>
Sub test()
Dim Hyp As Hyperlink, c As Range, strAd As String, strSad As String
For Each Hyp In ActiveSheet.Hyperlinks
With Hyp
Set c = .Parent
strAd = .Address
strSad = .SubAddress
.Delete
c.Formula = _
"=HYPERLINK(" & Chr(&H22) & Chr(&H5B) & _
strAd & Chr(&H5D) & strSad & Chr(&H22) & ")"
End With
Next
End Sub
</pre>