Pull Data from a Website

[LadyDi]

I was just wondering if it is possible to program Access so that it will go to a website, enter search criteria and then copy the resulting data.

I have recently been asked to track the weather condition in cities where my company's equipment has problems. The website that I found to capture this information is http://www.wunderground.com/history/. Right now, I have to go to the website, enter the zip code for the city, enter the date I need the weather for (yesterday's date), click the submit button, and then fill in the information that I see on that website in my database. Can Access do those steps for me, so that all I need to do is click a button? I'm attaching a snapshot of some of the information that I am copying to my database. Any advice or direction you can provide would be greatly appreciated.

 

[jdraw]

After reading you request I googled and found this info. Seems a good starting point. I haven't used it and have not interacted with web pages in the manner you're looking for.

I have accessed online Foreign exchange rates etc, but that was all xml.

Anyway here's the link
http://www.utteraccess.com/wiki/index.php/Access_and_the_web_HTML_Object_Library

Post back if you get something working-- I'm interested.
Good luck.

 

[Rx_]

I would also be very interested in the method described above.
http://www.cmegroup.com/market-data/delayed-quotes/commodities.html
This is a method I used at another site, sorry I don't have the code on-hand to share.
If you take the web site above and Select All - copy
then open a new Excel worksheet - go to cell one and Past As Text
The above could be done by the old Send-Key and Excel automation.

Using Excel to locate the cell with Floor with Corn below it -
Then go out to the columns to gather the information.
I provided this for a group of commodities (e.g. natural gas) for terminal and storage.

Sucked that Excel data into a database with Access front end (SQL back end).

Here is what I experienced over a couple of years...
On some days, Corn won't be posted. Or they may introduce a new term such as "CornI" and CornD (international / domestic) Or, they add a new category.
Here is the other thing - they sometimes change the tags at random.
Why? Well, they sell a nice $5,000 / month subscription data service.

So, in your design for Excel, and probably for html tags too:
Build a maintenance table so the search item can be easily maintained.
Mine allowed the front-desk receptionist to take care of that kind of behavior.

The Excel interface model was real easy for a worker bee to understand.

In one case, they didn't change anything for three months. Then, every other day, they changed in a rotation of three different style sheets. If we were publishing, it is exactly the kind of thing we might do too.

 

[spikepl]

In this thread http://www.access-programmers.co.uk/forums/showthread.php?t=226807&highlight=story I posted a screenscraper I made to scrape content from this story: http://www.access-programmers.co.uk/forums/showthread.php?t=69111&highlight=story

However, screenscraping always entails risk of changes down the road, as described by Rx in #3.

Besides, many sites have ToS forbidding scraping.

Weather data I am sure can be collected in XML from a webservice for a fee, which would likely lessen maintenance needs. and could be automated 100 %

 

[LadyDi]

Thank you for all the assistance you were able to provide.

I just thought I would post back and let everyone know what I was able to come up with. The site I was referencing, had a comma delimited file that could be downloaded. I used the address for the comma delimited file and changed it based on my airport code and date. Then I programmed Access to import the comma delimited file into Excel, format it, and then import it into Access. It also runs through a loop and imports files for all airports in my database.

I can't take full credit for this though, someone on the UtterAccess site helped me out alot.