Pull data from web page
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Pull data from web page
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.)
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.)
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pull data from web page
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Pull data from web page
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.
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.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pull data from web page
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.
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Pull data from web page
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!
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!
Last edited by agibsonsw on 13 Apr 2010, 17:56, edited 1 time in total.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- SilverLounger
- Posts: 2403
- Joined: 05 Feb 2010, 22:21
- Location: London ENGLAND
Re: Pull data from web page
Ta, you were just ahead of me (as usual). Andy.
"I'm here to save your life. But if I'm going to do that, I'll need total uninanonynymity." Me Myself & Irene.
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Pull data from web page
off topic.. which actually calculates kilometres A useful tool though. ThanksHansV wrote:........ and/or Calculating Mileages.
Maybe the page layout has since changed?
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pull data from web page
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.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Pull data from web page
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.
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.
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.
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.
You do not have the required permissions to view the files attached to this post.
Nathan
There's no place like home.....
There's no place like home.....
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pull data from web page
Well, in Excel 2007 you have a LOT of cells per sheet...
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 1185
- Joined: 24 Jan 2010, 12:02
- Location: Wales, UK.
Re: Pull data from web page
...And it's coming to my (work) desk this week. My post count is about to sky-rocket!HansV wrote:Well, in Excel 2007 you have a LOT of cells per sheet...
Nathan
There's no place like home.....
There's no place like home.....
-
- Microsoft MVP
- Posts: 656
- Joined: 24 Jan 2010, 17:51
- Status: Microsoft MVP
- Location: Weert, The Netherlands
Re: Pull data from web page
OK, I went ahead and ordered the bricks.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78545
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pull data from web page
Best wishes,
Hans
Hans
-
- PlutoniumLounger
- Posts: 15641
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: Pull data from web page
Andy, you seem to have your solution.agibsonsw wrote:How can I pull a piece of data from a web page? Either some HTML or text?
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.
He who plants a seed, plants life.