JSON to VBA - Error 13 mismatch on "root values"

Question

I was trying to get some information from a JSON API and everything was going OK. So I started to get mismatch errors when I try to parse values that are inside the “root” of the JSON.

The code I use is below:

Public Sub Times()    
    Dim http As Object, JSON As Object, i As Integer

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "https://api.cartolafc.globo.com/time/id/1084847/7", False
    http.Send

    Set JSON = ParseJson(http.responseText)  
    i = 2

    Application.ScreenUpdating = False

    Sheets("Youtube").Select       
    For Each Item In JSON
        Sheets("Mais Escalados").Cells(i, 2).value = Item("pontos")
        i = i + 1
    Next

    Application.ScreenUpdating = True

    MsgBox ("Atualização Completa")
End Sub

I can parse the data inside atletas sub-items or any other header changing the code like this:

Sheets("Mais Escalados").Cells(i, 2).value = Item("atletas")("nome")

But when I try to parse information like pontos on the root I get the mismatch error.

Answer 1

This will give you the root value for the key "pontos":

JSON("pontos")

You can't loop over the root keys like you show in your posted code: you would need to check the type of each key's value before you try to write it to the sheet:

Public Sub Times()

    Dim http As Object, JSON As Object, i As Integer, k
    Set http = CreateObject("MSXML2.XMLHTTP")

    http.Open "GET", "https://api.cartolafc.globo.com/time/id/1084847/7", False
    http.Send
    Set JSON = ParseJson(http.responseText)

    For Each k In JSON
        Debug.Print k, TypeName(JSON(k))
    Next

End Sub

Output:

atletas       Collection
clubes        Dictionary
posicoes      Dictionary
status        Dictionary
capitao_id    Double
time          Dictionary
patrimonio    Double
esquema_id    Double
pontos        Double
valor_time    Double
rodada_atual  Double