22
31

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

VBAでJSONを読み込んでみる

Posted at

なんかふと気になったので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文を回せばいい。

22
31
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
22
31

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?