Excel VBA run-time error 91 can't solve

Question

I'm trying to pull data from website with XML. Look at my script below:

Script

Option Explicit

Private Sub btnRefresh_Click()
    Dim req As New XMLHTTP
    Dim resp As New DOMDocument
    Dim weather As IXMLDOMNode
    Dim ws As Worksheet: Set ws = ActiveSheet
    Dim wshape As Shape
    Dim thiscell As Range
    Dim i As Integer

    req.Open "GET", "http://api.worldweatheronline.com/premium/v1/weather.ashx?key=myConfidentialToken&q=Baku&format=xml&num_of_days=5"
    req.send

    resp.LoadXML req.responseText

    For Each weather In resp.getElementsByTagName("weather")
        i = i + 1
        ws.Range("theDate").Cells(1, i).Value = weather.SelectNodes("date")(0).Text
        ws.Range("highTemps").Cells(1, i).Value = weather.SelectNodes("maxtempC")(0).Text
        ws.Range("lowTemps").Cells(1, i).Value = weather.SelectNodes("mintempC")(0).Text
        Set thiscell = ws.Range("weatherPicture").Cells(1, i)
        Set wshape = ws.Shapes.AddShape(msoShapeRectangle, thiscell.Left, thiscell.Top, thiscell.Width, thiscell.Height)
        wshape.Fill.UserPicture weather.SelectNodes("weatherIconUrl").Item(0).Text
    Next weather

End Sub

This script returns run-time error 91. It says: "Object variable or With block variable not set". When I debug, running stops at the line before "next weather" statement. Why this script returns such an error, while I've already set my "wshape" variable?

Answer 1

As @JohnRC pointed, there are the set of <hourly> nodes inside each <weather> node, and each <hourly> node in turn contains <weatherIconUrl>. You may save the response XML to a file, and use any online XML tree viewer to look into a structure:

Take a look at the below example, there is a nested loop added to get <weatherIconUrl> from each <hourly> node:

Option Explicit

Private Sub btnRefresh_Click()

    Dim oReq As Object
    Dim sResp As String
    Dim oDoc As Object
    Dim oData As Object
    Dim cDays As Object
    Dim oDay As Object
    Dim cHours As Object
    Dim oHour As Object

    Set oReq = CreateObject("MSXML2.XMLHTTP")
    oReq.Open "GET", "http://api.worldweatheronline.com/premium/v1/weather.ashx?key=e11be04676ad49038f9175720181600&q=saint-petersburg&format=xml&num_of_days=5", False
    oReq.Send
    sResp = oReq.ResponseText
    Set oDoc = CreateObject("MSXML2.DOMDocument")
    oDoc.LoadXML sResp
    Set oData = oDoc.getElementsByTagName("data")(0)
    Set cDays = oData.SelectNodes("weather")
    For Each oDay In cDays
        Debug.Print oDay.SelectNodes("date")(0).Text
        Debug.Print oDay.SelectNodes("maxtempC")(0).Text
        Debug.Print oDay.SelectNodes("mintempC")(0).Text
        Set cHours = oDay.SelectNodes("hourly")
        For Each oHour In cHours
            Debug.Print vbTab & oHour.SelectNodes("time")(0).Text
            Debug.Print vbTab & oHour.SelectNodes("tempC")(0).Text
            Debug.Print vbTab & oHour.SelectNodes("weatherIconUrl")(0).Text
        Next
    Next

End Sub