从单个JSON请求解析多个单元格和值 [英] Parse multiple cells and values from a single JSON-request

问题描述

我想显示来自JSON请求的以下变量；分别在以下B，C，D，E，F，G和H列中显示时间"，打开"，高"，低"，关闭"，"volumefrom"，"volumeto".

I would like to display the following variables from a JSON-request; "time", "open", "high", "low", "close", "volumefrom", "volumeto" in respectively the following columns B, C, D, E, F, G and H.

请求: https: //min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG

因此，我想例如查看C2:C51中的"open"值.

So, I would like to see for example the values of "open" located in C2:C51.

我写了以下宏:

Sub OHLCdata()                                                            
Dim strURL As String                                                      
Dim strJSON As String                                                     
Dim strCurrency As String                                                 
Dim strLength As Integer                                                  
Dim i As Integer                                  
Dim http As Object                                                     

strURL = "https://min-api.cryptocompare.com/data/histoday?fsym=" & strTicker & "&tsym=" & strCurrency & "&limit=" & strLength & "&aggregate=3&e=CCCAGG" 
strTicker = Range("A2")
strCurrency = Range("A3")                                           
strLength = Range("A4")                                                   
Set http = CreateObject("MSXML2.XMLHTTP")                           
http.Open "GET", strURL, False                                      
http.Send                                                             
strJSON = http.responsetext                                               
Set JSON = JsonConverter.ParseJson(strJSON)                                 
i = 2                                                                     

For Each Item In JSON("DATA")
Sheets(1).Cells(i, 1).Value = Item("time")
Sheets(1).Cells(i, 2).Value = Item("open")
Sheets(1).Cells(i, 3).Value = Item("high")
Sheets(1).Cells(i, 4).Value = Item("low")
Sheets(1).Cells(i, 5).Value = Item("close")
Sheets(1).Cells(i, 6).Value = Item("volumefrom")
Sheets(1).Cells(i, 7).Value = Item("volumeto")                              
i = i + 1                                                                
Next                                                                      
End Sub

不幸的是，该宏无法正常工作，因为调试显示以下行中存在错误:

Unfortunately, the macro doesn't work as debugging shows that there is an error in the following line:

For Each Item In JSON("DATA")

但是，我需要引用(数据")吗?

However, I need to refer to ("Data") right?

{"Response":"Success","Type":100,"Aggregated":true,**"Data"**:[{"time":1493769600,"close":1507.77,"high":1609.84,"low":1424.05,"open":1445.93,"volumefrom":338807.89999999997,"volumeto":523652428.9200001},

有人可以向我解释我做错了什么吗?预先感谢，

Can anyone explain to me what I am doing wrong? Thanks in advance,

推荐答案

有人可以向我解释我在做什么错吗?

您很亲密:

1. 我怀疑您可能是在JSON解析器上进行了复制/粘贴，而不是下载*.bas文件并导入了它.如果您复制了文件然后将其粘贴到模块中，则会看到Attribute VB_Name = "JsonConverter"行，尽管在.bas文件中是合法的，但它不在模块中，因此*编译错误:过程内部无效". *错误消息.
2. 在定义包含的变量之前，先创建strURL.因此变量将为空
3. 当您写结果时，您的列号是关闭的，因此它将在A列而不是B列开始.
4. 您无法声明某些变量.
5. 由于JSON是字典类型的对象，因此密钥将区分大小写(除非您声明它是其他类型).因此，DATA和Data是两个不同的键.您需要使用Data.

1. I suspect you probably did a copy/paste on the JSON parser rather than downloading the *.bas file and importing it. If you copied the file and then pasted it into a module, you would see the line Attribute VB_Name = "JsonConverter" Although legal in the .bas file, it is not in a module, hence the *"compile error: invalid inside procedure." * error message.
2. You create strURL before you define the variables that are included. Therefore the variables will be blank
3. Your column numbers are off when you write the results, so it will start in column A instead of B.
4. You fail to declare some of your variables.
5. Since JSON is a dictionary type object, the key will be case sensitive (unless you declare it to be otherwise). Hence DATA and Data are two different keys. You need to use Data.

这是包含更改的代码；并且不要忘记导入.bas文件，也不要复制/粘贴.

Here is your code with the changes; and don't forget to import the .bas file and don't copy/paste.

Option Explicit
Sub OHLCdata()
Dim strURL As String
Dim strJSON As String
Dim strCurrency As String
Dim strLength As Integer
Dim strTicker As String
Dim i As Integer
Dim http As Object

Dim JSON As Dictionary, Item As Dictionary


strTicker = Range("A2")
strCurrency = Range("A3")
strLength = Range("A4")

strURL = "https://min-api.cryptocompare.com/data/histoday?fsym=" & strTicker & "&tsym=" & strCurrency & "&limit=" & strLength & "&aggregate=3&e=CCCAGG"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strJSON = http.responsetext
Set JSON = JsonConverter.ParseJson(strJSON)
i = 2

For Each Item In JSON("Data")
Sheets(1).Cells(i, 2).Value = Item("time")
Sheets(1).Cells(i, 3).Value = Item("open")
Sheets(1).Cells(i, 4).Value = Item("high")
Sheets(1).Cells(i, 5).Value = Item("low")
Sheets(1).Cells(i, 6).Value = Item("close")
Sheets(1).Cells(i, 7).Value = Item("volumefrom")
Sheets(1).Cells(i, 8).Value = Item("volumeto")
i = i + 1
Next
End Sub

注意:关于在bas文件中可见的Attribute行，如果在文本编辑器中将其打开，则可以参考Chip Pearson在

Note: With regard to the Attribute line visible in the bas file if you open it in a text editor, you may refer to Chip Pearson's article on Code Attributes For The VBA Object Browser. It is generally considered bad form to refer to an external link, as they may disappear. However, I could not find a good discussion here on SO. If I have missed it, someone please comment and I will edit this.

这篇关于从单个JSON请求解析多个单元格和值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！