Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts

    URL / Anchor / Hyperlink information in an Excel (2003) cell

    I've copied a lot of URLs from a web page into Excel, one link per cell, in a column. I know how to launch a web page from Excel once I have the URL, but I cannot work out how to access the URL that is in the cell. I use the old macro 4 language and would very much like to have a small VBA routine to pass back the URL in a nominated reference (eg [book]sheet!M37). I could thus launch which web pages I need.

    I'd very much appreciate any help available please.

    Geoffrey

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In VB you can use the Hyperlink.follow method. Without VB, you can use the HYPERLINK function in a formula to convert text to a hyperlink.

    Steve

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Steve,

    Thank you for your help so far. I've attached a sample spreadsheet of what I hope to achieve. In essence I need to extract by program the URLs in a large number of cells. The URLs are already stored in the cells. Because there are so many URLs (one to a cell as a hyperlink) I need to be able to open those I select and thus need a loop in the program. The only part I cannot do is the extract of the URL address in the hyperlink. I believe this will need a brief VBA function.

    The attached spreadsheet is only shown to give a context of the problem I wish to solve. It has two sheets: one is the macro sheet, and the other is the data sheet

    regards

    Geoffrey
    Attached Files Attached Files

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Not sure what you are after.

    The line
    sLink = activecell.Hyperlinks(1).Address

    Will store the hyperlink address in a variable

    The line:
    activecell.Hyperlinks(1).Follow

    will open the hyperlink contained in the cell.

    You can do standard looping through each cell in the selection in VB or you could just look through all the hyperlinks on the sheet and not worry about the selection.

    If this does not answer you question could you be very specific about what you need. Note if you need something for the old macro language, I will not be able to help as I never learned it. I started with XL97 and VBA. I can help with VBA solutions.

    Steve
    Last edited by sdckapr; 2012-01-31 at 21:18.

  5. #5
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Steve,

    I should have explained that my VBA skills are very limited, perhaps the reason why I appear not to be as precise as you'd like. I've attempted to write a VBA function and I'm afraid it doesn't work. I've copied it below. I need a function (XCellHyperlinkGet) that receives an input argument of the cell address I want as text. XCellHyperlinkGet would then return the hyperlink as text. XCellHyperlinkGet could be called from a normal spreadsheet or from a macro sheet. If XCellHyperlinkGet fails, FALSE as a Boolean would be returned.

    I couldn't find Hyperlinks in VBA Help.

    I hope this gives you a better view.

    Regards

    Geoffrey



    Function XCellHyperlinkGet(myCell As String)


    ' Returns Hyperlink contained in Cell
    ' I want myCell to be the cell with the URL
    ' to be returned
    ' use of ActiveCell below is wrong I think
    ' If the macros fails I would like FALSE returned
    ' FALSE as in Excel

    On Error GoTo MyFail
    sLink = ActiveCell.Hyperlinks(1).Address
    XCellHyperlinkGet = sLink
    Exit Function
    MyFail:
    XCellHyperlinkGet = "False"

    End Function

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You were very close. The cell needs to be a range, not a string. And instead of "Activecell" you need to use the range reference you are feeding to the function. There is also no need to put it into string only to feed it to the function later, you can do it all at once

    Steve

    Code:
    Function XCellHyperlinkGet(myCell As Range)
    On Error GoTo MyFail
    XCellHyperlinkGet = myCell.Hyperlinks(1).Address
    Exit Function
    MyFail:
    XCellHyperlinkGet = "False"
    
    End Function

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    A futher comment, If the function fails, it returns the text string "false". If you want the boolean false, change the line to:

    XCellHyperlinkGet = False

    [No quotations]

    Steve

  8. #8
    Star Lounger
    Join Date
    Feb 2005
    Posts
    50
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Steve,

    Many thanks for sticking with the problem. The code looks so elegant, it works, it's really useful for me, and I've learnt a lot.

    Geoffrey

Tags for this Thread

Posting Permissions

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