Access VBA to incorporate a web service (weather) ?

[AlexHedley]

Ah was sure that worked must just be UNC server paths I was thinking about.



Found an article from MS giving a few different options but "http://" they suggest the WebBrowser Control.

http://support.microsoft.com/default...b;en-us;285820

[June7]

Not finding code to dynamically download the images with each data refresh. The idea of copying all the images from webpage and storing them locally then displaying from that local set will work. The complication I find is that the name in the URL captured by code is not the name given to the downloaded image. This would be an issue with either approach. Try:

1. Create 5 textboxes, one for each day of the forecast, name them like Desc1, Desc2, etc.

2. Modify the btnRefresh code to capture the weather description into the textboxes
Me.Controls("Desc" & i)= Trim(Weather.SelectNodes("weatherDesc").Item(0).Te xt)

3. Set each Image control ControlSource to: ="your image files path\" & [Desc1] & ".png", changing the number suffix for each.

4. After downloading images rename them to the corresponding weatherDesc value. You can see the weather description below each image on the website.

[hinsdale1]

That's exactly the insight I was looking for - thanks so much June. I can use that.

In the meantime I was testing out displaying the url .png images using Web browser controls... and they took a bit to format aesthetically - but seem to be a functional solution. If they continue to work as neeeded then it may just save me the effort of manually locating and storing all the images.

Again, I really appreciate all your help June - u have been a lifesaver.

I am still planning on tackling the Wunderground data and see if I can parse anything from their xml service - I will certainly be in over my head so u can expect more questions

[hinsdale1]

Ah was sure that worked must just be UNC server paths I was thinking about.

Found an article from MS giving a few different options but "http://" they suggest the WebBrowser Control.

http://support.microsoft.com/default...b;en-us;285820

Thanks for contributing your ideas Alex.. was really helpful for me.

[June7]

Additional info about displaying images. I just discovered that some images are used for multiple weather descriptions. This has two solutions:

1. copies of images with the different names

2. a function called by the Image controls that returns the image name based on the weather description. As a for instance, any description with the word 'rain' would return the image named Rain.png.

However, if displaying images in web browser controls is working, that seems the simpler approach.

[hinsdale1]

Thanks for the info. Luckily so far using the web browser controls to display the images seems to function fine for my needs - so probably won't mess with other image options for now.

My next job (tomorrow) is to tackle that wunderground xml - so any suggestions you have as to vba code lines I might try to parse the: hour, temp, condition, "feels like" | for particular hours (for example 7AM, 10AM, 1PM) would BE VERY MUCH APPRECIATED:

Here is just the first two hours (hours 2 and 3) of the generated xml response for the Hourly forecast (full response is over 50000 characters limit):

<response>
            <version>0.1</version>
            <termsofService>http://www.wunderground.com/weather/api/d/terms.html</termsofService>
            <features>
                        <feature>hourly</feature>
            </features>
            <hourly_forecast>
                        <forecast>
                        <FCTTIME>
                        <hour>2</hour><hour_padded>02</hour_padded><min>00</min><sec>0</sec><year>2013</year><mon>5</mon><mon_padded>05</mon_padded><mon_abbrev>May</mon_abbrev><mday>8</mday><mday_padded>08</mday_padded><yday>127</yday><isdst>1</isdst><epoch>1367996400</epoch><pretty>2:00 AM CDT on May 08, 2013</pretty><civil>2:00 AM</civil><month_name>May</month_name><month_name_abbrev>May</month_name_abbrev><weekday_name>Wednesday</weekday_name><weekday_name_night>Wednesday Night</weekday_name_night><weekday_name_abbrev>Wed</weekday_name_abbrev><weekday_name_unlang>Wednesday</weekday_name_unlang><weekday_name_night_unlang>Wednesday Night</weekday_name_night_unlang><ampm>AM</ampm><tz></tz><age></age>
                                    </FCTTIME>
                                    <temp>
                                                <english>53</english>
                                                <metric>11</metric>
                                    </temp>
                                    <dewpoint>
                                                <english>42</english>
                                                <metric>5</metric>
                                    </dewpoint>
                                    <condition>Partly Cloudy</condition>
                                   
                                   
                                    <icon>partlycloudy</icon>
                                    <icon_url>http://icons-ak.wxug.com/i/c/k/nt_partlycloudy.gif</icon_url>
                                    <fctcode>2</fctcode>
                                    <sky>29</sky>
                                    <wspd>
                                                <english>3</english>
                                                <metric>4</metric>
                                    </wspd>
                                    <wdir>
                                                <dir>NE</dir>
                                                <degrees>34</degrees>
                                    </wdir>
                                    <wx></wx>
                                    <uvi>0</uvi>
                                    <humidity>66</humidity>
                                    <windchill>
                                                <english>-9998</english>
                                                <metric>-9998</metric>
                                    </windchill>
                                    <heatindex>
                                                <english>-9998</english>
                                                <metric>-9998</metric>
                                    </heatindex>
                                   
                                                <feelslike>
                                                            <english>53</english>
                                                            <metric>11</metric>
                                                </feelslike>
                                   
                                    <qpf>
                                                <english></english>
                                                <metric></metric>
                                    </qpf>
                                    <snow>
                                                <english></english>
                                                <metric></metric>
                                    </snow>
                                    <pop>0</pop>
                                    <mslp>
                                                <english>30.01</english>
                                                <metric>1016</metric>
                                    </mslp>
                        </forecast>
                       
           
                       
                        <forecast>
                                    <FCTTIME>
                                                <hour>3</hour><hour_padded>03</hour_padded><min>00</min><sec>0</sec><year>2013</year><mon>5</mon><mon_padded>05</mon_padded><mon_abbrev>May</mon_abbrev><mday>8</mday><mday_padded>08</mday_padded><yday>127</yday><isdst>1</isdst><epoch>1368000000</epoch><pretty>3:00 AM CDT on May 08, 2013</pretty><civil>3:00 AM</civil><month_name>May</month_name><month_name_abbrev>May</month_name_abbrev><weekday_name>Wednesday</weekday_name><weekday_name_night>Wednesday Night</weekday_name_night><weekday_name_abbrev>Wed</weekday_name_abbrev><weekday_name_unlang>Wednesday</weekday_name_unlang><weekday_name_night_unlang>Wednesday Night</weekday_name_night_unlang><ampm>AM</ampm><tz></tz><age></age>
                                    </FCTTIME>
                                    <temp>
                                                <english>51</english>
                                                <metric>11</metric>
                                    </temp>
                                    <dewpoint>
                                                <english>42</english>
                                                <metric>6</metric>
                                    </dewpoint>
                                    <condition>Partly Cloudy</condition>
                                   
                                   
                                    <icon>partlycloudy</icon>
                                    <icon_url>http://icons-ak.wxug.com/i/c/k/nt_partlycloudy.gif</icon_url>
                                    <fctcode>2</fctcode>
                                    <sky>29</sky>
                                    <wspd>
                                                <english>2</english>
                                                <metric>4</metric>
                                    </wspd>
                                    <wdir>
                                                <dir>NE</dir>
                                                <degrees>34</degrees>
                                    </wdir>
                                    <wx></wx>
                                    <uvi>0</uvi>
                                    <humidity>71</humidity>
                                    <windchill>
                                                <english>-9998</english>
                                                <metric>-9998</metric>
                                    </windchill>
                                    <heatindex>
                                                <english>-9998</english>
                                                <metric>-9998</metric>
                                    </heatindex>
                                   
                                                <feelslike>
                                                            <english>51</english>
                                                            <metric>11</metric>
                                                </feelslike>
                                   
                                    <qpf>
                                                <english></english>
                                                <metric></metric>
                                    </qpf>
                                    <snow>
                                                <english></english>
                                                <metric></metric>
                                    </snow>
                                    <pop>0</pop>
                                    <mslp>
                                                <english>30.01</english>
                                                <metric>1016</metric>
                                    </mslp>
                        </forecast>
                       
           
                       
                        <forecast>
                                    <FCTTIME>

Im starting pretty clueless - no idea how to differentiate between the temp of hour 2 or hour 3 for example. They provide some coding samples but not for VBA so not helping me too much right now, maybe it will help you?:

http://www.wunderground.com/weather/...e-samples&MR=1

[June7]

The only thing I can think of at the moment is in the loop that reads the nodes, have a conditional structure that retrieves only the desired hours, like:

Select Case "the hour node reference here"
Case 7, 10, 13
'get data
End Select

[hinsdale1]

Sorry for delay - our family bulldog (and great friend) unexpectedly died on wednesday so it has been a bit of a rough week around here.

I must say that my I am having real difficulty understanding the vba syntax to parse xml and not finding good examples to pattern. The xml response text I am trying to parse is 2 posts above here in post #21.

Trying to use only working vba code i have and adapt to the xml from Weather Underground like this:

Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim Weather As IXMLDOMNode
Req.Open "GET", "http://api.wunderground.com/api/ENTER YOUR KEY HERE/hourly/q/IL/Hinsdale.xml", False
Req.send
Resp.loadXML Req.responseText


For Each Weather In Resp.getElementsByTagName("weather")
  
  i = i + 1
  Me.Controls("Date" & i) = Weather.selectNodes("/response/hourly_forecast/forecast/FCTTIME[@hour=21]/temp/english")(0).Text
  
Next

But getting nothing.. cant seem to come up with a working syntax.. anyone have other suggestions to try?

[orange]

If you use weather underground you can get info in csv format -- if you don't care to use XML etc.

There is a thread here
see http://www.utteraccess.com/forum/Pul...-t1999204.html

I tried it for a local airport YOW Ottawa with this request.

http://www.wunderground.com/history/...tario&format=1

Good luck.

[June7]

A text file like that would be easier than the xml to parse. Odd, there are 2 records each for 2am, 8am, 2pm.

[orange]

Yes, csv / text would be easier. I have no idea (haven't research wunderground for an explanation of the 2 records.
I tried it with a March 9 2013 date and get 2 records for 1am, 7 am and 1pm , 7pm???

[hinsdale1]

I am having trouble locating the forecast data in a comma delimited file. I see it on the history page Orange used as an example but not seeing a csv option for any of the forecast pages I am looking at like:

http://www.wunderground.com/cgi-bin/...=Saturday&MR=1

I tried adding &format=1 to the url but no luck.. am i missing something? If there is no cvs, would it be easier to parse the above webpage then it is to parse the xml (and if so could someone give me some instruction?)? If i could parse this page it would be useful for me.

[orange]

I believe the csv is only for history info.
If you use today's date, you will only get results up to the last hour, an even then you may get only up to 2 hours ago depending on how the wunderground updating occurs.

At 11:06, I'm only getting up to 10 AM, so it isn't updated immediately on the hour.

[hinsdale1]

I believe the csv is only for history info.
If you use today's date, you will only get results up to the last hour, an even then you may get only up to 2 hours ago depending on how the wunderground updating occurs.

At 11:06, I'm only getting up to 10 AM, so it isn't updated immediately on the hour.

Thanks for help but unfortunately I need forecast (future) data.

I was thinking XML was specifically designed to be easily parsed so am kind of surprised I can't seem to find any useful examples of parsing using Visual Basic for Applications?

I am completely stuck. So if anyone has any vba code suggestions to parse the xml response text (see post #21 above) please don't hesitate to post. If I could just figure out the syntax to get one field I would be delirious - so any and all input/ suggestions are welcomed.

[June7]

You need hourly forecast for the current day, not longer-term forecast?

For starters, the 'weather' tag name is not in the Wunderground xml, that is a WorldWeatherOnline tag.

Next, the xml structure for the hourly forecast is more complicated. The principle node of interest is FORECAST. FORECAST has subnodes and each subnode has subsubnodes (or elements). For instance the date/time info is a subnode called FCTTIME and that data is further detailed in elements. Syntax for referencing into the elements was a challenge. This gets results:

Sub GetWeatherWUN()
Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim Forecast As IXMLDOMNode
Req.Open "GET", "http://api.wunderground.com/api/KEY CODE HERE/hourly/q/IL/Hinsdale.xml", False
Req.Send
Resp.LoadXML Req.responseText
For Each Forecast In Resp.getElementsByTagName("forecast")
Debug.Print Forecast.SelectNodes("FCTTIME")(0).SelectNodes("ho ur")(0).Text
Debug.Print Forecast.SelectNodes("temp")(0).SelectNodes("engli sh")(0).Text
Next
End Sub

Careful, case seems to matter in referencing tags. The forum post is throwing in those extra spaces in "hour" and "english".