AccessからAPIをコールしてクラウドサービスから情報を取得することになりました。
APIの引数と戻り値の形式はJSONフォーマット。
VBAではなかなか使わないフォーマットです。
今回はVBAでJSONフォーマットを作成するところを解説していきます！
JSONフォーマットを作成する準備
今回はConvertToJson()関数を使って作成していきます。
必要なのはVBA-JSONの導入と参照設定です。
VBA-JSONの導入
ソースはGitHubにあります。
まずはダウンロードしましょう。
zip形式でダウンロードできますので、解凍しておきます。
JsonConverter.basをインポートします。
▲ファイル→ファイルのインポート
参照設定
ConvertToJson()関数に引数としてデータを渡す際には連想配列の形を使います。
これがDictionary型を使うためには「Microsoft Scripting Runtime」ライブラリが必要となりますので、VBAのツールから参照設定を追加してください。
[ツール(T)] > [参照設定(R)] から「Microsoft Scripting Runtime」にチェック
参照設定を追加していない場合、「コンパイルエラー：ユーザー定義型は定義されていません。」のエラーになります！
シンプルなJSONフォーマットを作成する
【変換前のデータ】
| Code | issueNo |
| ABC-001 | 1 |
【変換後のデータ】
{“code”:”ABC-001″,”issueNo”:1}
Private Sub MakeJson()
Dim param As New Dictionary
Dim strResponse As String
On Error GoTo Err_Exit
'IDと商品コードを連想配列にセット
param.Add "code", "ABC-001"
param.Add "issueNo", 1
'連想配列からJSONフォーマットに変換
strResponse = ConvertToJson(param)
Debug.Print strResponse
Exit Sub
Err_Exit:
MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical
End Sub
配列やブロックのあるJSONフォーマット
【変換後のデータ】
| CustomerID | Name | Gender |
| 1 | Taro Tanaka | 1 |
| CustomerID | VisitDate | ItemCode |
| 1 | 2020/06/01 12:02:00 | 999 |
| 1 | 2020/06/01 18:30:00 | 111 |
【変換後のデータ】
{
“CUSTOMER”: [
{
“CustomerID”: 1,
“Name”: “Taro Tanaka”,
“Gender”: 1
}
],
“ITEM”: [
{
“CustomerID”: 1,
“VisitDate”: “2020/06/01 18:30:00”,
“ItemCode”: 111
},
{
“CustomerID”: 1,
“VisitDate”: “2020/06/01 18:30:00”,
“ItemCode”: 111
}
]
}
Dim param As New Dictionary
Dim strResponse As String
On Error GoTo Err_Exit
'顧客情報と商品情報を定義するDictionary配下のオブジェクトとして定義
param.Add "CUSTOMER", New Collection
param.Add "ITEM", New Collection
'顧客情報をｾｯﾄ（1レコード)
Dim customerObj As New Dictionary
customerObj.Add "CustomerID", 1
customerObj.Add "Name", "Taro Tanaka"
customerObj.Add "Gender", 1
param("CUSTOMER").Add customerObj
'商品情報をｾｯﾄ（複数レコード)
Dim ItemObj As New Dictionary
'①データ
ItemObj.Add "CustomerID", 1
ItemObj.Add "VisitDate", "2020/06/01 12:02:00"
ItemObj.Add "ItemCode", 999
param("ITEM").Add ItemObj
'ItemObjをｸﾘｱ
ItemObj.RemoveAll
'②データ
ItemObj.Add "CustomerID", 1
ItemObj.Add "VisitDate", "2020/06/01 18:30:00"
ItemObj.Add "ItemCode", 111
param("ITEM").Add ItemObj
'JSONフォーマットに変換
'JSONを読みやすくするためwhitespaceでインデントを設定
strResponse = ConvertToJson(param, Whitespace:=2)
Debug.Print strResponse
Exit Sub
Err_Exit:
MsgBox Err.Number & ":" & Err.Description, vbOKOnly + vbCritical
End Sub
※通常はテーブルから読みだしてループする形になると思います。今回はサンプルなのでベタ書きしています。
JSONフォーマットの注意点
引数にnullを渡すときは、【null】
真偽値を渡すときは、【true】【false】をそれぞれ小文字で渡します。
日付は文字列として渡します。
null, true, falseにはダブルクォート（”）囲みは不要です！
accessのYes/No型値をそのまま渡すとConvertToJson()関数がダブルクォートなしのtrue,falseをｾｯﾄしてくれます♪
日本語文字列を引数渡ししたい
また、ConvertToJson()関数を使うと、日本語文字は【16 進数で表した文字列型】に変換されます。
JSONを受け取る側の仕様によってはそのままの文字列として渡したい場合もあります。
その場合は、JsonConverterのjson_Encode()関数内の以下の行をコメントアウトします。
Case 0 To 31, 127 To 65535
' Non-ascii characters -> convert to 4-digit hex
json_Char = "\u" & VBA.Right$("0000" & VBA.Hex$(json_AscCode), 4)
json_Encode()変更なし → ”item”: “\u30CF”,
json_Encode()変更後 → ”item”: “ハ”,
日付型の値をそのまま渡すと時間がずれる？
JSONに日付の値をそのまま渡すとISO 8601フォーマットでの日付を表す表示に変換されてしまいます。
（例）変数Ａに格納されている日時（2020/07/02 10:30）
変換した後の値（”2020-07-02T01:30:00.000Z”）・・・＞1:30扱い
うっかりしていると時刻がずれてしまうので、日付の値も文字列として渡すよう気をつけましょう。
〇 itemobj.add “VisitDate”, format(変数A, “yyyy/mm/dd hh:nn”)
× itemobj.add “VisitDate”, 変数A
完成したJsonテキストの検証
プログラム中ではdebug.printで.responseTextを表示させています。
ただdebug.print（イミディエイトウィンドウ）には文字数制約があるため全てを表示することができない場合があります。
msgbox()関数にも文字数制限があるので、長文になるJsonテキストはファイルに出力した方が検証しやすいです。
' *==============================================================
' * 文字列ファイル出力
' *
' * @param strjson (出力する文字列）
' *==============================================================
Public Sub makeText(ByVal strjson As String)
Dim datFile As String
'カレントフォルダにdata.txtファイルを出力
datFile = CurrentProject.Path & "\data.txt"
Open datFile For Output As #1
Print #1, strjson
Close #1
End Sub
