VBA - API call displayed in Excel

Question

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.

Solution 1

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

Solution 2

Take a look at the below example. Import JSON.bas module into the VBA project for JSON processing.

Option Explicit

Sub Test48852376()

    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String
    Dim vElement As Variant
    Dim sValue As String
    Dim aData()
    Dim aHeader()

    ' Retrieve JSON string
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False
        .Send
        sJSONString = .responseText
    End With
    ' Parse JSON
    JSON.Parse sJSONString, vJSON, sState
    If sState = "Error" Then MsgBox "Invalid JSON string": Exit Sub
    ' Extract ripple price_usd
    Do
        For Each vElement In vJSON
            Select Case False
                Case vElement.Exists("id")
                Case vElement("id") = "ripple"
                Case vElement.Exists("price_usd")
                Case Else
                    MsgBox "ripple price_usd " & vElement("price_usd")
                    Exit Do
            End Select
        Next
        MsgBox "ripple price_usd not found"
    Loop Until True
    ' Output the entire table to the worksheet
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With
    MsgBox "Completed"

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

The output for me as follows:

BTW, the similar approach applied in other answers.

Comments

[QHarr]

Please give more detail on the errors? What are the descriptions and on which lines do they occur?

[QHarr]

Can you examine the response or write out so you can show the relevant JSON...?

[thesystem]

@QHarr "Run-Time Error '438': Object doesn't support this property or method." Happens at line 16 and/or 17, when I try to extract the values.

[QHarr]

That being the line where you say If oJSON......?

[omegastripes]

To retrieve price_usd for ripple id you should loop through each object, find the object having ripple id and get price_usd value.

[thesystem]

@QHarr Exactly. I just implemented MsgBox httpObject.Status and ´ReadyState`, and it returns 200 and 4, so the call should be fine in itself.

[thesystem]

@omegastripes I revisited my script and made the following changes. For Each sItem In oJSON If oJSON.Name = "Ripple" Then B1 = oJSON("Ripple")("price_usd") End If Next I still receive Run Time Error 438, so that doesn't work either. EDIT: Formatting

[thesystem]

Thank you very much, highly appreciated!

[thesystem]

Thank you very much for the thorough example and also the references to earlier answers. I really appreciate the help.