VBA-JSONでJSON文字列を作成してみる！

はじめに

VBAでWebAPIを使う際、データの取得（GET）処理では、この記事のようにJSONパースの情報が比較的多く公開されていたが、データの送信（POSTとか）時に使用するExcelデータ⇒JSONデータの作成方法が見当たらなかったのでメモです。

VBA-JSONの導入方法などなどは上記リンク等や、GitのReadMe見てください。

Jsonデータ作成概要

VBA-JSONでJsonデータを生成するには”JsonConverter.ConvertToJson”を使います。この関数は引数にDictionary配列をとり、戻り値はString型です。

WebAPIではざっくりいうとこの戻り値を渡してあげればOKです！

Deictionary配列のKeyとvalueがJSONのKeyとValueに対応していて、複数のKeyとValueをDictionary配列に渡せば、それぞれがJSONのメンバーとして出力されます。

サンプル

単一行

以下のように3列1行の表を前提とします。 

その場合には以下のコードのようにDictionary配列にKeyとValueをいれてJsonConverter.ConvertToJsonに渡してあげれば...

sample.bas

    Dim buf As New Dictionary

    'Key項目だけでLoop
    For Each Item In Range("A1:C1")
        
        '値はOffsetで取得
        Call buf.Add(Item.Value, Item.Offset(1, 0).Value)

    Next

    'Json型文字列取得と結果表示
    MsgBox JsonConverter.ConvertToJson(buf)

以下のようにJSONデータが生成されます。

WebAPIのPayloadとしても使えそうですね。

複数行

複数行の場合にはかなりややこしくなります。 

複数行の場合には各行をDictionaryで表現しつつ、それらを一旦Collection型配列に格納します。さらにそれをJsonConverterに渡すためにDictionary配列の要素に格納します。

sample2.bas

    Dim buf As New Dictionary
    Dim bufColl As New Collection
    
    '行ループ
    For i = 1 To 2
    
        'Key項目だけでLoop 列ループ
        For Each Item In Range("A1:C1")
                    
            '値はOffsetで取得
            Call buf.Add(Item.Value, Item.Offset(i, 0).Value)
    
        Next
        
        'DictionaryをCollectionに格納
        Call bufColl.Add(buf)
        
        '次ループに備えDictionary初期化。これをしないとKey重複エラーになる
        Set buf = Nothing
        Set buf = New Dictionary
    
    Next i
    
    'CollectionをDictionaryに格納
    Call buf.Add("items", bufColl)

    'Json型文字列取得
    MsgBox JsonConverter.ConvertToJson(buf)

配列で出力されました。

Collectionをかませることで親が1で子要素がNのケースでも使えますし、その階層はいくつでも深くできます。

まとめ

結構簡単にExcelからJSONデータを作成することができます。 複数行を配列にしたいときだけCollectionを使うようにしましょう。