Pull data from web page

[agibsonsw]

Hello. Excel/Access 2003.
How can I pull a piece of data from a web page? Either some HTML or text?
I don't want to link to an entire page.
Is it possible to do without parsing an entire page (DOM Traversal, I believe it's called). Thanks, Andy.
(I don't have much other information as yet - the question was asked of me in this manner.)

[HansV]

You can use the InternetExplorer object to access a web page; it helps if you already know which part of the page you want to inspect.

See Reference for Visual Basic Developers for an overview of the available objects, methods etc.

Perhaps the discussion in Screen Scraper on the Windows Secrets Lounge will help, and/or Calculating Mileages.

[agibsonsw]

Thank you for these references.
I can't see WebBrowser or Internet Explorer listed under Tools/ References?
I have M/S HTML which might do.
Would the process involve activating a browser window (although hidden) and navigating to the web page and then within it? Or can I, somehow, treat the web page as though it were an active document? Thanks, Andy.

[HansV]

If you use late binding, you don't need to set a reference:

Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

If you want to use early binding, you can set a reference to Microsoft Internet Controls.

The InternetExplorer object is an program interface to the Internet Explorer engine; it doesn't open a browser window, not even hidden.
The WebBrowser control in the same library is a control that you can place on a userform or form, and that acts as a browser window.

[agibsonsw]

Great.
I've managed to start IE, navigate to a page and retrieve the innerHTML. I could then search this string for the text I need.
There's probably a better way, but this will do for the moment.
Anyone know how I can exit IE when I've extracted what I need? myIE.Close and myIE.Exit don't work. Ta, Andy.

Third time lucky.. myIE.Quit does the job!

[HansV]

Use

myIE.Quit
Set myIE = Nothing

[agibsonsw]

Ta, you were just ahead of me (as usual). Andy.

[VegasNath]

........ and/or Calculating Mileages.

off topic.. which actually calculates kilometres A useful tool though. Thanks

Maybe the page layout has since changed?

[HansV]

The web page (http://www.postcode.org.uk/country/uk/_ ... ulator.asp" onclick="window.open(this.href);return false;) displays distances in both kilometres and miles.

[VegasNath]

But the "Mileage Calculator.xls" scrapes kilometres.

I am attaching a slightly modified version that scrapes both miles and kilometers, by both air and road, for anyone that should find this useful in the future.

Mileage calculator USES IE.xls

Exploring the concept of how the data is scraped has been a useful lesson for me. I'm sure I can find many uses for this.

[HansV]

Thanks!

And now to build a skyscraper...

[VegasNath]

Out of....... Excel cells?

[HansV]

Well, in Excel 2007 you have a LOT of cells per sheet...

[VegasNath]

Well, in Excel 2007 you have a LOT of cells per sheet...

...And it's coming to my (work) desk this week. My post count is about to sky-rocket!

[Jan Karel Pieterse]

OK, I went ahead and ordered the bricks.

[HansV]

[ChrisGreaves]

How can I pull a piece of data from a web page? Either some HTML or text?

Andy, you seem to have your solution.
I have built two methods to achieve web-data-mining.
The first uses Excel to harvest financial statements (Balance Sheet, Cash Flow, Income Statement) from Yahoo Finance and related sites, and also grabs each days press releases (text) from Canada News Wire. Details here and here.
The second is much, much faster and uses Internet Explorer to crawl web sites; it has the advantage of delivering the text and the html, but lacks the cell-like layout of the Excel method.