Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: HYPERLINKS converting

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Romania
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 !!!

  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!


    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    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

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •