-1

I want to capture real-time data that updates into a table on a webpage; I prefer capturing it into excel using VBA, but I will write it in .NET C# or VB if I that is easier.

the data updates about 1 or 2 seconds, and I want to just grab the latest data quotes and log it into my spreadsheet; the table names are the same, only the data refreshes, and it does so automatically on the web page.

I've done a lot of Excel VBA and I know how to download a URL to a file--this is NOT what I want; I want to gain access to my webpage that is active and grab the data updates after I've logged into my site and selected a webpage that I like.

Is there a simple way to access this data on the webpage from Excel or .Net? Because it refreshes no more than once every 1 or 2 seconds, it is easy to just keep checking it for updates, and I can compare the latest data to see if it actually refreshed.

Community
  • 1
  • 1
Mark Main
  • 7
  • 1
  • 5
  • 2
    I removed the inapproprate tag data-mining. You are just [tag:web-scraping], this is not data-mining http://en.wikipedia.org/wiki/Data_mining . You must the first even considering to do it in Excel... good luck. – Has QUIT--Anony-Mousse Mar 25 '12 at 08:50
  • 1
    Search Google for "IE automation". Plenty of vb[a] examples of how to access a specific element on a page loaded in IE. – Tim Williams Mar 26 '12 at 02:36
  • 1
    See this link. http://stackoverflow.com/questions/8798260/html-parsing-of-cricinfo-scorecards/8846791#8846791 Hope it puts you in the right direction ;) Sid – Siddharth Rout Mar 26 '12 at 19:11

1 Answers1

1

In Excel 2003, use Data/Import External Data/New Web Query
Browse to your page and select the table you want to import.
After that you can either do a manual Refresh, or use a timer procedure to do something like:

Sheet1.Range("b3").QueryTable.Refresh BackgroundQuery:=False
iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • I've never selected a webpage table from VBA; it's going to be doing this every 1 second, so it needs to be automated--how do I select the table? – Mark Main Mar 25 '12 at 15:50
  • Also, is there a more direct "background" way to access the data rather than a physical "select" on the front of the page? – Mark Main Mar 25 '12 at 15:53
  • I merely meant to make the point that the Timer function of Forms in Access (to me anyway) is easier than worrying about OnTime cleanup in Excel. And as far as not having some functionality in Access, you can always pull in `Excel.Application` or whatever library references you need. – Gaffi Mar 28 '12 at 10:48