Results 1 to 6 of 6

Copying Hyperlinks in Excel VBA

This is a discussion on Copying Hyperlinks in Excel VBA within the Excel Questions forums, part of the Question Forums category; Hi folks, I have an excel sheet with thousands of hyperlinks. In a seperate sheet I have a list of ...

  1. #1
    Board Regular Terry_Orange's Avatar
    Join Date
    Jul 2002
    Posts
    133

    Default Copying Hyperlinks in Excel VBA

    Hi folks,

    I have an excel sheet with thousands of hyperlinks.

    In a seperate sheet I have a list of some of these websites named, which I VLOOKUP and find. I then want to copy across the actual hyperlink (and print out the text for a user to see).

    YAHOO (www.yahoo.com)

    I can find it fine, but I can't access the hyperlink information to copy it across. I can add a hyperlink - but I don't know how to assess the original...

    activecell.hyperlink. ????

    Any ideas anyone !?!?!?!?!

    THANK YOU AS ALWAYS.


    TO.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,505

    Default Re: Copying Hyperlinks in Excel VBA

    Something like this?

    =HYPERLINK(VLOOKUP(A1,Sheet1!$A$1:$B$2,2,FALSE))

  3. #3
    Board Regular Terry_Orange's Avatar
    Join Date
    Jul 2002
    Posts
    133

    Default Re: Copying Hyperlinks in Excel VBA

    Andrew.

    Thanks for that - it copies across the link (which I've managed - though a long piece of macro code!), but....

    is there any (easy) way to get the actual weblink address....

    eg. the www.yahoo.com bit as a non clickable..... ?

    as usual we are here to challenge you!

    cheers,
    TO.

  4. #4
    Board Regular Terry_Orange's Avatar
    Join Date
    Jul 2002
    Posts
    133

    Default Re: Copying Hyperlinks in Excel VBA

    and actually the link doesn't work when clicked anyway....



    hmmm......

    It looks right! but just comes up with a msg CAN'T FIND SPEcIFICIED FILE !

    TO.

  5. #5
    Board Regular Terry_Orange's Avatar
    Join Date
    Jul 2002
    Posts
    133

    Default Re: Copying Hyperlinks in Excel VBA **RESOLVED**

    in VBA I can use:

    Dim HyperLink1 as String
    HyperLink1 = ActiveCell.Hyperlinks(1).Name

    and then simply put this into my cell.

    Thanks for your help Andrew. As ever very gratefully appreciated.

    TO.

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,505

    Default Re: Copying Hyperlinks in Excel VBA

    I had on Sheet1:

    ******** ******************** ************************************************************************>
    Microsoft Excel - BOOK7___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Ahttp://216.92.17.166/**
    2
    Bhttp://216.92.17.166/board2/**
    Sheet1
    Hyper Lynks Address is here ...

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    and on Sheet2:

    ******** ******************** ************************************************************************>
    Microsoft Excel - BOOK7___Running: xl2000 : OS = Windows Windows 2000
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    A
    B
    C
    D
    1
    Ahttp://216.92.17.166/**
    Sheet2*

    [HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


    Click the Hyperlink that results from the formula:

    =HYPERLINK(VLOOKUP(A1,Sheet1!$A$1:$B$2,2,FALSE))

    worked for me.

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
  •  


DMCA.com