[VBA] 抓取 Json 檔案轉入 Excel

周一同事說，有個政府網頁的數據因為直接 Show 圖上，當滑鼠停在上面才會跳出數據，所以必須一筆一筆透過移動滑鼠來讀取並記錄。我回覆說，可以把網頁提供給資訊部，看看能不能協助存取。

昨天我收到網址，並由同事告訴我她要抓甚麼數據，確認後，開啟 Edge 的開發者模式，馬上就確認是透過 AJAX 讀取資料更新畫面。

用 Edge 測試了幾個網址，資料內容是 Json 格式，都可以直接撈出來，大概沒啥阻擋，打算用過去寫好 .Net framework 的 WebClient 去抓，一測試，WebClient 連線能建立成功，但是無法取得資料流，檔案大小都是 0 到逾時。

---

註：寫部落格時才想到，可能是 https 加密問題… 參考先前紀錄：

[CLR] .Net framework 2.0 WebClient 連接 https

---

剛好我以前是用 VBScript 寫過 AJAX ，有現成程式碼拷貝到 Excel 去測試，在 Excel 測 xmlhttp 抓資料就正常，所以可以推論這個網站有針對 http protocol 的 Header 做偵測與阻擋。

透過 xmlhttp 抓 AJAX 取得 Json 資料後，問題在 Json 解碼，這個在 GitHub 有現成程式碼可以引用：

GitHub – VBA-tools/VBA-JSON: JSON conversion and parsing for VBA

我直接引用他寫好的 JsonConverter ，見下圖的專案視窗。

上面網址的最下方說明，要同時引用他另一個專案：

GitHub – VBA-tools/VBA-Dictionary: Drop-in replacement for Scripting.Dictionary on Mac

我在 Excel 365 跑這段程式碼的時候，會因為他自建類別 Dictionary ，與 Scripting.Dictionary 不相容，所以我去修改他的類別，把原始 Scripting.Dictionary 透過 .This 丟出來，就可以順利的完成執行。

主要程式碼如下圖：

VBA 透過 XmlHttp 抓取 AJAX 資料並分析匯入

順利把 Json 共 24 欄 12,313 列資料匯入 Excel 。

已匯入的原始資料