vba解析json - vba爬蟲




從單個JSON請求中解析多個單元格和值 (2)

您可以將JSON數據放入數組並輸出,如下面的示例代碼所示。 JSON.bas 模塊導入VBA項目以進行JSON處理。

Option Explicit

Sub OHLCdata()

    Dim sJSONString As String
    Dim vJSON As Variant
    Dim sState As String
    Dim aData()
    Dim aHeader()

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://min-api.cryptocompare.com/data/histoday?fsym=BTC&tsym=USD&limit=60&aggregate=3&e=CCCAGG", False
        .send
        sJSONString = .responseText
    End With
    JSON.Parse sJSONString, vJSON, sState
    vJSON = vJSON("Data")
    JSON.ToArray vJSON, aData, aHeader
    With Sheets(1)
        .Cells.Delete
        .Cells.WrapText = False
        OutputArray .Cells(1, 1), aHeader
        Output2DArray .Cells(2, 1), aData
        .Columns.AutoFit
    End With

End Sub

Sub OutputArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize(1, UBound(aCells) - LBound(aCells) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

Sub Output2DArray(oDstRng As Range, aCells As Variant)

    With oDstRng
        .Parent.Select
        With .Resize( _
                UBound(aCells, 1) - LBound(aCells, 1) + 1, _
                UBound(aCells, 2) - LBound(aCells, 2) + 1)
            .NumberFormat = "@"
            .Value = aCells
        End With
    End With

End Sub

這是我的輸出:

我想從JSON請求中顯示以下變量; 分別在以下列B,C,D,E,F,G和H中的“時間”,“打開”,“高”,“低”,“關閉”,“體積從”,“體積到”。

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

所以,我想看看例如C2中的“open”值: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

不幸的是,宏不起作用,因為調試顯示以下行中存在錯誤:

For Each Item In JSON("DATA")

但是,我需要參考(“數據”)對嗎?

{"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},

誰能向我解釋我做錯了什麼? 提前致謝,


誰能向我解釋我做錯了什麼?

你很近:

  1. 我懷疑你可能在JSON解析器上進行了複製/粘貼,而不是下載 *.bas 文件並導入它。 如果您複製了文件然後將其粘貼到模塊中,您會看到行 Attribute VB_Name = "JsonConverter" 雖然在 .bas 文件中是合法的,但它不在模塊中,因此*“編譯錯誤:內部程序無效。 “ * 錯誤信息。
  2. 在定義包含的變量之前,先創建 strURL 。 因此變量將為空白
  3. 編寫結果時,您的列號已關閉,因此它將從A列而不是B列開始。
  4. 你沒有聲明你的一些變量。
  5. 由於JSON是字典類型對象,因此密鑰將區分大小寫(除非您將其聲明為其他情況)。 因此 DATAData 是兩個不同的鍵。 您需要使用 Data

這是你的代碼與變化; 並且不要忘記導入.bas文件,不要復制/粘貼。

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 關於VBA對象瀏覽器的代碼屬性 的文章。 引用外部鏈接通常被認為是不好的形式,因為它們可能會消失。 但是,我在這裡找不到一個很好的討論。 如果我錯過了,有人請評論,我會編輯這個。







vba