Access

AccessVBAでJSONフォーマットのデータを作成する

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}

 

配列やブロックのある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
}
]
}

※通常はテーブルから読みだしてループする形になると思います。今回はサンプルなのでベタ書きしています。

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フォーマットでの日付を表す表示に変換されてしまいます。

(例)変数Aに格納されている日時(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テキストはファイルに出力した方が検証しやすいです。

 

 

ACCESS VBAからWebAPIをコールするAccessのVBAからWebAPIをコールする方法です。 私はインセンティブ配信用のURLをAPI関数を使って取得するために利用...

 

ABOUT ME
アズビーパートナーズ
アズビーパートナーズ
プログラマーと社内SEとしての経験を活かして、 情報システム部門のご相談を承ります。 得意な分野はAccessによる短納期開発、 BIツール(QlikSense/QlikView)の開発です。 現在はCMSを利用したホームページの作成にも力を入れています。
今のスキルのままで大丈夫?

あなたのスキル。今のままで大丈夫ですか?

時代のニーズに合った開発スキルを身につけてあなたの価値を高めましょう。

\オンラインでも教室でも学べるおすすめのスクール/

TECH::CAMP(エンジニアスクール)

まずは基礎から・・・という方はProgateUdemyどっとインストールなどがおすすめ!

スキルアップについて詳しくはこちら