none
I need help using VBA to copy a hyperlink from one Excel workbook to another

    Question

  • Hi all,
    I've been working on this macro for days and I'm hoping someone can help me.  I'm using Excel 2007.

    I have an existing workbook - let's call it "WB1".  WB1 has hyperlinks in column "C" that have the "Text to Display" shown in the cell with the actual URL not shown.  So "C" would be something like "Click this link" and when they click it it would go to the URL specified when the hyperlink was created.  Note that column C on WB1 has been merged with other columns. 


    The workbook I'm creating is "WB2".  If I do a copy/paste in my macro from cell C in WB1 to WB2 the result is also a merged cell on WB2.  This would be OK, except that I then want to do some formatting, which can't be done on merged cells.

    So - what I want to do is use "hyperlinks.add" using the Anchor, Address, etc to get the hyperlink (including "text to display") from WB1 to WB2.  This will allow me to get the cell from WB1 to WB2 without ending up with a merged cell in WB2.   I could easily do this if I wanted to copy a URL from one workbook to another, but in this instance I want the URL and the Text to Display on WB2.

    The problem I'm having is I don't know how to get the values for "text to display" and the "actual URL" out of the cell in WB1.  

    Can anyone shed some light on this.  I'd really appreciate it.

    BTW - I don't have any control over WB1 - it comes to me that way.


    KW
    Friday, January 27, 2012 2:13 AM

Answers

  • Let's say the merged cells are A1:A2. Then you can retrieve the address by using this code.

    Sheets("Sheet1").Range("A1").Hyperlinks(1).Address
    

    Example

    Sub Sample()
        '~~> This will give you the URL
        MsgBox Sheets("Sheet1").Range("A1").Hyperlinks(1).Address
        '~~> This will give you the Text To Display
        MsgBox Sheets("Sheet1").Range("A1").Value
    End Sub


    EDIT: If you want to use the above values to create a hyperlink in a cell then see a similar thread where I have posted a solution.

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/f8da4a29-1244-4630-b30c-c5c472bd4a47



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Friday, January 27, 2012 2:19 AM
    Moderator

All replies

  • Let's say the merged cells are A1:A2. Then you can retrieve the address by using this code.

    Sheets("Sheet1").Range("A1").Hyperlinks(1).Address
    

    Example

    Sub Sample()
        '~~> This will give you the URL
        MsgBox Sheets("Sheet1").Range("A1").Hyperlinks(1).Address
        '~~> This will give you the Text To Display
        MsgBox Sheets("Sheet1").Range("A1").Value
    End Sub


    EDIT: If you want to use the above values to create a hyperlink in a cell then see a similar thread where I have posted a solution.

    http://social.msdn.microsoft.com/Forums/en-US/exceldev/thread/f8da4a29-1244-4630-b30c-c5c472bd4a47



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.

    Friday, January 27, 2012 2:19 AM
    Moderator
  • Thanks for your quick reply.  Unfortunately every time I try to get to the similar post you provided a link to my browser freezes.  Would it be possible for you to post your solution here or on a new thread?

     


    KW
    Friday, January 27, 2012 8:09 PM
  • Hi RecoveringProgrammer,

    The link that Sid provided to you is a question he helped me with. The link works when i try and open it.

    You can just search for the thread its called:  Automatic Hyperlink creation

    GL :)

    Sunday, January 29, 2012 11:58 PM