VBAでJScriptを利用し、JSONデータをパースする

この記事について

VBAそのものにはJSON文字をパースするための関数は含まれていないそうです。
なので、JScriptを利用しJSONを読み込み、パースする方法をご紹介します。

開発環境

PC: Windows10 64ビット
Excel：2022 32ビット

Macだと下記の方法で試しても動かなかったので注意してください。

準備

読み込むJSONデータはこんな感じ

    {"date":"20221201 17:31:35","measurer":{"id":"0000001""contents":"テスト"},"name": "HogeHoge",}
    {"date":"20221202 11:28:10","measurer":{"id":"0000002""contents":"テスト2"},"name": "HogeHoge2",}

例なので2行しかありませんが、実際使用したデータは100行以上あります。
このデータの名前をtest.jsonとし、Cドライブのhogehogeフォルダの中に格納しました。

実装内容

コード

Sub test()
Dim num As Integer
Dim str As String

'ファイル番号を取得
 num = FreeFile
'JSONファイルを開く
 Open "C:\hogehoge\test.json" For Input As #num
 
 'EOF関数でファイルの末尾まで処理を繰り返す
  Do Until EOF(num)
   '1行ずつデータを読み込む
    Line Input #num, str
   'JSON文字列をデシリアライズ（JScriptを利用）
    Dim json: Set json = ParseJson(str)

   'JSONのmeasurer.idを取得
    Dim id As String
    id = CallByName(CallByName(json, "measure", VbGet), "id", VbGet)

    '処理を繰り返す
    Loop
 'ファイルを閉じる
 Close #num
End Sub

Function ParseJson(str)
  Dim scriptControl: Set scriptControl = CreateObject("ScriptControl")
  scriptControl.Language = "JScript"
  scriptControl.AddCode "function Parse(str) { return eval('(' + str + ')'); };"
  Dim json: Set json = scriptControl.CodeObject.Parse(str)
  Set ParseJson = json
End Function

解説

   'JSONのmeasurer.idを取得
    Dim id As String
    id = CallByName(CallByName(json, "measure", VbGet), "id", VbGet)

例として、ここでidを取得していますが、同じような書き方でdateやnameも取得可能です。

   'JSONのdateを取得
    Dim date As String
    date = CallByName(json, "date", VbGet)
   'JSONのnameを取得
    Dim name As String
    name = CallByName(json, "name", VbGet)

また、メッセージボックスでIDを表示させるなら以下のような書き方になります。

    MsgBox CallByName(CallByName(json, "measure", VbGet), "id", VbGet)

参考記事