【EXCEL VBA】VBAからREST APIを呼び出したい

2020.11.01

外部REST APIサービスから情報を取得し、EXCELシートへ反映したいという要件が上がってきました。pythonやnodeを利用し取得することを検討しましたが、VBAからREST APIを呼び出しすることができるようでした。調査結果を記事にしました。

今回はサンプルプログラムで動作確認できるよう無料利用できる「httpbin」サービスを呼び出しています。サーバーモックを作るまでもない簡単な開発では非常に重宝します。クライアントから送信した「ヘッダー情報」と「リクエスト情報」を確認することができます。JSON形式のレスポンスが返却されます。

httpbin.org
httpbin.org

サンプルプログラムでは「Dictionary」と「XMLHTML60」クラスを使います。よって、参照設定にて「Microsoft Scripting Runtime」と「Microsoft XML, v6.0」をONにしておいてください。

【EXCEL VBA】Dictionary、FileSystemObjectを利用したい
Dictionary、FileSystemObjectを利用するには、VBEの参照設定を変更する必要があります。参照可能なライブラリファイルとして「Microsoft Scripting Runtime」にチェックを入れる必要があります。 ...
vba-labo.rs-techdev.com
2019.06.14

サンプルプログラム

HTTP GETとHTTP POSTの呼び出し方法について紹介します。HTTP GETについてはURLエンコーディングが必要なため関数を用意しました。HTTP応答待ち時にDoEvents関数をコールしていますが、これはOSへ制御を戻すためです。

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

    
    ' POSTサンプル
    With httpReq
      .Open "POST", "https://httpbin.org/post"
      .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
      .Send "key4=parameterSample&key5=日本語パラメータ&key6=% $\記号"
    End With

    Do While httpReq.readyState < 4
        DoEvents
    Loop

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

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

レスポンス情報（Debug.prinの出力結果）　※一部情報をマスクしています。

GETレスポンス
{
  "args": {
    "Key2": "\u65e5\u672c\u8a9e\u30d1\u30e9\u30e1\u30fc\u30bf\u30b5\u30f3\u30d7\u30eb", 
    "Key3": "% $\\\u8a18\u53f7", 
    "key1": "parameterSample"
  }, 
  "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-1266f2a36f1d488228a562c8"
  }, 
  "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"
}

POSTレスポンス
{
  "args": {}, 
  "data": "", 
  "files": {}, 
  "form": {
    "key4": "parameterSample", 
    "key5": "\u65e5\u672c\u8a9e\u30d1\u30e9\u30e1\u30fc\u30bf", 
    "key6": "% $\\\u8a18\u53f7"
  }, 
  "headers": {
    "Accept": "*/*", 
    "Accept-Encoding": "gzip, deflate", 
    "Accept-Language": "ja-JP", 
    "Cache-Control": "no-cache", 
    "Content-Length": "66", 
    "Content-Type": "application/x-www-form-urlencoded", 
    "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-168acf763a92e812393a15c0"
  }, 
  "json": null, 
  "origin": "XXX.XXX.XXX.XXX", 
  "url": "https://httpbin.org/post"
}

スポンサーリンク