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.

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