Unable to extract the JSON filed values in Excel

[knitin28289]

Hi,
This below code is helpful to get the data from json. but I am getting error at line "Set MyList = RetVal.Data" . The error is 'Object doesn't support this property or method'.

Note : I am able to extract all data from URL into object Retval when I can see all the details in Watches.

Please help me in this. Do I need to add any references for this or need to create a specific object for this.

Code :
Sub Test5()
Dim objHTTP As Object
Dim MyScript As Object
Dim i As Long
Dim myData As Object
Set MyScript = CreateObject("MSScriptControl.ScriptControl")
MyScript.Language = "JScript"


URL = "https://min-api.cryptocompare.com/data/histominute?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG"

Set objHTTP = CreateObject("MSXML2.XMLHTTP")
objHTTP.Open "GET", URL, False
objHTTP.send

Set RetVal = MyScript.Eval("(" + objHTTP.responsetext + ")")
objHTTP.abort

i = 2

Set MyList = RetVal.Data

x = 0
For Each myData In MyList
x = x + 1
If x = 1 Then 'Get values from the second data
Cells(i, 1).Value = myData.volumefrom
Cells(i, 2).Value = myData.volumeto
i = i + 1
Exit For
End If
Next

Set MyList = Nothing
Set objHTTP = Nothing
Set MyScript = Nothing
End Sub

 

[John_w]

Your code works for me. No references are needed.

This below code is helpful to get the data from json. but I am getting error at line "Set MyList = RetVal.Data" . The error is 'Object doesn't support this property or method'.

The first item in the JSON response string is "Response":"Success" and the response contains a "Data" array containing all the data items. I'm guessing that if "Response" isn't "Success" the Data property is empty or undefined, hence the error you're getting.

Here is your code modified to check whether "Response" = "Success" and if so output the values of the 2nd data item, otherwise it displays a message.

Sub Test2()
    Dim objHTTP As Object
    Dim MyScript As Object
    Dim myData As Object
    Dim URL, RetVal, MyList
    
    Set MyScript = CreateObject("MSScriptControl.ScriptControl")
    MyScript.Language = "JScript"
    
    URL = "https://min-api.cryptocompare.com/data/histominute?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG"
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", URL, False
    objHTTP.send
    Debug.Print objHTTP.responsetext
    Set RetVal = MyScript.Eval("(" + objHTTP.responsetext + ")")
    objHTTP.abort
    
    If RetVal.Response = "Success" Then
        Set MyList = RetVal.Data
        Set myData = VBA.CallByName(MyList, "1", VbGet) 'get 2nd Data item
        Cells(2, 1).Value = myData.volumefrom
        Cells(2, 2).Value = myData.volumeto
    Else
        MsgBox "JSON Response value = " & RetVal.Response
    End If
    
    Set MyList = Nothing
    Set objHTTP = Nothing
    Set MyScript = Nothing
End Sub

PS please use CODE tags for VBA code - click the # icon in the message editor.