通过VBA解析Json数组 [英] Parsing Json array via VBA

问题描述

我从api获取json响应，并将其解析为在excel中更新.下面是代码.我无法进一步解析以获取价格信息.

I am getting a json response from an api and parse it to update in excel. Below is the code. I am not able to parse further to get the price info.

Dim strResult As String
Dim objHTTP As Object
Dim URL As String
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://bitbns.com/order/getTickerAll"
objHTTP.Open "GET", URL, False
objHTTP.Send
Set JSON = JsonConverter.ParseJson(objHTTP.ResponseText)
'strResult = objHTTP.ResponseText
'MsgBox JSON(1)("BTC")("sellPrice")
baseCol = 9
buyCol = 10
sellCol = 11
i = 1
Dim keyCurr As String
For Each Item In JSON
    ActiveSheet.Cells(i + 2, baseCol).Value = Item.Keys
    i = i + 1
Next

好心的帮助.正如您在上面的评论中看到的那样，我能够以硬编码方式获取数据

Kinly help. As you could see in a comment above, I am able to get data as hard coded

MsgBox JSON(1)("BTC")("sellPrice")

但是当我尝试将其循环时，我无法.以下是我尝试过但没有用的方法.

But when I try getting that in loop, I am unable to. Below are the ones I tried but did not work.

ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(Item.Keys)("sellPrice") 
ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(" + Item.Keys + ")("sellPrice")
ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(Item(0))("sellPrice")
ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(Item(1))("sellPrice")

对于解析JSON，我使用 vbaJSON 库.它似乎返回了正确的对象(可以看到能够访问硬编码方式，但是不能循环访问)

For parsing JSON, I use vbaJSON library. It seem to return proper object (as could see am able to access hard coded way, but could not access in loop)

更新:根据Vityata的提示，以下代码似乎工作正常.谢谢大家提供的即时帮助. :)

Update: As per Vityata's hint, below code seem to be working fine. Thank you all for such immediate help. :)

For Each Item In JSON
    ActiveSheet.Cells(i + 2, baseCol).Value = Item.Keys
    For Each curr In Item
        ActiveSheet.Cells(i + 2, buyCol).Value = JSON(i)(curr)("buyPrice")
        ActiveSheet.Cells(i + 2, sellCol).Value = JSON(i)(curr)("sellPrice")
        i = i + 1
    Next curr
Next Item

推荐答案

如果您对"sellPrice"进行了硬编码，则可以想到以下内容:

It you hard-code the "sellPrice", you can come up with something like this:

Dim something, someItem, cnt&
For Each something In JSON
    For Each someItem In something
        cnt = cnt + 1
        Debug.Print someItem
        Debug.Print JSON(cnt)(someItem)("sellPrice")
    Next someItem
Next something

在立即窗口中:

BTC
 623900 
XRP
 70,35 
NEO
 7699,5 
GAS
 2848,97 
ETH
 59500 
XLM
 28,38 

键和项是集合，可以通过以下方式循环访问:

The keys and the items are collection, which can be looped through:

Dim something, someItem, cnt&, obj, iO
For Each something In JSON
    For Each someItem In something
        cnt = cnt + 1
        Debug.Print someItem
        Set obj = JSON(cnt)(someItem)
        For Each iO In obj.Keys
            Debug.Print iO
            Debug.Print obj.item(iO)
        Next iO
    Next someItem
Next something

在立即窗口中:

BTC
sellPrice
 625000 
buyPrice
 624000 
lastTradePrice
 625000 
XRP
sellPrice
 70,2 
buyPrice
 70,1 
lastTradePrice
 70,2 

这篇关于通过VBA解析Json数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！