【EXCEL VBA】VBAでJSONパースをしたい

VBAでREST APIサービスを利用する際に、サービスからのレスポンスがJSON形式であることが多々あります。自力でJSONをパースするのは厳しいので「VBA-JSON」を利用したパース方法を紹介します。

事前準備

「VBA-JSON」を利用するには事前準備が必要となります。以下記事を確認して設定を完了させてください。

【EXCEL VBA】VBAでJSONを利用したい
VBAでJSONを利用するには、VBA-JSON一択となっています。まずは、VBA-JSONの導入方法について紹介します。 （昔は「ScriptControl」が使えたようですが、Excel2010のサポート切れ、Office365(64ビ...
vba-labo.rs-techdev.com
2020.11.01

サンプルプログラム

JSONパースのポイントは、「JsonConverter.ParseJson」です。Dictionary型で返却されるので、あとは直接参照なりループ処理等で値を抽出するだけです。

Option Explicit

Sub sample()
    
    Dim httpReq As New XMLHTTP60   '「Microsoft XML, v6.0」を参照設定
    Dim params As New Dictionary   '「Microsoft Scripting Runtime」を参照設定
    
    ' GETサンプル
    ' リクエスト情報を作成する。
    params.Add "Key1", "parameterSample"
    params.Add "Key2", "日本語パラメータサンプル"
    params.Add "Key3", "% $\記号"

    With httpReq
      .Open "GET", "https://httpbin.org/get?" & encodeParams(params)
      .Send
    End With

    Do While httpReq.readyState < 4
        DoEvents
    Loop

    Debug.Print "GETレスポンス"
    Debug.Print httpReq.responseText

    ' JSONパースサンプル
    Dim jsonObj As Object
    Set jsonObj = JsonConverter.ParseJson(httpReq.responseText)
    
    Debug.Print "args-Key1 = " & jsonObj("args")("Key1")
    Debug.Print "args-Key2 = " & jsonObj("args")("Key2")
    Debug.Print "args-Key3 = " & jsonObj("args")("Key3")
    
    Dim arg As Variant
    For Each arg In jsonObj("args")
      Debug.Print arg & " = " & jsonObj("args")(arg)
    Next
    
End Sub


' URLエンコード関数
Function encodeParams(pDic As Dictionary)
    
    Dim ary() As String
    ReDim ary(pDic.Count - 1) As String

    Dim i As Long
    For i = 0 To pDic.Count - 1
     ary(i) = pDic.Keys(i) & "=" & Application.WorksheetFunction.EncodeURL(pDic.Items(i))
    Next i

    encodeParams = Join(ary, "&")

End Function

出力結果

GETレスポンス
{
  "args": {
    "Key1": "parameterSample", 
    "Key2": "\u65e5\u672c\u8a9e\u30d1\u30e9\u30e1\u30fc\u30bf\u30b5\u30f3\u30d7\u30eb", 
    "Key3": "% $\\\u8a18\u53f7"
  }, 
  "headers": {
    "Accept": "*/*", 
    "Accept-Encoding": "gzip, deflate", 
    "Accept-Language": "ja-JP", 
    "Host": "httpbin.org", 
    "User-Agent": "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 10.0; WOW64; Trident/7.0; .NET4.0C; .NET4.0E; .NET CLR 2.0.50727; .NET CLR 3.0.30729; .NET CLR 3.5.30729; Zoom 3.6.0)", 
    "X-Amzn-Trace-Id": "Root=1-XXXXXXXX-6dfffa737c7c748b053f2a11"
  }, 
  "origin": "XXX.XXX.XXX.XXX", 
  "url": "https://httpbin.org/get?Key1=parameterSample&Key2=\u65e5\u672c\u8a9e\u30d1\u30e9\u30e1\u30fc\u30bf\u30b5\u30f3\u30d7\u30eb&Key3=%25 %24\\\u8a18\u53f7"
}

args-Key1 = parameterSample
args-Key2 = 日本語パラメータサンプル
args-Key3 = % $\記号
Key1 = parameterSample
Key2 = 日本語パラメータサンプル
Key3 = % $\記号

スポンサーリンク