VBA - API call displayed in Excel

thesystem picture thesystem · Feb 18, 2018 · Viewed 15.8k times · Source

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.

Answer

Ron Rosenfeld picture Ron Rosenfeld · Feb 18, 2018

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