I think at least two ways to pull this data from web page. Neither requires the Web Browser control.
1. VBA code opens the web page as an Internet Explorer object. Requires VBA reference to Microsoft Internet Controls. Does not require an API account. This was the method I originally had in mind.
2. VBA uses website's API interface, requires an account and a key code and VBA reference to Microsoft XML.
Got this much to work with Wunderground:
Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim Weather As IXMLDOMNode
Req.Open "GET", "http://api.wunderground.com/api/YOUR KEY CODE HERE/forecast10day/q/CA/San_Francisco.xml", False
Req.Send
Debug.Print Req.responseText
Unfortunately, I am not seeing a 5-day forecast parameter with the Wunderground API. I see a 10-day but the XML structure is really different from the video example. Switched to WorldWeatherOnline because have a working example in the video and seems to offer more help tools.
I set up a form with 5 controls for each data element (date, day, max temp, min temp, image). Gave them names like: Date1, Date2, etc. I set format property for the date textboxes as mm/dd/yyyy. Set format property for the day textboxes as ddd and ControlSource of =Date1, etc.
Code:
Private Sub btnRefresh_Click()
Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim Weather As IXMLDOMNode
Dim i As Integer
'WorldWeatherOnline API, could not get the syntax from video to work, the second version is from the website
''Req.Open "GET", "http://free.worldweatheronline.com/feed/weather.ashx?q=CITY NAME HERE&format=xml&num_of_days=5&key=YOUR KEY CODE HERE", False
Req.Open "GET", "http://api.worldweatheronline.com/free/v1/weather.ashx?q=CITY NAME HERE&format=xml&num_of_days=5&key=YOUR KEY CODE HERE", False
Req.Send
Resp.LoadXML Req.responseText
For Each Weather In Resp.getElementsByTagName("weather")
i = i + 1
Me.Controls("Date" & i) = CDate(Weather.SelectNodes("date")(0).Text)
Me.Controls("Hi" & i) = Weather.SelectNodes("tempMaxF")(0).Text
Me.Controls("Lo" & i) = Weather.SelectNodes("tempMinF")(0).Text
''Me.Controls("Image" & i).Picture = Weather.SelectNodes("weatherIconUrl").Item(0).Text
Next
End Sub
If the city name is multiple words, spaces must be replaced.
Wunderground example: San_Francisco
World example: san+diego
What I am stuck on is getting the images to display. I tried Image controls and setting the Picture property. I don't think the Image control ControlSource property can be set with VBA, found that out helping another poster. VBA gets the URL string but then just errors 'Microsoft Access can't open the file'. I read one source that says image must be a jpg. These weather images are png files. I also did a test with a spreadsheet and that works.
If you want to write data to a table instead of into controls on form, there are ways to do that.
Access VBA to incorporate a web service (weather) ?
Windows 7 64bit 
Access 2013
Reply With Quote
Originally Posted by June7
