VBA / 抓取網路 JSON資料 版本4

VBA / 抓取網路 JSON資料 版本4

Post Views: 1,627

繼續補充 VBA處理 Json的方式

VBA / 抓取網路 JSON資料

VBA / 抓取網路 JSON資料 版本2

VBA / 抓取網路 JSON資料 版本3

這個方式是參考〈Excel VBA 經典程式碼─一行抵萬行「偷懶程式碼」應用大全〉的範例

利用VBA引用物件htmlfile (HTML Document)

再透過parentWindow 建立window物件

之後利用execScript將Javascript程式碼寫入window物件並執行

可以透過eval函數來輸入要執行Javascript代碼字串，在windows物件內利用Javascript操作物件

使用eval函數可以避免VBA自動將屬性名稱的第一個字母改成大寫，因為在Javascript大小寫有差異

接下來改寫前一篇的程式碼

Const URl2 As String = "https://script.google.com/macros/s/AKfycbwxkgntkYF25Kk2Khdgn0bSLqxhHvDTRiRTfwJTfTuX4BQzOrpOImxccrRuTWvBZhMXHw/exec?mailID=trico109748007@gmail.com.tw"

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
 
'
'htmlfile.parentWindow
'Json資料是陣列格式
'
Public Sub test22()

    '刪除舊資料
    Sheets(2).Select
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
    
    '設定 XMLHTTP 物件
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    xmlHttp.Open "GET", URl2, False
    xmlHttp.send
    
    strJson = xmlHttp.responseText                         '回傳資料內容
'    strJson = "{" & """" & "data" & """" & ":" & strJson & "}"
'
''    Debug.Print strJson
'    Dim html, window
'    Set html = CreateObject("htmlfile")
'    Set window = html.parentWindow
'    window.execScript "var json = " & strJson , "Javascript"
'
'    Debug.Print window.eval("json.data[1].dataload[1]")

    Dim html, window
    Set html = CreateObject("htmlfile")
    Set window = html.parentWindow                            '建立 htmlfile物件 跟 window物件 執行 Javascript
    window.execScript "var json = " & strJson, "Javascript"   '執行 Javascript代碼字串   var json -> window.json
     
'    Debug.Print window.eval("json[1].dataload")             '在windows內Javascript取物件屬性的方式  eval函數可以執行Javascript代碼字串 使用eval 可以避免VBA自動將屬性名稱的第一個字母改成大寫  因為在Javascript大小寫有差異
'    Debug.Print window.eval("json.length")
'    Debug.Print window.eval("json[0].dataload.length")
'    Debug.Print window.eval("json[0].dataload[1]")
'    Debug.Print window.eval("json[1].dataload[1]")
'    Debug.Print window.eval("typeof(json)")
    
    For i = 0 To window.eval("json.length") - 1
        For j = 0 To window.eval("json[0].dataload.length") - 1
            Cells(i + 1, j + 1) = window.eval("json[" & i & "].dataload[" & j & "]")
            
        Next
    Next
    
    Set xmlHttp = Nothing
End Sub

 

#31

用Late binding的方式建立HTML Object 

如果要改用 Early binding，就要引用 Microsoft HTML Object Library

#33

將收到的Json格式資料，透過execScript連同變數宣告語句一起傳入window之中

#42-#47

這邊的迴圈是利用eval函數將要執行的Javascript程式碼傳入到window之中

然後將回傳值寫入儲存格中

 

參考資料

〈Excel VBA 經典程式碼─一行抵萬行「偷懶程式碼」應用大全〉，P17-60

how turn on the Microsoft HTML Object Library reference when using option explicit (excel-vba)?

Tags: