JSON to Excel: Type Mismatch Error

  • I receive a Type Mismatch Error when I run this code. I do not understand why. Any assistance is appreciated. Thank you.

    Sub ES()

    Dim http As Object, JSON As Object, i As Integer
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", "http://www.cmegroup.com/CmeWS/mvc/Settlements/Futures/Settlements/133/FUT?strategy=DEFAULT&tradeDate=04/06/2018&pageSize=500&_=1523029287847", False
    Set JSON = ParseJson(http.responseText)
    i = 3
    For Each Item In JSON
    Sheets("Sheet1").Cells(i, 1).Value = Item("month")
    Sheets("Sheet1").Cells(i, 2).Value = Item("open")
    Sheets("Sheet1").Cells(i, 3).Value = Item("high")
    Sheets("Sheet1").Cells(i, 4).Value = Item("low")
    Sheets("Sheet1").Cells(i, 5).Value = Item("last")
    Sheets("Sheet1").Cells(i, 6).Value = Item("change")
    Sheets("Sheet1").Cells(i, 7).Value = Item("settle")
    Sheets("Sheet1").Cells(i, 8).Value = Item("volume")
    Sheets("Sheet1").Cells(i, 9).Value = Item("openInterest")
    i = i + 1
    MsgBox ("complete")
    End Sub

Log in to reply

Looks like your connection to Codingislove Forum was lost, please wait while we try to reconnect.