Is there an easier way to pull URLs out of Web Query?

[Bob Smith]

Hi folks,

I've been playing with a workbook, where I'm doing web queries, with
options set to HTML. 100 cells of data comes down the person's name, and
included is a web link included with each name. I'd like to extract those
hyperlinks from column A to column B.

What I've been doing is copying column a to column b, then using a small
macro to remove the name which will then show the hyperlink in column b.
Only think is that I have to do that for each cell, and it takes a small bit
of time to do 100 cells.

Is there a formula or setting, that will extract the urls easier? I ask as
there are number of other sets of data I'd like to download, with a 1000 or
more entries, and it's just undoable to do those right now.

Any ideas, formulas or changes to the below macro would be appreciated.

Sub RemoveHandle()
'
' RemoveHandle Macro
' Macro recorded 11/2/2006 by Robert Smith
'
' Keyboard Shortcut: Ctrl+z
'
Selection.Hyperlinks(1).TextToDisplay = ""
End Sub

Another question - Is there a way to set the automatic replication to
download HTML, so I don't have to go through the steps of manually "refresh
data" and then going to Options to select HTML?

Thanks for any info you can provide.

Bob

 

[Guest]

If you have a hyperlink in A1 then this little UDF:

Function hyp(r As Range) As String
hyp = ""
If r.Hyperlinks.Count > 0 Then
hyp = r.Hyperlinks(1).Address
End If
End Function

will return the URL

 

[Bob Smith]

Hi Gary,

Thanks for the reply and I apologize for the late reply.

I hate to sound like a newbie, but where do I apply the function listed
below? In a new macro? By the by, the hyperlinks start in cell b3, b4 etc,
to b102.

Regards,

Bob

 

[Guest]

First paste the UDF in a module (can be the same as your sub).

Then, in the worksheet, in an un-used cell enter:

=hyp(B3) and copy down the column.

The function should return the URL address of the hyperlink in question