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

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote