I am trying to show prices of specific cryptocurrencies in an Excel sheet. I am extracting the JSON data from the API of CoinMarketCap - https://api.coinmarketcap.com/v1/ticker/
Ultimately, I am trying to get the price of Ripple (line 16), and then set cell B1 in my Excel sheet to display the price of ripple (line 17).
This is my script, but it is not working for some reason.
Sub test()
Dim httpObject As Object
Set httpObject = CreateObject("MSXML2.XMLHTTP")
sURL = "https://api.coinmarketcap.com/v1/ticker/"
sRequest = sURL
httpObject.Open "GET", sRequest, False
httpObject.Send
sGetResult = httpObject.ResponseText
Dim oJSON As Object
Set oJSON = JsonConverter.ParseJson(sGetResult)
If oJSON.Name = "Ripple" Then
B1 = oJSON("Ripple")("price_usd")
End If
End Sub
The API call is successful (I believe), but I get syntax errors etc. Hope anybody is able to help. Thanks in advance
EDIT: This is Microsoft Excel 2010
EDIT 2: It is lines 16 and 17 (respectively If oJSON.Name... and B1 = oJSON(... that poses the problem, but I have been unable to solve it/find the error as of now. See comments for Run Time Error etc.
EDIT 3: I believe I have made a mistake in lines 16 and 17 by referring to oJSON and not the item (sItem). However, even after changing this (e.g. If sItem.Name = "Ripple" Then...), it is still not working.
EDIT 4: I believe I also tagged the excel-cell in the wrong manner. Instead of simply writing B1 = ..., I am now writing Range.("B1").Value = ..., which worked in a test.
This modification suggested by @omegastripes works here. The json object is a collection of dictionaries, so you need to treat it as such.
Dim oJSON As Object
Set oJSON = JsonConverter.ParseJson(sGetResult)
Dim V As Object
For Each V In oJSON
If V("name") = "Ripple" Then
Cells(1, 2) = V("price_usd")
Exit For
End If
Next V