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 以显示 Ripple 的价格(第 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

EDIT 2: 第 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.

编辑 3:我相信我在第 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.

编辑 4:我相信我也以错误的方式标记了 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屋！