Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
Hi all,
I need to read a page from the web and import it in Excel, programatically (ie. thru a macro). This page contains some text that have hyperlinks... A typical example of the page is: --------------------------------------------------------------------------- Sample Page with Links Text line 1.... Text line 2... ...... ..... Link to Data Set 1 Link to Data Set 2 ...... ---------------------------------------------------------------------------- The lines "Link to Data Set 1" and "Link to Data Set 2" are links to another page(s). I need to get THESE LINKS into the Excel sheet, along with the text. Is this possible at all? I am open to experiementing with WebQuery or any other method, including editing the .iqv file manually, if that will work. Alternative 1: If it is simply not possible from within Excel, the first workaround is to try to get the source page of the web page. Is it possible to get the Source of the web page using WebQuery or any other methods in Excel VBA? Alternative 2: Any other roundabout method of using other tools such as PERL etc, and finally getting them into Excel? Please note: The option of copying manually by doing a select all and pasting into Excel or from IE 6 using Right-mouse-button option of Exporting to Excel is not a solution in this case, because, I need to access a whole lot of such pages (as given in the sample above) - about thousands of them... (over a period of time). So it has to be done programatically. Advance thanks for any & all feedback. Thanks & Best Regards, -feltra PS: I am just a beginner in Excel VBA and learn mostly by using Recorded Macros to understand whats happening, so please bear with me if i take time to understand... thanks. |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
I've done that type of thing with an add-in I wrote. It's free and
open source and can be found at this Yahoo Group: http://finance.groups.yahoo.com/group/smf_addin/ The primary purpose of the add-in -- to grab financial information off the web -- would be meaningless to you. However, I often use the RCHGetWebData() function to do "cascading finds" of data on a web page. For example, at its simplest, this function would return the first 32727 bytes of a web page: =RCHGetWebData(....url...., 1) I've use this function in a cascading function to extract all kinds of data off of web pages, even things like my NetFlix movie queue. It's pretty much just a matter of finding the right pattern to find each next item you want to extract, then using MID() function to extract the details of each pattern. Look for this file in the "Templates and Examples" folder of the files area: SMF-Template-RCHGetWebData-Cascading-Extraction.xls It is an example of using the cascading extraction technique to interpret something as simple as a text file, but the general technique is the same. On Aug 4, 7:40 pm, feltra wrote: I need to read a page from the web and import it in Excel, programatically (ie. thru a macro). This page contains some text that have hyperlinks... A typical example of the page is: --------------------------------------------------------------------------- Sample Page with Links Text line 1.... Text line 2... ..... .... Link to Data Set 1 Link to Data Set 2 ..... ---------------------------------------------------------------------------- The lines "Link to Data Set 1" and "Link to Data Set 2" are links to another page(s). I need to get THESE LINKS into the Excel sheet, along with the text. Is this possible at all? I am open to experiementing with WebQuery or any other method, including editing the .iqv file manually, if that will work. Alternative 1: If it is simply not possible from within Excel, the first workaround is to try to get the source page of the web page. Is it possible to get the Source of the web page using WebQuery or any other methods in Excel VBA? Alternative 2: Any other roundabout method of using other tools such as PERL etc, and finally getting them into Excel? Please note: The option of copying manually by doing a select all and pasting into Excel or from IE 6 using Right-mouse-button option of Exporting to Excel is not a solution in this case, because, I need to access a whole lot of such pages (as given in the sample above) - about thousands of them... (over a period of time). So it has to be done programatically. Advance thanks for any & all feedback. Thanks & Best Regards, -feltra PS: I am just a beginner in Excel VBA and learn mostly by using Recorded Macros to understand whats happening, so please bear with me if i take time to understand... thanks. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
On Sun, 05 Aug 2007 06:26:17 -0000 in microsoft.public.excel.misc,
Randy Harmelink spake thusly: I've done that type of thing with an add-in I wrote. It's free and open source and can be found at this Yahoo Group: http://finance.groups.yahoo.com/group/smf_addin/ Thanks, Randy. I'll check your stuff out. -- dman |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
On Sun, 05 Aug 2007 02:40:20 -0000 in microsoft.public.excel.misc,
feltra spake thusly: I need to read a page from the web and import it in Excel, programatically (ie. thru a macro). This page contains some text that have hyperlinks... I am open to experimenting with WebQuery or any other method, WebQuery will work. I am using it now to import about 10 pages of data from my broker, including links that do work from within Excel. This is with Excel 2002. You should note that you'll want to jiggle with the Options settings in the web query. When you're on the page when setting up the query, up in the menu on the right is an Options area. Click that and select, e.g., "Full HTML formatting". You might also want to jiggle with the "Data / Import External Data / Data Range Properties" settings. Note that you need to have a cell or range from your imported query highlighted in order for these data-edit features to be enabled. You can auto-refresh the query -- that's what I do -- or you can put it into a macro. I do that in another worksheet, as well. There is a setting to get the macro to not pause and wait for your confirmation click on the import. I found that after some trial-and-error. But right this second I don't have that book open and don't recall just what that trick was. Just knowing it's possible might be enough help to get you going, though. P.S. Sorry, I ran spell-check and accidentally corrected your "experimenting" while I was at it, and now I can't see how you originally had that word spelled before I go and post this. So that word is not an accurate quote of your original text. :-) -- dman |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
Hi Randy & Dallman,
Thanks a million for responding! I will try out and let you know how it goes... Yes, I also asked only for financial info. And Randy, it's great to see a yahoo group dedicated for Financial data macros... Will reply back in a couple of days... Thanks a lot & Best Regards, -feltra PS: Dallman, yes, I did not do a spell check before posting... Sorry, bad form.. Will correct that in future posts. |
#6
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
On Sun, 05 Aug 2007 18:00:13 -0000 in microsoft.public.excel.misc,
feltra spake thusly: Hi Randy & Dallman, Thanks a million for responding! I will try out and let you know how it goes... Yes, I also asked only for financial info. And Randy, it's great to see a yahoo group dedicated for Financial data macros... See, also, the xltraders group over there. PS: Dallman, yes, I did not do a spell check before posting... Sorry, bad form.. Will correct that in future posts. No sweat. I was simply explaining why someone might find a one-letter difference between what you posted and what I quoted you as having posted. Those hash-watchers, you know . . . :-) -- dman |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
Dear Dallman,
I don't know how to thank you!! I just had to try setting the "Full HTML Formatting" and it works like a charm! Also verified that I actually got the html links by checking outthis command in Immediate window: ?ActiveCell.Hyperlinks(1).Address and sure enough it displayed the URL from the downloaded table in the sheet. Ok, the reason I wanted this was to get (dump) the Quarterly, HalfYearly and Annual Results from BSE (indian) exchange for each listed company... Without this particular thing working, it was looking like a case of "so near yet so far"... You can't imagine what a relief it is to know that it is possible to get the links within the sheet! Thanks a lot & Best Regards, -feltra On Aug 5, 6:41 pm, Dallman Ross <dman@localhost. wrote: On Sun, 05 Aug 2007 02:40:20 -0000 in microsoft.public.excel.misc, feltra spake thusly: I need to read a page from the web and import it in Excel, programatically (ie. thru a macro). This page contains some text that have hyperlinks... I am open to experimenting with WebQuery or any other method, WebQuery will work. I am using it now to import about 10 pages of data from my broker, including links that do work from within Excel. This is with Excel 2002. You should note that you'll want to jiggle with the Options settings in the web query. When you're on the page when setting up the query, up in the menu on the right is an Options area. Click that and select, e.g., "Full HTML formatting". You might also want to jiggle with the "Data / Import External Data / Data Range Properties" settings. Note that you need to have a cell or range from your imported query highlighted in order for these data-edit features to be enabled. You can auto-refresh the query -- that's what I do -- or you can put it into a macro. I do that in another worksheet, as well. There is a setting to get the macro to not pause and wait for your confirmation click on the import. I found that after some trial-and-error. But right this second I don't have that book open and don't recall just what that trick was. Just knowing it's possible might be enough help to get you going, though. P.S. Sorry, I ran spell-check and accidentally corrected your "experimenting" while I was at it, and now I can't see how you originally had that word spelled before I go and post this. So that word is not an accurate quote of your original text. :-) -- dman |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
On Sun, 05 Aug 2007 18:34:56 -0000 in
microsoft.public.excel.programming, feltra spake thusly: Dear Dallman, I don't know how to thank you!! I just had to try setting the "Full HTML Formatting" and it works like a charm! Also verified that I actually got the html links by checking outthis command in Immediate window: ?ActiveCell.Hyperlinks(1).Address and sure enough it displayed the URL from the downloaded table in the sheet. Super that this was able to help you, Feltra. Also interesting to hear how you tackled the Immediate-window testing. I might use that sometime myself! Dallman ========================= Ok, the reason I wanted this was to get (dump) the Quarterly, HalfYearly and Annual Results from BSE (indian) exchange for each listed company... Without this particular thing working, it was looking like a case of "so near yet so far"... You can't imagine what a relief it is to know that it is possible to get the links within the sheet! Thanks a lot & Best Regards, -feltra On Aug 5, 6:41 pm, Dallman Ross <dman@localhost. wrote: On Sun, 05 Aug 2007 02:40:20 -0000 in microsoft.public.excel.misc, feltra spake thusly: I need to read a page from the web and import it in Excel, programatically (ie. thru a macro). This page contains some text that have hyperlinks... I am open to experimenting with WebQuery or any other method, WebQuery will work. I am using it now to import about 10 pages of data from my broker, including links that do work from within Excel. This is with Excel 2002. You should note that you'll want to jiggle with the Options settings in the web query. When you're on the page when setting up the query, up in the menu on the right is an Options area. Click that and select, e.g., "Full HTML formatting". You might also want to jiggle with the "Data / Import External Data / Data Range Properties" settings. Note that you need to have a cell or range from your imported query highlighted in order for these data-edit features to be enabled. You can auto-refresh the query -- that's what I do -- or you can put it into a macro. I do that in another worksheet, as well. There is a setting to get the macro to not pause and wait for your confirmation click on the import. I found that after some trial-and-error. But right this second I don't have that book open and don't recall just what that trick was. Just knowing it's possible might be enough help to get you going, though. |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
Hi Dallman,
Super that this was able to help you, Feltra. Also interesting to hear how you tackled the Immediate-window testing. I might use that sometime myself! Well, I hope I didn't sound too fancy... I am sure you must be already knowing what I am describing below.... What I did was this: 1. First, Record a macro for the WebQuery thing that I want to do 2. Position / resize the Sheet window and the VBA Editor window so that I can see both at the same time (visually). 3. Run the macro... The data (from the web) will appear on the sheet (in the lower half of the screen) where I can see it. 4. Manually position the activecell to the cell I want to check out [I do this by clicking on an empty adjacent cell and then use arrow keys to position to the cell i want.. Clicking directly may cause un-needed effects like say going to a web page, if that cell has a hyperlink]. 4. In the VBA Editor (that is on the upper half of the screen) invoke the immeidate window. 5. Simply type in: ?ActiveCell.Hyperlinks(1).Address Thanks & Best Regards, -feltra |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
Hi Randy,
It's a fantastic site that you have! I promptly joined and also let a few of my friends know about the site.... I can already see hours and hours of browsing on your site... and hope I can contribute something useful in future.. Thanks & Best Regards, -feltra http://finance.groups.yahoo.com/group/smf_addin/ |
#11
Posted to microsoft.public.excel.programming,microsoft.public.excel.misc
|
|||
|
|||
Importing Web Page with Hyperlinks into Excel
On Mon, 06 Aug 2007 23:24:35 -0000 in microsoft.public.excel.misc,
feltra spake thusly: Hi Dallman, Well, I hope I didn't sound too fancy... I am sure you must be already knowing what I am describing below.... What I did was this: Well, I didn't know it all. I know a lot of some stuff and not much at all of other. Trying to heap more knowledge coals onto the lexical fire, though. :-) 4. In the VBA Editor (that is on the upper half of the screen) invoke the immeidate window. 5. Simply type in: ?ActiveCell.Hyperlinks(1).Address Neat! Thanks, Feltra. =dman= |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing Data Into Excel From LONG Web Page With URL Ending With .cfm | Excel Worksheet Functions | |||
opening a new page with hyperlinks in excel | Excel Discussion (Misc queries) | |||
Hyperlinks in Excel to new page | Setting up and Configuration of Excel | |||
Excel Web Page with Hyperlinks | Excel Discussion (Misc queries) | |||
Creating hyperlinks in Excel HTML converted page. | Setting up and Configuration of Excel |