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

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

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

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
--
Gary's Student


"Bob Smith" wrote:

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

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

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

"Gary''s Student" wrote in message
...
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
--
Gary's Student


"Bob Smith" wrote:

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

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

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
--
Gary's Student


"Bob Smith" wrote:

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

"Gary''s Student" wrote in message
...
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
--
Gary's Student


"Bob Smith" wrote:

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