Find the row for a Hyperlink
 

I have a web query that imports a table with both text and
hyperlinks. What I need is the row number of each cell that has a
hyperlink and the URL for it.

I know how to read the cells that I want using: For Each rangeX In
Sheets("x").Range("A1:G7")
but I can not figure out how to determine the cells that have
hyperlinks.

I can also read the page using the following: For Each hyperX In
Sheets("x").Hyperlinks
but I can not figure out how to determine the worksheet row/col of the
hyperlink.

Any suggestions? (using WinXP and both XL2003 & XL2007)

Find the row for a Hyperlink
 

On Fri, 16 Dec 2011 15:04:03 -0800 (PST), cubbybear3 wrote:

I have a web query that imports a table with both text and
hyperlinks. What I need is the row number of each cell that has a
hyperlink and the URL for it.

I know how to read the cells that I want using: For Each rangeX In
Sheets("x").Range("A1:G7")
but I can not figure out how to determine the cells that have
hyperlinks.

I can also read the page using the following: For Each hyperX In
Sheets("x").Hyperlinks
but I can not figure out how to determine the worksheet row/col of the
hyperlink.

Any suggestions? (using WinXP and both XL2003 & XL2007)

In xl2007 (I don't know about xl2003), you can get the row from

hyperX.range.row

and the column from

hyperX.range.column.


To get the cell address:

======================
Option Explicit
Sub GetHyperlinkAddress()
Dim hls As Hyperlinks, hl As Hyperlink
Dim v()
Set hls = Worksheets("sheet1").Hyperlinks
For Each hl In hls
Debug.Print hl.Name, Cells(hl.Range.Row, hl.Range.Column).Address
Next hl
End Sub
============================

If you put a breakpoint at "next hl" and add a watch for hl, you can discern all types of interesting information from the watch window.

Find the row for a Hyperlink
 

On Fri, 16 Dec 2011 15:04:03 -0800 (PST), cubbybear3 wrote:

I have a web query that imports a table with both text and
hyperlinks. What I need is the row number of each cell that has a
hyperlink and the URL for it.

I know how to read the cells that I want using: For Each rangeX In
Sheets("x").Range("A1:G7")
but I can not figure out how to determine the cells that have
hyperlinks.

I can also read the page using the following: For Each hyperX In
Sheets("x").Hyperlinks
but I can not figure out how to determine the worksheet row/col of the
hyperlink.

Any suggestions? (using WinXP and both XL2003 & XL2007)

Of course, to get just the address that the hyperlink is attached to, you can more simply use:

hl.range.address

Also see HELP for the members of the hyperlink object.

Find the row for a Hyperlink
 

Thanks Ron,

Is there any way to identify the Hyperlink using the 1st method
(For Each rangeX In Sheets("x").Range("A1:G7")?

-pb

Find the row for a Hyperlink
 

On Fri, 16 Dec 2011 17:17:42 -0800 (PST), cubbybear3 wrote:

Thanks Ron,

Is there any way to identify the Hyperlink using the 1st method
(For Each rangeX In Sheets("x").Range("A1:G7")?

-pb


For Each rangeX in ...
If rangeX.Hyperlinks.Count 0 Then
Debug.Print rangeX.Address, rangeX.Hyperlinks(1).Address
End If
next rangeX

Find the row for a Hyperlink
 

Again, thank you Ron.
-pb

Find the row for a Hyperlink
 

On Mon, 19 Dec 2011 16:15:20 -0800 (PST), cubbybear3 wrote:

Again, thank you Ron.
-pb

Glad to help. Thanks for the feedback.