VBAでJSONを読み込んでみる

なんかふと気になったのでExcelでJSONを読み取る方法を探した。

JSONパース自体はこちらを参考にさせてもらった。
ExcelでJSONを扱う場合のつまずきポイント

まず前提として、外部ファイルであるJSONファイルを開き読み込んでシートに一覧出力する、
という処理を行いたい。

JSONの構造

JSONの構造は下記。

{"tweet":[
    {
        "id":"0001",
        "daytime":[
            {
                "year":"2019",
                "month":"01",
                "day":"01",
                "hour":"12",
                "minute":"00"
            }
        ],
        "contents":"テストテストテスト"
    },
    {
        "id":"0002",
        "daytime":[
            {
                "year":"2019",
                "month":"01",
                "day":"01",
                "hour":"12",
                "minute":"00"
            }
        ],
        "contents":"hogehoge"
    }
]}

JSONファイルを読み込む

ということで、以下がファイル入力のコード。

    Dim buf as String
    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open
        .LoadFromFile "./tweet.json"
        buf = .ReadText
        .Close
    End With

JSONファイルには日本語も含まれているので、
文字コードを選択できるADODBを呼び出す方法を使った。

読み込んだ内容をパースする

上のリンク先コードを活用させてもらった。

    Dim obj as Object
    Dim json as Object

    Set obj = CreateObject("ScriptControl")
    obj.Language = "JScript"
    obj.addcode "function jsonParse(s){ return eval('('+s+')');}"

    Set json = obj.CodeObject.jsonParse(buf)

これで読み込んだJSONファイルを使う準備は整った。

読み込んだJSONファイルを使う

まず先ほども参照させてもらった上のリンク先の方法。

    Set json = obj.CodeObject.jsonParse("{id:5,name:'山田',age:20}")

    MsgBox CallByName(json, "name", VbGet)

値として「name」を渡してやることで、「name」の右辺である「山田」という値を取り出している。
「id」を渡すと「5」、
「age」を渡すと「20」が、
返ってくるのは想像できた。

何となく既視感あるなと思っていたが、やっと似たものを思い出す。
そう、連想配列だ。

ちなみに個人的にPHPでJSONを扱うときの連想配列を想像するのがとても理解しやすかった。

先ほどの例で行くと、PHPでは下記のようになる。

    echo $json["name"];

つまり、一個のCallByNameがPHPでの[]になるのだ。
それを踏まえて。

最初に前提として提示したJSONで、一つ目のcontentsの中身を取り出したい場合、
PHPでは下記のようになる。

    echo $json['tweet'][0]['contents'];

つまりマクロでは[]の個数分CallByNameを呼べばいいということになる。

    contents = CallByName(CallByName(CallByName(json, "tweet", VbGet), 0, VbGet), "contents", VbGet)

「day」の値を取る場合は下記。

    day = CallByName(CallByName(CallByName(CallByName(CallByName(json, "tweet", VbGet), i, VbGet), "daytime", VbGet), 0, VbGet), "day", VbGet)

個数を取得したい場合

上記のJSONで配列値の個数を知りたい時。

    splt = Split(CallByName(json, "tweet", VbGet), ",")
    For i = 0 To UBound(splt)
        MsgBox CallByName(CallByName(CallByName(json, "tweet", VbGet), 0, VbGet), "id", VbGet)
    Next i

配列値の上の階層？までをCallByNameして、その値を「,」でSplitで分割する。
そしてその分けられた配列数をUBound関数で調べて、その値を使ってfor文を回せばいい。