json - VBA 解析 JSON 数据

Question

我正在尝试解析来自网站的 JSON 数据。我成功获取了 JSON 字符串，但我无法解析它。下面的代码中抛出了异常:

Runtime Error 424. Object Required

这是我的代码:

' Download string from URL
Public Function DownloadDataFromURL(url As String) As String
    Set Myrequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    Myrequest.Open "GET", url
    Myrequest.send
    Dim WebResponse As String
    WebResponse = Myrequest.responseText
    Cells(1, 1).Value = WebResponse
    DownloadDataFromURL = WebResponse
End Function

' Download all cryptocoins in
Public Sub LoadCryptocoins()
    Dim Path As String
    Dim Data As String
    Dim json As Object

    Path = "https://api.coinmarketcap.com/v1/ticker/"
    Data = DownloadDataFromURL(Path)

    Set jp = New JsonParser
    Set jo = jp.Decode(Data)

    For Each Item In jp.EnumKeys(jo)
        MsgBox (Item.GetValue(jo, "id")) 'The exception is thrown here
    Next
End Sub

我从这里使用 JSON 解析器: Parsing JSON in Excel VBA

我处理的原始 JSON 数据可以在这里找到: https://api.coinmarketcap.com/v1/ticker/

我怎样才能得到每个硬币的名称和美元的价格？

Answer 1

试试下面的脚本。它将获取您提到的所有数据。不需要外部解析器来实现这一点:

Sub coinmarketcap_data()
    Dim http As New XMLHTTP60, res As Variant

    With http
        .Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False
        .send
        res = .responseText
    End With

    For r = 1 To UBound(Split(res, "id"": """))
        Cells(r, 1) = Split(Split(Split(res, "name"": """)(r), """symbol")(0), """")(0)
        Cells(r, 2) = Split(Split(Split(res, "price_usd"": """)(r), """price_btc")(0), """")(0)
    Next r
End Sub

添加到库的引用:

Microsoft XML, v6.0