Unable to extract the JSON filed values in Excel

knitin28289

New Member
Joined
Dec 27, 2017
Messages
2
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
Code:
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.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,170
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top