Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
changing query source | Excel Worksheet Functions | |||
AHHH! Again | Excel Discussion (Misc queries) | |||
AHHHH-Get Data from Multiple Excel workbooks | Excel Discussion (Misc queries) | |||
How do I get MS Query to pull info from 3 different workbooks? | Excel Discussion (Misc queries) |