JSON VBA parsing problem

[dawds]

So I'm trying to scrape historic score data from masseyratings.com, and then parse it using a JSON VBA parser. I'm running into issues defining the JSON fields to pull out, I get a runtime error 13 type mismatch on the underlined line of code below. The JSON data for this query looks like this Online JSON Viewer . I want to pull third level information from DI. I have a coworker who scrapes the site using python and his syntax for JSON fields was fairly similar to what I'm using, I just can't get mine to work. Any help would be appreciated. My code currently is: (you can ignore the if statement for colOut, I haven't determined in what way I want to loop/paste the data yet)

Sub json_parsed_massey_query()
    Dim wbk As Workbook, wksht As Worksheet
    Dim http As Object, JSON As Object
    Dim teamID As String, teamSzn As String
    Set wbk = Workbooks("massey_scores_scraper")
    Set wksht = wbk.Sheets("Scraper_Inputs")
    Set http = CreateObject("MSXML2.XMLHTTP") 'or ("winhttp.winhttprequest.5.1")?
    lastrow0 = Cells(rows.Count, 3).End(xlUp).Row
    i = 2
    x = 0
    colOut = 5


    wksht.Activate
    For Each rng In wksht.Range("c2:c" & lastrow0)
        teamID = rng.Value
        teamSzn = Range("D2").Value
        http.Open "get", "http://www.masseyratings.com/teamjson.php?t=" & teamID & "&s=" & teamSzn & "", False
        http.Send
        Set JSON = ParseJson(http.ResponseText)


        For Each Item In JSON
            ActiveSheet.Cells(i, colOut).Value = Item("DI")("x")("0")
            ActiveSheet.Cells(i, colOut).Value = Item("DI")("x")("1")
            ActiveSheet.Cells(i, colOut).Value = Item("DI")("x")("2")
            ActiveSheet.Cells(i, colOut).Value = Item("DI")("x")("3")("0")
            ActiveSheet.Cells(i, colOut).Value = Item("DI")("x")("3")("4")
            i = i + 1
            x = x + 1
            If colOut = 14 Then
                colOut = 5
            Else: colOut = colOut + 1
            End If
        Next Item
    Next rng


End Sub

 

[dawds]

I figured it out.

 

[dawds]

I spoke a little soon, and since I couldn't find an edit button I'm posting again. My code parses perfectly for the first subset of "DI", but upon looping to next "rng" I get the error "Run-time error '10001': ^ Expecting '{' or '['". I can't figure out what's causing it, everything steps out perfectly until it tries to parse the next JSON query. I even tried adding "JSON is Nothing" before it loops to next rng with no luck. Here's my code as it stands, and if you'd like the code in the parser that errors out please let me know. Thanks to anyone who can help.

 Sub json_parsed_massey_query()    Dim wbk As Workbook, wksht As Worksheet, wkshtX As Worksheet
    Dim http As Object, JSON As Object
    Dim teamID As String, teamSzn As String
    Set wbk = Workbooks("massey_scores_scraper")
    Set wksht = wbk.Sheets("Scraper_Inputs")
    Set http = CreateObject("MSXML2.XMLHTTP")    'or ("winhttp.winhttprequest.5.1")?
    lastrow0 = Cells(rows.Count, 3).End(xlUp).Row
    i = 2
    colOut = 3
    r = 2
    
    wksht.Activate
    For Each rng In wksht.Range("c2:c" & lastrow0)
        teamID = rng.Value
        teamSzn = Range("D2").Value
        shtName = Cells(r, 2).Value
        
        http.Open "get", "http://www.masseyratings.com/teamjson.php?t=" & teamID & "&s=" & teamSzn & "", False
        http.Send
        Set JSON = ParseJson(http.ResponseText)
        Set jsonDI = JSON("DI")
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = shtName
        Set wkshtX = wbk.Sheets(shtName)
        
        wkshtX.Activate
        Cells(1, colOut).Value = "Day"
        Cells(1, colOut + 1).Value = "Date"
        Cells(1, colOut + 2).Value = "Away"
        Cells(1, colOut + 4).Value = "Playoff/Exhib"
        Cells(1, colOut + 5).Value = "Result/Pred"
        Cells(1, colOut + 6).Value = "Home Points"
        Cells(1, colOut + 7).Value = "Away Points"
        For x = 1 To jsonDI.Count
            wkshtX.Cells(i, colOut).Value = jsonDI.Item(x).Item(1)
            wkshtX.Cells(i, colOut + 1).Value = jsonDI.Item(x).Item(2)
            wkshtX.Cells(i, colOut + 2).Value = jsonDI.Item(x).Item(3)
            wkshtX.Cells(i, colOut + 3).Value = jsonDI.Item(x).Item(4).Item(1)
            wkshtX.Cells(i, colOut + 4).Value = jsonDI.Item(x).Item(5)
            wkshtX.Cells(i, colOut + 5).Value = jsonDI.Item(x).Item(8).Item(1)
            wkshtX.Cells(i, colOut + 6).Value = jsonDI.Item(x).Item(10)
            wkshtX.Cells(i, colOut + 7).Value = jsonDI.Item(x).Item(11)
            If wkshtX.Cells(i, 5).Value = "" Then
            wkshtX.Cells(i, 5).Value = wkshtX.Cells(i, 6)
            ElseIf ActiveSheet.Cells(i, 5).Value = "at" Then
            wkshtX.Cells(i, 5).Value = wkshtX.Cells(i, 5).Value & " " & wkshtX.Cells(i, 6).Value
            End If
            i = i + 1
        Next x
        Columns("f:f").EntireColumn.Delete Shift:=xlToLeft
        r = r + 1
    Next rng


End Sub