VBA Macro, type mismatch error

Question

I'm trying to get JSON data in excel and parse it. However, I'm getting an error: Type Mismatch.

Does anyone know how I can resolve this? I can't figure out what I'm doing wrong.

This is the link to the API I'm using: https://min-api.cryptocompare.com/data/price?fsym=ETH&tsyms=USD

Here is the code:

Public Sub exceljson()

    Dim https As Object, JSON As Object, i As Integer

    Set https = CreateObject("MSXML2.XMLHTTP")

    https.Open "GET", "https://min-api.cryptocompare.com/data/price?fsym=ETH&tsyms=USD", False
    https.Send

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

    For Each Item In JSON
        Sheets(1).Cells(i, 1).Value = Item("USD")
        i = i + 1
    Next

    MsgBox ("complete")

End Sub

Which line is throwing the Type Mismatch? Set Breakpoint (F9) on the https.open line and then Step through with F8 key. — Gordon Bell
fyi, i just added an S to the end of this: XMLHTTP --> XMLHTTPS; now the error says: Active X component cannot create object — Yousuf

Phil Phil · Accepted Answer · 2017-12-10T14:40:54

If you confirm you have correctly imported JsonConverter module and Dictionary Class and refer to Microsoft Scripting Runtime, then the following code will work:

    Public Sub exceljson()
    Dim https As Object, Json As Object, i As Integer

    Dim Item As Variant

    Set https = CreateObject("MSXML2.XMLHTTP")
    https.Open "GET", "https://min-api.cryptocompare.com/data/price?fsym=ETH&tsyms=USD", False
    https.Send

    Set Json = JsonConverter.ParseJson(https.responseText)

    i = 2
    For Each Item In Json.Items
        Sheets(1).Cells(i, 1).Value = Item
        i = i + 1
    Next
    MsgBox ("complete")
End Sub

Hope it helps!

updated

You should analyze the Json data responsed by the sever, the value of "Data" is a Dictionary. So you should use a Dictionary in For Each Loop.

The following codes will get all the data, I just use the "high" key as example. You can easily output other keys into excel.

Public Sub exceljson()
    Dim https As Object, Json As Object, DataItem As Dictionary, i As Integer


    Set https = CreateObject("MSXML2.XMLHTTP")
    https.Open "GET", "https://min-api.cryptocompare.com/data/histominute?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG", False
    https.Send

    'Use this debug command to see the whole response text
    'Debug.Print https.responseText
    Set Json = JsonConverter.ParseJson(https.responseText)


    i = 2
    For Each DataItem In Json("Data")
        'Use "high" as example, you can output other key/values
        Sheets(1).Cells(i, 1).Value = DataItem("high")

        i = i + 1
    Next
    MsgBox ("complete")
End Sub

VBA Macro, type mismatch error

1 Answers

updated