VBA-在Excel中显示的API调用 [英] VBA - API call displayed in Excel

问题描述

我正在尝试在Excel工作表中显示特定加密货币的价格.我正在从CoinMarketCap的API中提取JSON数据- https://api.coinmarketcap.com/v1/ticker/

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/

最终，我试图获取Ripple的价格(第16行)，然后在Excel工作表中设置单元格B1以显示波纹的价格(第17行).

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

API调用成功(我相信)，但是出现语法错误等.希望任何人都可以提供帮助.预先感谢

The API call is successful (I believe), but I get syntax errors etc. Hope anybody is able to help. Thanks in advance

这是Microsoft Excel 2010

This is Microsoft Excel 2010

是问题所在的第16行和第17行(分别是If oJSON.Name...和B1 = oJSON(...，但到目前为止，我一直无法解决/找到错误.请参阅有关运行时错误等的注释.

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.

我相信我在第16和17行中通过引用oJSON而不是项目(sItem)犯了一个错误.但是，即使更改了此内容(例如If sItem.Name = "Ripple" Then...)，它仍然无法正常工作.

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.

我相信我也以错误的方式标记了excel-cell.现在，我不再只是编写B1 = ...，而是编写在测试中工作的Range.("B1").Value = ....

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.

推荐答案

@omegastripes建议的此修改在这里有效. json对象是词典的集合，因此您需要这样处理.

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

这篇关于VBA-在Excel中显示的API调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！