VBA XML SelectNodes - script errors out when nodes are missing

H

Hawk11ns

Board Regular
Joined
Jul 21, 2015
Messages
61
Office Version
  1. 365
Platform
  1. Windows
The below VBA script errors out when it reaches a node where the value is missing. For example, for most days, <precipitation> includes @VALUE. However, some days it does not and this is causing my script to error out. How can I tell the script to simply print 0 if it errors out and move on?

link to XML: http://api.openweathermap.org/data/...78079d2e2d49e35ddb&mode=xml&units=imperial&q=

VBA Script:

VBA Code: 
Public Sub getWeather()
Dim xmlhttp As New MSXML2.ServerXMLHTTP, myurl As String, xmlresponse As New DOMDocument

myurl = "http://api.openweathermap.org/data/2.5/forecast/daily?q=houston&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q="
xmlhttp.Open "GET", myurl, False
xmlhttp.Send
xmlresponse.LoadXML (xmlhttp.responseText)

'Houston 7-Day Weather Data
Worksheets("Weather").Range("B2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/location/name")(0).Text)
Worksheets("Weather").Range("C3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(0).Text
Worksheets("Weather").Range("C4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(0).Text)
Worksheets("Weather").Range("C5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(0).Text)
Worksheets("Weather").Range("C6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(0).Text)
Worksheets("Weather").Range("C7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(0).Text
Worksheets("Weather").Range("C8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(0).Text
Worksheets("Weather").Range("C9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(0).Text
Worksheets("Weather").Range("C10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(0).Text
Worksheets("Weather").Range("C11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(0).Text
Worksheets("Weather").Range("C12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(0).Text
Worksheets("Weather").Range("C13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(0).Text

Worksheets("Weather").Range("D3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(1).Text
Worksheets("Weather").Range("D4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(1).Text)
Worksheets("Weather").Range("D5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(1).Text)
Worksheets("Weather").Range("D6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(1).Text)
Worksheets("Weather").Range("D7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(1).Text
Worksheets("Weather").Range("D8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(1).Text
Worksheets("Weather").Range("D9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(1).Text
Worksheets("Weather").Range("D10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(1).Text
Worksheets("Weather").Range("D11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(1).Text
Worksheets("Weather").Range("D12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(1).Text
Worksheets("Weather").Range("D13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(1).Text

Worksheets("Weather").Range("E3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text
Worksheets("Weather").Range("E4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(2).Text)
Worksheets("Weather").Range("E5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(2).Text)
Worksheets("Weather").Range("E6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(2).Text)
Worksheets("Weather").Range("E7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(2).Text
Worksheets("Weather").Range("E8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(2).Text
Worksheets("Weather").Range("E9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(2).Text
Worksheets("Weather").Range("E10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(2).Text
Worksheets("Weather").Range("E11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(2).Text
Worksheets("Weather").Range("E12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(2).Text
Worksheets("Weather").Range("E13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(2).Text

Worksheets("Weather").Range("F3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(3).Text
Worksheets("Weather").Range("F4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(3).Text)
Worksheets("Weather").Range("F5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(3).Text)
Worksheets("Weather").Range("F6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(3).Text)
Worksheets("Weather").Range("F7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(3).Text
Worksheets("Weather").Range("F8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(3).Text
Worksheets("Weather").Range("F9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(3).Text
Worksheets("Weather").Range("F10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(3).Text
Worksheets("Weather").Range("F11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(3).Text
Worksheets("Weather").Range("F12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(3).Text
Worksheets("Weather").Range("F13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(3).Text

Worksheets("Weather").Range("G3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(4).Text
Worksheets("Weather").Range("G4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(4).Text)
Worksheets("Weather").Range("G5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(4).Text)
Worksheets("Weather").Range("G6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(4).Text)
Worksheets("Weather").Range("G7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(4).Text
Worksheets("Weather").Range("G8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(4).Text
Worksheets("Weather").Range("G9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(4).Text
Worksheets("Weather").Range("G10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(4).Text
Worksheets("Weather").Range("G11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(4).Text
Worksheets("Weather").Range("G12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(4).Text
Worksheets("Weather").Range("G13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(4).Text

Worksheets("Weather").Range("H3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(5).Text
Worksheets("Weather").Range("H4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(5).Text)
Worksheets("Weather").Range("H5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(5).Text)
Worksheets("Weather").Range("H6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(5).Text)
Worksheets("Weather").Range("H7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(5).Text
Worksheets("Weather").Range("H8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(5).Text
Worksheets("Weather").Range("H9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(5).Text
Worksheets("Weather").Range("H10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(5).Text
Worksheets("Weather").Range("H11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(5).Text
Worksheets("Weather").Range("H12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(5).Text
Worksheets("Weather").Range("H13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(5).Text

Worksheets("Weather").Range("I3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(6).Text
Worksheets("Weather").Range("I4").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(6).Text)
Worksheets("Weather").Range("I5").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@value")(6).Text)
Worksheets("Weather").Range("I6").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@name")(6).Text)
Worksheets("Weather").Range("I7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(6).Text
Worksheets("Weather").Range("I8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(6).Text
Worksheets("Weather").Range("I9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(6).Text
Worksheets("Weather").Range("I10").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(6).Text
Worksheets("Weather").Range("I11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(6).Text
Worksheets("Weather").Range("I12").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(6).Text
Worksheets("Weather").Range("I13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text

'MsgBox (xmlresponse.getElementsByTagName("temperature")(0).Attributes(1).Text)  Alternate method to parse XML

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
The simplest way is that you clear the target area then use On Error Resume Next, as follows:
VBA Code: 
'Houston 7-Day Weather Data
Worksheets("Weather").Range("C3:I13").ClearContents     'Clear the target cells
On Error Resume Next
Worksheets("Weather").Range("B2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/location/name")(0).Text)
'..
'..all the other extracted parametres
'..
Worksheets("Weather").Range("I13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text
On Error GoTo 0
'MsgBox (xmlresponse.getElementsByTagName("temperature")(0).Attributes(1).Text)  Alternate method to parse XML
Bye
 
Upvote 0
Anthony47 said:
The simplest way is that you clear the target area then use On Error Resume Next, as follows:
VBA Code: 
'Houston 7-Day Weather Data
Worksheets("Weather").Range("C3:I13").ClearContents     'Clear the target cells
On Error Resume Next
Worksheets("Weather").Range("B2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/location/name")(0).Text)
'..
'..all the other extracted parametres
'..
Worksheets("Weather").Range("I13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text
On Error GoTo 0
'MsgBox (xmlresponse.getElementsByTagName("temperature")(0).Attributes(1).Text)  Alternate method to parse XML
Bye
Click to expand...

This works perfectly; however, when there is no value present, the cell is returned blank. How can I populate the cell to show 0.00? Also, some of the output values (i.e. humidity and cloud cover)" need to be divided by 100 in order to convert to a proper percentage. is it possible to do this or perhaps possible to just add the '%'symbol to the text output?
 
Upvote 0
To initialize all the destination cells, use
VBA Code: 
Worksheets("Weather").Range("C3:I13").Value = "'0.00"      'Instead of .ClearContents
If you know that some values are percentage, modify the way you write the cell; for example:
Rich (BB code): 
Worksheets("Weather").Range("E3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text & "%"
Bye
 
Upvote 0
Anthony47 said:
To initialize all the destination cells, use
VBA Code: 
Worksheets("Weather").Range("C3:I13").Value = "'0.00"      'Instead of .ClearContents
If you know that some values are percentage, modify the way you write the cell; for example:
Rich (BB code): 
Worksheets("Weather").Range("E3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text & "%"
Bye
Click to expand...

Thanks for all your help, Anthony! Below is the code I've assembled. One thing I noticed, is the SelectNodes xpath language I am using will return the next value in line if a value is missing. For example, the 'Rain mm' value for 17-Sep is pulling in the 20-Sep value because the xml does not have values for the 17-19th. How can I correct this?

1600382072072.png

1600382039285.png


VBA Code: 
Public Sub getWeatherHouston()
'Call mxlhttp library service and create DOM document
    Dim xmlhttp As New MSXML2.ServerXMLHTTP, myURL As String, xmlresponse As New DOMDocument

' Generate a random, six-digit number..
    Dim intRand As Long
    intRand = Int((900000) * Rnd) + 100000

' Add the number as a param to the request...
    Dim strUrl As String
    strUrl = "http://api.openweathermap.org/data/2.5/forecast/daily?q=houston&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah=" & intRand
    
    xmlhttp.Open "GET", strUrl, False
    xmlhttp.send
    xmlresponse.LoadXML (xmlhttp.responseText)
    Worksheets("Weather").Range("B2:H9").Value = "0.00"
    Worksheets("Weather").Range("B12:H19").Value = "0.00"
    Worksheets("Weather").Range("B22:H29").Value = "0.00"
    Worksheets("Weather").Range("B32:H39").Value = "0.00"
    
        On Error Resume Next
            Worksheets("Weather").Range("B1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(0).Text
            Worksheets("Weather").Range("B2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(0).Text)
            Worksheets("Weather").Range("B3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(0).Text
            Worksheets("Weather").Range("B4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(0).Text
            Worksheets("Weather").Range("B5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(0).Text
            Worksheets("Weather").Range("B6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(0).Text
            Worksheets("Weather").Range("B7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(0).Text & "%"
            Worksheets("Weather").Range("B8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(0).Text & "%"
            Worksheets("Weather").Range("B9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(0).Text
            
            Worksheets("Weather").Range("C1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(1).Text
            Worksheets("Weather").Range("C2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(1).Text)
            Worksheets("Weather").Range("C3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(1).Text
            Worksheets("Weather").Range("C4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(1).Text
            Worksheets("Weather").Range("C5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(1).Text
            Worksheets("Weather").Range("C6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(1).Text
            Worksheets("Weather").Range("C7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(1).Text & "%"
            Worksheets("Weather").Range("C8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(1).Text & "%"
            Worksheets("Weather").Range("C9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(1).Text
        
            Worksheets("Weather").Range("D1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text
            Worksheets("Weather").Range("D2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(2).Text)
            Worksheets("Weather").Range("D3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(2).Text
            Worksheets("Weather").Range("D4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(2).Text
            Worksheets("Weather").Range("D5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(2).Text
            Worksheets("Weather").Range("D6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(2).Text
            Worksheets("Weather").Range("D7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(2).Text & "%"
            Worksheets("Weather").Range("D8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(2).Text & "%"
            Worksheets("Weather").Range("D9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(2).Text
        
            Worksheets("Weather").Range("E1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(3).Text
            Worksheets("Weather").Range("E2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(3).Text)
            Worksheets("Weather").Range("E3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(3).Text
            Worksheets("Weather").Range("E4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(3).Text
            Worksheets("Weather").Range("E5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(3).Text
            Worksheets("Weather").Range("E6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(3).Text
            Worksheets("Weather").Range("E7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(3).Text & "%"
            Worksheets("Weather").Range("E8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(3).Text & "%"
            Worksheets("Weather").Range("E9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(3).Text
        
            Worksheets("Weather").Range("F1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(4).Text
            Worksheets("Weather").Range("F2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(4).Text)
            Worksheets("Weather").Range("F3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(4).Text
            Worksheets("Weather").Range("F4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(4).Text
            Worksheets("Weather").Range("F5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(4).Text
            Worksheets("Weather").Range("F6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(4).Text
            Worksheets("Weather").Range("F7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(4).Text & "%"
            Worksheets("Weather").Range("F8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(4).Text & "%"
            Worksheets("Weather").Range("F9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(4).Text
        
            Worksheets("Weather").Range("G1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(5).Text
            Worksheets("Weather").Range("G2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(5).Text)
            Worksheets("Weather").Range("G3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(5).Text
            Worksheets("Weather").Range("G4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(5).Text
            Worksheets("Weather").Range("G5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(5).Text
            Worksheets("Weather").Range("G6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(5).Text
            Worksheets("Weather").Range("G7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(5).Text & "%"
            Worksheets("Weather").Range("G8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(5).Text & "%"
            Worksheets("Weather").Range("G9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(5).Text
        
            Worksheets("Weather").Range("H1").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(6).Text
            Worksheets("Weather").Range("H2").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(6).Text)
            Worksheets("Weather").Range("H3").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(6).Text
            Worksheets("Weather").Range("H4").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(6).Text
            Worksheets("Weather").Range("H5").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(6).Text
            Worksheets("Weather").Range("H6").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(6).Text
            Worksheets("Weather").Range("H7").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(6).Text & "%"
            Worksheets("Weather").Range("H8").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(6).Text & "%"
            Worksheets("Weather").Range("H9").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text
        On Error GoTo 0
        
End Sub

Public Sub getWeatherNorth()
'Call mxlhttp library service and create DOM document
    Dim xmlhttp As New MSXML2.ServerXMLHTTP, myURL As String, xmlresponse As New DOMDocument

' Generate a random, six-digit number...
    Dim intRand As Long
    intRand = Int((900000) * Rnd) + 100000

' Add the number as a param to the request...
    Dim strUrl As String
    strUrl = "http://api.openweathermap.org/data/2.5/forecast/daily?q=dallas&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah=" & intRand
    
    xmlhttp.Open "GET", strUrl, False
    xmlhttp.send
    xmlresponse.LoadXML (xmlhttp.responseText)
    Worksheets("Weather").Range("B11:H19").Value = "0.00"
    
        On Error Resume Next
            Worksheets("Weather").Range("B11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(0).Text
            Worksheets("Weather").Range("B12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(0).Text)
            Worksheets("Weather").Range("B13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(0).Text
            Worksheets("Weather").Range("B14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(0).Text
            Worksheets("Weather").Range("B15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(0).Text
            Worksheets("Weather").Range("B16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(0).Text
            Worksheets("Weather").Range("B17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(0).Text
            Worksheets("Weather").Range("B18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(0).Text
            Worksheets("Weather").Range("B19").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(0).Text
            
            Worksheets("Weather").Range("C11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(1).Text
            Worksheets("Weather").Range("C12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(1).Text)
            Worksheets("Weather").Range("C13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(1).Text
            Worksheets("Weather").Range("C14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(1).Text
            Worksheets("Weather").Range("C15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(1).Text
            Worksheets("Weather").Range("C16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(1).Text
            Worksheets("Weather").Range("C17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(1).Text & "%"
            Worksheets("Weather").Range("C18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(1).Text & "%"
            Worksheets("Weather").Range("C19").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(1).Text
            
            Worksheets("Weather").Range("D11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text
            Worksheets("Weather").Range("D12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(2).Text)
            Worksheets("Weather").Range("D13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(2).Text
            Worksheets("Weather").Range("D14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(2).Text
            Worksheets("Weather").Range("D15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(2).Text
            Worksheets("Weather").Range("D16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(2).Text
            Worksheets("Weather").Range("D17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(2).Text & "%"
            Worksheets("Weather").Range("D18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(2).Text & "%"
            Worksheets("Weather").Range("D19").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(2).Text
            
            Worksheets("Weather").Range("E11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(3).Text
            Worksheets("Weather").Range("E12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(3).Text)
            Worksheets("Weather").Range("E13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(3).Text
            Worksheets("Weather").Range("E14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(3).Text
            Worksheets("Weather").Range("E15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(3).Text
            Worksheets("Weather").Range("E16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(3).Text
            Worksheets("Weather").Range("E17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(3).Text & "%"
            Worksheets("Weather").Range("E18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(3).Text & "%"
            Worksheets("Weather").Range("E19").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(3).Text
            
            Worksheets("Weather").Range("F11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(4).Text
            Worksheets("Weather").Range("F12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(4).Text)
            Worksheets("Weather").Range("F13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(4).Text
            Worksheets("Weather").Range("F14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(4).Text
            Worksheets("Weather").Range("F15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(4).Text
            Worksheets("Weather").Range("F16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(4).Text
            Worksheets("Weather").Range("F17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(4).Text & "%"
            Worksheets("Weather").Range("F18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(4).Text & "%"
            Worksheets("Weather").Range("F19").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(4).Text
            
            Worksheets("Weather").Range("G11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(5).Text
            Worksheets("Weather").Range("G12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(5).Text)
            Worksheets("Weather").Range("G13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(5).Text
            Worksheets("Weather").Range("G14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(5).Text
            Worksheets("Weather").Range("G15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(5).Text
            Worksheets("Weather").Range("G16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(5).Text
            Worksheets("Weather").Range("G17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(5).Text & "%"
            Worksheets("Weather").Range("G18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(5).Text & "%"
            Worksheets("Weather").Range("G19").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(5).Text
            
            Worksheets("Weather").Range("H11").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(6).Text
            Worksheets("Weather").Range("H12").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(6).Text)
            Worksheets("Weather").Range("H13").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(6).Text
            Worksheets("Weather").Range("H14").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(6).Text
            Worksheets("Weather").Range("H15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(6).Text
            Worksheets("Weather").Range("H15").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(6).Text
            Worksheets("Weather").Range("H16").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(6).Text & "%"
            Worksheets("Weather").Range("H17").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(6).Text & "%"
            Worksheets("Weather").Range("H18").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text
        On Error GoTo 0

End Sub

Public Sub getWeatherSouth()
'Call mxlhttp library service and create DOM document
    Dim xmlhttp As New MSXML2.ServerXMLHTTP, myURL As String, xmlresponse As New DOMDocument

' Generate a random, six-digit number..
    Dim intRand As Long
    intRand = Int((900000) * Rnd) + 100000

' Add the number as a param to the request...
    Dim strUrl As String
    strUrl = "http://api.openweathermap.org/data/2.5/forecast/daily?q=san+angelo&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah=" & intRand
    
    xmlhttp.Open "GET", strUrl, False
    xmlhttp.send
    xmlresponse.LoadXML (xmlhttp.responseText)
    Worksheets("Weather").Range("B21:H29").Value = "0.00"

        On Error Resume Next
            Worksheets("Weather").Range("B21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(0).Text
            Worksheets("Weather").Range("B22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(0).Text)
            Worksheets("Weather").Range("B23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(0).Text
            Worksheets("Weather").Range("B24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(0).Text
            Worksheets("Weather").Range("B25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(0).Text
            Worksheets("Weather").Range("B26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(0).Text
            Worksheets("Weather").Range("B27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(0).Text
            Worksheets("Weather").Range("B28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(0).Text
            Worksheets("Weather").Range("B29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(0).Text
            
            Worksheets("Weather").Range("C21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(1).Text
            Worksheets("Weather").Range("C22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(1).Text)
            Worksheets("Weather").Range("C23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(1).Text
            Worksheets("Weather").Range("C24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(1).Text
            Worksheets("Weather").Range("C25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(1).Text
            Worksheets("Weather").Range("C26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(1).Text
            Worksheets("Weather").Range("C27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(1).Text & "%"
            Worksheets("Weather").Range("C28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(1).Text & "%"
            Worksheets("Weather").Range("C29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(1).Text
        
            Worksheets("Weather").Range("D21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text
            Worksheets("Weather").Range("D22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(2).Text)
            Worksheets("Weather").Range("D23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(2).Text
            Worksheets("Weather").Range("D24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(2).Text
            Worksheets("Weather").Range("D25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(2).Text
            Worksheets("Weather").Range("D26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(2).Text
            Worksheets("Weather").Range("D27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(2).Text & "%"
            Worksheets("Weather").Range("D28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(2).Text & "%"
            Worksheets("Weather").Range("D29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(2).Text
        
            Worksheets("Weather").Range("E21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(3).Text
            Worksheets("Weather").Range("E22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(3).Text)
            Worksheets("Weather").Range("E23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(3).Text
            Worksheets("Weather").Range("E24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(3).Text
            Worksheets("Weather").Range("E25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(3).Text
            Worksheets("Weather").Range("E26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(3).Text
            Worksheets("Weather").Range("E27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(3).Text & "%"
            Worksheets("Weather").Range("E28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(3).Text & "%"
            Worksheets("Weather").Range("E29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(3).Text
        
            Worksheets("Weather").Range("F21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(4).Text
            Worksheets("Weather").Range("F22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(4).Text)
            Worksheets("Weather").Range("F23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(4).Text
            Worksheets("Weather").Range("F24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(4).Text
            Worksheets("Weather").Range("F25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(4).Text
            Worksheets("Weather").Range("F26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(4).Text
            Worksheets("Weather").Range("F27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(4).Text & "%"
            Worksheets("Weather").Range("F28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(4).Text & "%"
            Worksheets("Weather").Range("F29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(4).Text
        
            Worksheets("Weather").Range("G21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(5).Text
            Worksheets("Weather").Range("G22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(5).Text)
            Worksheets("Weather").Range("G23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(5).Text
            Worksheets("Weather").Range("G24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(5).Text
            Worksheets("Weather").Range("G25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(5).Text
            Worksheets("Weather").Range("G26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(5).Text
            Worksheets("Weather").Range("G27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(5).Text & "%"
            Worksheets("Weather").Range("G28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(5).Text & "%"
            Worksheets("Weather").Range("G29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(5).Text
        
            Worksheets("Weather").Range("H21").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(6).Text
            Worksheets("Weather").Range("H22").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(6).Text)
            Worksheets("Weather").Range("H23").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(6).Text
            Worksheets("Weather").Range("H24").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(6).Text
            Worksheets("Weather").Range("H25").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(6).Text
            Worksheets("Weather").Range("H26").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(6).Text
            Worksheets("Weather").Range("H27").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(6).Text & "%"
            Worksheets("Weather").Range("H28").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(6).Text & "%"
            Worksheets("Weather").Range("H29").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text
        On Error GoTo 0
        
End Sub
Public Sub getWeatherWest()
'Call mxlhttp library service and create DOM document
    Dim xmlhttp As New MSXML2.ServerXMLHTTP, myURL As String, xmlresponse As New DOMDocument

' Generate a random, six-digit number...
    Dim intRand As Long
    intRand = Int((900000) * Rnd) + 100000

' Add the number as a param to the request...
    Dim strUrl As String
    strUrl = "http://api.openweathermap.org/data/2.5/forecast/daily?q=midland&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah=" & intRand
    
    xmlhttp.Open "GET", strUrl, False
    xmlhttp.send
    xmlresponse.LoadXML (xmlhttp.responseText)
    Worksheets("Weather").Range("B31:H39").Value = "0.00"
            
        On Error Resume Next
            Worksheets("Weather").Range("B31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(0).Text
            Worksheets("Weather").Range("B32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(0).Text)
            Worksheets("Weather").Range("B33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(0).Text
            Worksheets("Weather").Range("B34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(0).Text
            Worksheets("Weather").Range("B35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(0).Text
            Worksheets("Weather").Range("B36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(0).Text
            Worksheets("Weather").Range("B37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(0).Text
            Worksheets("Weather").Range("B38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(0).Text
            Worksheets("Weather").Range("B39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(0).Text
            
            Worksheets("Weather").Range("C31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(1).Text
            Worksheets("Weather").Range("C32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(1).Text)
            Worksheets("Weather").Range("C33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(1).Text
            Worksheets("Weather").Range("C34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(1).Text
            Worksheets("Weather").Range("C35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(1).Text
            Worksheets("Weather").Range("C36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(1).Text
            Worksheets("Weather").Range("C37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(1).Text & "%"
            Worksheets("Weather").Range("C38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(1).Text & "%"
            Worksheets("Weather").Range("C39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(1).Text
            
            Worksheets("Weather").Range("D31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(2).Text
            Worksheets("Weather").Range("D32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(2).Text)
            Worksheets("Weather").Range("D33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(2).Text
            Worksheets("Weather").Range("D34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(2).Text
            Worksheets("Weather").Range("D35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(2).Text
            Worksheets("Weather").Range("D36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(2).Text
            Worksheets("Weather").Range("D37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(2).Text & "%"
            Worksheets("Weather").Range("D38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(2).Text & "%"
            Worksheets("Weather").Range("D39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(2).Text
            
            Worksheets("Weather").Range("E31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(3).Text
            Worksheets("Weather").Range("E32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(3).Text)
            Worksheets("Weather").Range("E33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(3).Text
            Worksheets("Weather").Range("E34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(3).Text
            Worksheets("Weather").Range("E35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(3).Text
            Worksheets("Weather").Range("E36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(3).Text
            Worksheets("Weather").Range("E37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(3).Text & "%"
            Worksheets("Weather").Range("E38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(3).Text & "%"
            Worksheets("Weather").Range("E39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(3).Text
            
            Worksheets("Weather").Range("F31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(4).Text
            Worksheets("Weather").Range("F32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(4).Text)
            Worksheets("Weather").Range("F33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(4).Text
            Worksheets("Weather").Range("F34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(4).Text
            Worksheets("Weather").Range("F35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(4).Text
            Worksheets("Weather").Range("F36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(4).Text
            Worksheets("Weather").Range("F37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(4).Text & "%"
            Worksheets("Weather").Range("F38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(4).Text & "%"
            Worksheets("Weather").Range("F39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(4).Text
            
            Worksheets("Weather").Range("G31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(5).Text
            Worksheets("Weather").Range("G32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(5).Text)
            Worksheets("Weather").Range("G33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(5).Text
            Worksheets("Weather").Range("G34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(5).Text
            Worksheets("Weather").Range("G35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(5).Text
            Worksheets("Weather").Range("G36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(5).Text
            Worksheets("Weather").Range("G37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(5).Text & "%"
            Worksheets("Weather").Range("G38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(5).Text & "%"
            Worksheets("Weather").Range("G39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(5).Text
            
            Worksheets("Weather").Range("H31").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/@day")(6).Text
            Worksheets("Weather").Range("H32").Value = UCase(xmlresponse.SelectNodes("//weatherdata/forecast/time/symbol/@name")(6).Text)
            Worksheets("Weather").Range("H33").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@max")(6).Text
            Worksheets("Weather").Range("H34").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/temperature/@min")(6).Text
            Worksheets("Weather").Range("H35").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@probability")(6).Text
            Worksheets("Weather").Range("H36").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/precipitation/@value")(6).Text
            Worksheets("Weather").Range("H37").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/humidity/@value")(6).Text & "%"
            Worksheets("Weather").Range("H38").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/clouds/@all")(6).Text & "%"
            Worksheets("Weather").Range("H39").Value = xmlresponse.SelectNodes("//weatherdata/forecast/time/windSpeed/@mps")(6).Text
            
        On Error GoTo 0

End Sub
 
Upvote 0
You have to examine nodes for each date, or you will get messed information.
My proposal is the following:
a) you move your table one coloumn right
b) in column A you write the url = the location to analyze.
You do this for each of the locations, so (going to your data) you write
Code: 
A1= http://api.openweathermap.org/data/2.5/forecast/daily?q=houston&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah="
A11= http://api.openweathermap.org/data/2.5/forecast/daily?q=dallas&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah="
A21= http://api.openweathermap.org/data/2.5/forecast/daily?q=san+angelo&cnt=7&appid=4a2360d14bf33378079d2e2d49e35ddb&mode=xml&units=imperial&q=" & "&blah="
 Etc etc
c) write the Row headers in B2:B9 and on the additional blocks you defined (one block for each Url you set in column A)

Now use the following macro, in replacement of your several Sub getWeatherXyxXyz:
Code: 
Public Sub getWeatherAll()
'Call mxlhttp library service and create DOM document
'Remember: require that a reference to the Microsoft XML library be set, via Menu /Tool /Reference
Dim xmlhttp As New MSXML2.ServerXMLHTTP, myURL As String, xmlresponse As New DOMDocument
Dim strUrl As String
Dim nodArr, nodLab, JJ As Long, I As Long, K As Long, myXML As Object
Dim intRand As Long
'Loop through the set url
For JJ = 1 To Worksheets("Weather").Cells(Rows.Count, "A").End(xlUp).Row
    If Cells(JJ, 1) <> "" Then
    ' Generate a random, six-digit number..
        intRand = Int((900000) * Rnd) + 100000
        strUrl = Worksheets("Weather").Cells(JJ, 1).Value & intRand
    'Create Request
        xmlhttp.Open "GET", strUrl, False
        xmlhttp.send
    'Read response
        xmlresponse.LoadXML (xmlhttp.responseText)
    'Initialize area:
        Worksheets("Weather").Cells(JJ, 3).Resize(9, 7).Value = "'0.00"
    'Analyze response
        'Write Location
        Worksheets("Weather").Cells(JJ, "B").Value = xmlresponse.SelectSingleNode("//weatherdata/location/name").Text
        'Get Time Nodes
        Set myXML = xmlresponse.SelectNodes("//weatherdata/forecast/time")
        'Get single Node
        On Error Resume Next
        For I = 0 To myXML.Length - 1
            Worksheets("Weather").Cells(JJ, 3 + I).Value = myXML(I).SelectSingleNode("@day").Text
            Worksheets("Weather").Cells(JJ + 1, 3 + I).Value = UCase(myXML(I).SelectSingleNode("symbol/@name").Text)
            Worksheets("Weather").Cells(JJ + 2, 3 + I).Value = myXML(I).SelectSingleNode("temperature/@max").Text
            Worksheets("Weather").Cells(JJ + 3, 3 + I).Value = myXML(I).SelectSingleNode("temperature/@min").Text
            Worksheets("Weather").Cells(JJ + 4, 3 + I).Value = myXML(I).SelectSingleNode("precipitation/@probability").Text
            Worksheets("Weather").Cells(JJ + 5, 3 + I).Value = myXML(I).SelectSingleNode("precipitation/@value").Text
            Worksheets("Weather").Cells(JJ + 6, 3 + I).Value = myXML(I).SelectSingleNode("humidity/@value").Text
            Worksheets("Weather").Cells(JJ + 7, 3 + I).Value = myXML(I).SelectSingleNode("clouds/@all").Text
            Worksheets("Weather").Cells(JJ + 8, 3 + I).Value = myXML(I).SelectSingleNode("windSpeed/@mps").Text
        Next I
        On Error GoTo 0
    End If
'Next location
Next JJ
MsgBox ("Import Completed")
End Sub

This will read each of the Url in ccolumn A and import, in the adjacent columns, from C to I, the wether parametres.
The first time you run the Macro, format the cells as you wish and save the file

Hope this fits your needs

Bye
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
Help! VBA to loop through a list containing data references to update forecast sheets automatically
Replies
3
Views
122
Domenic
D
D
  • Question
VBA code works when called directly, but not from another routhine
Replies
7
Views
708
davidepstein22
D
E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
2K
profray
P
W
  • Question
Personnel Roster using Macro's
Replies
0
Views
178
womeclr
W
Agnarr
  • Question
VBA problem with creating new sheets
Replies
0
Views
348
Agnarr
Agnarr

Forum statistics

Threads
1,214,988
Messages
6,122,620
Members
449,092
Latest member
amyap

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top