2

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.

Improve this question
7
  • 2
    Please give more detail on the errors? What are the descriptions and on which lines do they occur? Commented Feb 18, 2018 at 14:01
  • 1
    Can you examine the response or write out so you can show the relevant JSON...? Commented Feb 18, 2018 at 14:03
  • @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. Commented Feb 18, 2018 at 14:03
  • 1
    That being the line where you say If oJSON......? Commented Feb 18, 2018 at 14:04
  • 1
    To retrieve price_usd for ripple id you should loop through each object, find the object having ripple id and get price_usd value. Commented Feb 18, 2018 at 14:06

2 Answers 2

Reset to default
4

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:

output

BTW, the similar approach applied in other answers.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

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

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
Improve this answer

1 Comment

Thank you very much, highly appreciated!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.