Importing Web Page with Hyperlinks into Excel

[feltra]

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.

 

[Randy Harmelink]

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.

 

[Dallman Ross]

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.

 

[Dallman Ross]

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.

 

[feltra]

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.

 

[Dallman Ross]

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 . . .

 

[feltra]

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

 

[Dallman Ross]

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

=========================

 

[feltra]

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

 

[feltra]

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

 

[Dallman Ross]

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=