Access VBA to incorporate a web service (weather) ?

[hinsdale1]

Absolute genius.. Just when I was about to admit defeat, June to the rescue again. Was out this evening but looking forward to trying it out.. will be thrilled to finally get it working. Thanks!

[hinsdale1]

June7, your coding works like a charm as usual - I couldn't be happier.

[hinsdale1]

June7, you mentioned that feeding the api response into a table is an option.. can you give me a shove in the right direction as to how one might accomplish?.

I am now looking for more than just the current hourly forecast and would like to use the hourly10day option. (same format - just switch the feature from hourly to hourly10day). Im just not sure how to handle the switch of days . They just seem to end the hourly forecast for one day (</forecast>) at hour 23 and then start the new day with a <forecast> tag at hour 0 - not sure how to handle this.

I guess if you include the <mon_abbrev> and <mday> nodes you could just use a "For Each loop" to fill a table and include the date tags in order to differentiate each days hourly temp readings - but this brings me back to my first issue; I don't know how to propagate a table

Looking to fill the hourly <temp>.<english> and <condition> nodes for the 10 day forecast into a table.

[June7]

Couple ways to write to table.

One is to open a recordset of the destination table and insert new records to the recordset, then the recordset records are committed to table.
http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

Another is to send each individual record direct to table:

CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3, etc) VALUES(value1, value2, value3, etc)

The values could be concatenated variables, example for date, text, number:

CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES(#" & variable1 & "#, '" & variable2 & "', " & variable3)

[hinsdale1]

Thanks June - really helpful and almost there.

I was thinking i would need just utilize some sort of temprorary table to store the data to be accessed by the form (that would be deleted after use)?, I'm not sure how many fields will be required each time I pull the hourly10day forecast so I was hoping I could use VBA to create the table, create relevant fields to accept the variables, and populate from the API response text? Is something like that possible?

[June7]

Yes, that is possible.

Why would the number of fields vary?

[hinsdale1]

Because its my understanding that the hourly forecast changes as the hours of the current day pass? so there are 23 data points of hourly temps forecast for the first day at 1AM and only 1 data point of hourly temps forecast for the first day remains at 11PM?

[June7]

Could just set up the table for the maximum possible number of fields. The code will only populate those that are needed for the downloaded data.

[hinsdale1]

Thanks june. I wound up just using .addnew (or .edit - if primary key exists) to handle the propogation of the table so I didnt have to setup specific number of fields to receive - and the table can grow if I decide to retain the temperature data for some period.

Thanks again.. you've been a great help as usual.

[June7]

Am I am missing something there? .AddNew does not create fields in table. It will just populate a field that already exists.

[hinsdale1]

Am I am missing something there? .AddNew does not create fields in table. It will just populate a field that already exists.

No, it wasn't you - I keep confusing fields with records/rows. I thought you meant I had to create rows/PrimaryIDs/Records ahead of time to receive each of the 240 temperature data points in the hourly10day forecast. Not just the 4 fields in the table I needed.

Assuming I am an idiot will serve you best when attempting to understand one of my posts.

[June7]

My statement wasn't entirely accurate - AddNew initiates a new record in a recordset, then can populate fields of the record. You have table set up so each time data is a separate record? I had thought you would want each day as single record and a field for each hour of the day. So there was a misunderstanding on my part.

[hinsdale1]

Yes I have the table setup using the <pretty></pretty> node of the hourly10Day feature:

Example: "10:00 AM CDT on May 13, 2013"

as the indexed primary key field.

That way I can populate the table with the temps/chance_of_rain/etc from several different locations/zip codes (using a couple of GET's) and prevent duplicate records. If the primary key doesnt exist it uses .addnew - if the primary key is there then it uses .edit to update the fields as they change or propogate fields setup for secondary locations with secondary GET's.

[hinsdale1]

My "For Each/Next" loop seems to stall after parsing the first Tag correctly and placing in the appropriate field. So I must have the wrong syntax to parse the xml but cant seem to get it working.

Anyone (or hopefully June who is so good at this) care to point out the errors of my way?

Here is the weather api service response xml text that I am trying to parse:

<response>
    <version>0.1</version>
    <termsofService>http://www.wunderground.com/weather/api/d/terms.html</termsofService>
    <features>
        <feature>forecast</feature>
    </features>
    <forecast>
        <txt_forecast>
        <date>4:00 PM CDT</date>
        <forecastdays>
        <forecastday>
        <period>0</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Thursday</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy in the morning, then clear. High of 81F. Winds from the NNW at 5 to 10 mph shifting to the ENE in the afternoon.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy in the morning, then clear. High of 27C. Winds from the NNW at 5 to 20 km/h shifting to the ENE in the afternoon.]=]=></fcttext_metric>
        <pop>10</pop>
        </forecastday>
        <forecastday>
        <period>1</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Thursday Night</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy. Low of 52F. Winds from the ENE at 5 to 15 mph.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy. Low of 11C. Breezy. Winds from the ENE at 10 to 20 km/h.]=]=></fcttext_metric>
        <pop>10</pop>
        </forecastday>
        <forecastday>
        <period>2</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Friday</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy with  a chance of a thunderstorm and a chance of rain in the afternoon. High of 75F. Winds from the ENE at 5 to 15 mph. Chance of rain 20%.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy with  a chance of a thunderstorm and a chance of rain in the afternoon. High of 24C. Breezy. Winds from the ENE at 10 to 20 km/h.]=]=></fcttext_metric>
        <pop>20</pop>
        </forecastday>
        <forecastday>
        <period>3</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Friday Night</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy with a chance of a thunderstorm and a chance of rain. Low of 59F. Winds from the North at 5 to 10 mph shifting to the ESE after midnight. Chance of rain 20%.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy with a chance of a thunderstorm and a chance of rain. Low of 15C. Winds from the North at 10 to 15 km/h shifting to the ESE after midnight.]=]=></fcttext_metric>
        <pop>20</pop>
        </forecastday>
        <forecastday>
        <period>4</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Saturday</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy with a chance of a thunderstorm and a chance of rain. High of 73F. Winds from the SE at 5 to 10 mph. Chance of rain 20%.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy with a chance of a thunderstorm and a chance of rain. High of 23C. Breezy. Winds from the SE at 10 to 20 km/h.]=]=></fcttext_metric>
        <pop>20</pop>
        </forecastday>
        <forecastday>
        <period>5</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Saturday Night</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy. Low of 57F. Winds from the NE at 5 to 15 mph shifting to the ESE after midnight.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy. Low of 14C. Breezy. Winds from the NE at 10 to 20 km/h shifting to the ESE after midnight.]=]=></fcttext_metric>
        <pop>0</pop>
        </forecastday>
        <forecastday>
        <period>6</period>
        <icon>partlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
        <title>Sunday</title>
        <fcttext><=!=[=C=D=A=T=A=[Mostly cloudy. High of 75F. Winds from the East at 5 to 10 mph.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Mostly cloudy. High of 24C. Winds from the East at 10 to 15 km/h.]=]=></fcttext_metric>
        <pop>0</pop>
        </forecastday>
        <forecastday>
        <period>7</period>
        <icon>mostlycloudy</icon>
        <icon_url>http://icons-ak.wxug.com/i/c/k/mostlycloudy.gif</icon_url>
        <title>Sunday Night</title>
        <fcttext><=!=[=C=D=A=T=A=[Partly cloudy in the evening, then overcast. Low of 63F. Winds from the SE at 10 to 15 mph.]=]=></fcttext>
        <fcttext_metric><=!=[=C=D=A=T=A=[Partly cloudy in the evening, then overcast. Low of 17C. Breezy. Winds from the SE at 15 to 25 km/h.]=]=></fcttext_metric>
        <pop>0</pop>
        </forecastday>
        </forecastdays>
        </txt_forecast>
        <simpleforecast>
        <forecastdays>
        <forecastday>
        <date>
  <epoch>1368759600</epoch>
  <pretty_short>10:00 PM CDT</pretty_short>
  <pretty>10:00 PM CDT on May 16, 2013</pretty>
  <day>16</day>
  <month>5</month>
  <year>2013</year>
  <yday>135</yday>
  <hour>22</hour>
  <min>00</min>
  <sec>0</sec>
  <isdst>1</isdst>
  <monthname>May</monthname>
  <weekday_short>Thu</weekday_short>
  <weekday>Thursday</weekday>
  <ampm>PM</ampm>
  <tz_short>CDT</tz_short>
  <tz_long>America/Chicago</tz_long>
</date>
                    <period>1</period>
                    <high>
                        <fahrenheit>81</fahrenheit>
                        <celsius>27</celsius>
                    </high>
                    <low>
                        <fahrenheit>52</fahrenheit>
                        <celsius>11</celsius>
                    </low>
                    <conditions>Partly Cloudy</conditions>
                    
                    <icon>partlycloudy</icon>
                    <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
                    <skyicon>partlycloudy</skyicon>
                    <pop>10</pop>
                    <qpf_allday>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_allday>
                    <qpf_day>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_day>
                    <qpf_night>
                        <in>0.04</in>
                        <mm>1.0</mm>
                    </qpf_night>
                    <snow_allday>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_allday>
                    <snow_day>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_day>
                    <snow_night>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_night>
                    <maxwind>
                        <mph>11</mph>
                        <kph>18</kph>
                        <dir>NE</dir>
                        <degrees>55</degrees>
                    </maxwind>
                    <avewind>
                        <mph>8</mph>
                        <kph>13</kph>
                        <dir>ENE</dir>
                        <degrees>73</degrees>
                    </avewind>
                    <avehumidity>53</avehumidity>
                    <maxhumidity>66</maxhumidity>
                    <minhumidity>29</minhumidity>
                </forecastday>
                
                <forecastday>
                    <date>
  <epoch>1368846000</epoch>
  <pretty_short>10:00 PM CDT</pretty_short>
  <pretty>10:00 PM CDT on May 17, 2013</pretty>
  <day>17</day>
  <month>5</month>
  <year>2013</year>
  <yday>136</yday>
  <hour>22</hour>
  <min>00</min>
  <sec>0</sec>
  <isdst>1</isdst>
  <monthname>May</monthname>
  <weekday_short>Fri</weekday_short>
  <weekday>Friday</weekday>
  <ampm>PM</ampm>
  <tz_short>CDT</tz_short>
  <tz_long>America/Chicago</tz_long>
</date>
                    <period>2</period>
                    <high>
                        <fahrenheit>75</fahrenheit>
                        <celsius>24</celsius>
                    </high>
                    <low>
                        <fahrenheit>59</fahrenheit>
                        <celsius>15</celsius>
                    </low>
                    <conditions>Chance of a Thunderstorm</conditions>
                    
                    <icon>partlycloudy</icon>
                    <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
                    <skyicon>partlycloudy</skyicon>
                    <pop>20</pop>
                    <qpf_allday>
                        <in>0.04</in>
                        <mm>1.0</mm>
                    </qpf_allday>
                    <qpf_day>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_day>
                    <qpf_night>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_night>
                    <snow_allday>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_allday>
                    <snow_day>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_day>
                    <snow_night>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_night>
                    <maxwind>
                        <mph>11</mph>
                        <kph>18</kph>
                        <dir>ENE</dir>
                        <degrees>67</degrees>
                    </maxwind>
                    <avewind>
                        <mph>9</mph>
                        <kph>14</kph>
                        <dir>ENE</dir>
                        <degrees>70</degrees>
                    </avewind>
                    <avehumidity>61</avehumidity>
                    <maxhumidity>72</maxhumidity>
                    <minhumidity>48</minhumidity>
                </forecastday>
                
                <forecastday>
                    <date>
  <epoch>1368932400</epoch>
  <pretty_short>10:00 PM CDT</pretty_short>
  <pretty>10:00 PM CDT on May 18, 2013</pretty>
  <day>18</day>
  <month>5</month>
  <year>2013</year>
  <yday>137</yday>
  <hour>22</hour>
  <min>00</min>
  <sec>0</sec>
  <isdst>1</isdst>
  <monthname>May</monthname>
  <weekday_short>Sat</weekday_short>
  <weekday>Saturday</weekday>
  <ampm>PM</ampm>
  <tz_short>CDT</tz_short>
  <tz_long>America/Chicago</tz_long>
</date>
                    <period>3</period>
                    <high>
                        <fahrenheit>73</fahrenheit>
                        <celsius>23</celsius>
                    </high>
                    <low>
                        <fahrenheit>57</fahrenheit>
                        <celsius>14</celsius>
                    </low>
                    <conditions>Chance of a Thunderstorm</conditions>
                    
                    <icon>partlycloudy</icon>
                    <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
                    <skyicon>partlycloudy</skyicon>
                    <pop>20</pop>
                    <qpf_allday>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_allday>
                    <qpf_day>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_day>
                    <qpf_night>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_night>
                    <snow_allday>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_allday>
                    <snow_day>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_day>
                    <snow_night>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_night>
                    <maxwind>
                        <mph>10</mph>
                        <kph>16</kph>
                        <dir>ENE</dir>
                        <degrees>59</degrees>
                    </maxwind>
                    <avewind>
                        <mph>9</mph>
                        <kph>14</kph>
                        <dir>ENE</dir>
                        <degrees>69</degrees>
                    </avewind>
                    <avehumidity>70</avehumidity>
                    <maxhumidity>74</maxhumidity>
                    <minhumidity>52</minhumidity>
                </forecastday>
                
                <forecastday>
                    <date>
  <epoch>1369018800</epoch>
  <pretty_short>10:00 PM CDT</pretty_short>
  <pretty>10:00 PM CDT on May 19, 2013</pretty>
  <day>19</day>
  <month>5</month>
  <year>2013</year>
  <yday>138</yday>
  <hour>22</hour>
  <min>00</min>
  <sec>0</sec>
  <isdst>1</isdst>
  <monthname>May</monthname>
  <weekday_short>Sun</weekday_short>
  <weekday>Sunday</weekday>
  <ampm>PM</ampm>
  <tz_short>CDT</tz_short>
  <tz_long>America/Chicago</tz_long>
</date>
                    <period>4</period>
                    <high>
                        <fahrenheit>75</fahrenheit>
                        <celsius>24</celsius>
                    </high>
                    <low>
                        <fahrenheit>63</fahrenheit>
                        <celsius>17</celsius>
                    </low>
                    <conditions>Partly Cloudy</conditions>
                    
                    <icon>partlycloudy</icon>
                    <icon_url>http://icons-ak.wxug.com/i/c/k/partlycloudy.gif</icon_url>
                    <skyicon>partlycloudy</skyicon>
                    <pop>0</pop>
                    <qpf_allday>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_allday>
                    <qpf_day>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_day>
                    <qpf_night>
                        <in>0.00</in>
                        <mm>0.0</mm>
                    </qpf_night>
                    <snow_allday>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_allday>
                    <snow_day>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_day>
                    <snow_night>
                        <in>0</in>
                        <cm>0</cm>
                    </snow_night>
                    <maxwind>
                        <mph>9</mph>
                        <kph>14</kph>
                        <dir>East</dir>
                        <degrees>91</degrees>
                    </maxwind>
                    <avewind>
                        <mph>7</mph>
                        <kph>11</kph>
                        <dir>East</dir>
                        <degrees>89</degrees>
                    </avewind>
                    <avehumidity>70</avehumidity>
                    <maxhumidity>86</maxhumidity>
                    <minhumidity>58</minhumidity>
                </forecastday>
                
            </forecastdays>
        </simpleforecast>
    </forecast>


    
    
    
    
    
    
    
    
    
    




    
    
</response>

And here is the vba script I am trying to use to parse but seems to stop for some reason after filling in the first correct txtForecast1 field with the correct forecast text:

Private Sub btnGetWeather_Click()

Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim i As Integer
Dim Forecast As IXMLDOMNode
Req.Open "GET", "http://api.wunderground.com/api/YOUR KEY HERE/forecast/q/IL/60603.xml", False
Req.send
Resp.loadXML Req.responseText
For Each Forecast In Resp.getElementsByTagName("forecast")
i = i + 1
Me.Controls("txtForecast" & i) = Forecast.selectNodes("txt_forecast")(0).selectNode s("forecastdays")(0).selectNodes("forecastday")(0) .selectNodes("fcttext")(0).Text

Next

End Sub

[hinsdale1]

Well with a lot of random trial and error I seem to have stumbled upon the proper syntax... I guess even a blind squirrel finds a nut once in a while.

Dim Req As New XMLHTTP
Dim Resp As New DOMDocument
Dim i As Integer
Dim Forecast As IXMLDOMNode
Req.Open "GET", "http://api.wunderground.com/api/YOUR KEY HERE/forecast/q/IL/60603.xml", False
Req.send
Resp.loadXML Req.responseText
For Each Forecastday In Resp.getElementsByTagName("forecastday")
i = i + 1
Me.Controls("txtForecast" & i) = Forecastday.selectNodes("fcttext")(0).Text


Next