Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – API連携テンプレ

Excel VBA Excel VBA
スポンサーリンク

ねらい：Excelを「外部サービスとつながるクライアント」にする型を持つ

API連携テンプレのゴールは、こうです。
「毎回ググりながらバラバラのコードを書く」のをやめて、
“この型で書けば、だいたいどんなAPIとも話せる”という共通パターンを持つことです。

ここでは、
HTTP通信（GET/POST）のテンプレ
JSONレスポンスをシートに展開するテンプレ
設定シート方式でURLやキーを差し替えられる仕組み
を、初心者向けにかみ砕いてまとめます。

全体像：Excel VBAでAPIと話す基本フロー

API連携の流れを一枚の絵にする

ExcelからWeb APIと連携するときの流れは、いつも同じです。

APIのURLとパラメータを決める
HTTPリクエスト（GET/POSTなど）を送る
レスポンス（多くはJSON）を文字列として受け取る
必要ならJSONを解析して、シートに展開する

VBAでは、主に MSXML2.XMLHTTP または WinHttp.WinHttpRequest を使ってHTTP通信を行います。
ここでは、扱いやすい MSXML2.XMLHTTP をベースにテンプレを作ります。

コア部品1：GETリクエストの基本テンプレ

一番シンプルな「GETして中身を見る」コード

まずは、「APIにアクセスして、返ってきたJSONをそのままメッセージボックスで見る」テンプレです。

Sub ApiGet_Simple()
    Dim http As Object
    Dim url As String
    Dim response As String
    
    url = "https://jsonplaceholder.typicode.com/posts/1"  ' 例：ダミーAPI
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    
    http.Open "GET", url, False
    http.Send
    
    If http.Status = 200 Then
        response = http.responseText
        MsgBox response
    Else
        MsgBox "HTTPエラー: " & http.Status & " " & http.StatusText
    End If
End Sub
VB

初心者向けにポイントをかみ砕きます。

CreateObject(“MSXML2.XMLHTTP”) で「HTTPクライアント」を作る
Open で「メソッド（GET）」「URL」「同期/非同期（False＝同期）」を指定する
Send で実際にリクエストを送る
Status が 200（成功）なら responseText に中身（JSONなど）が入っている

まずはこのテンプレで、「APIから文字列を取ってくる」感覚をつかむのが第一歩です。

コア部品2：JSONレスポンスをシートに展開するテンプレ

JSONを扱うための前提（VBA-JSON）

多くのAPIはJSON形式でデータを返します。
VBAには標準でJSONパーサーがないので、実務では「VBA-JSON」というライブラリをよく使います。

JsonConverter.bas をプロジェクトにインポートしておくと、
JsonConverter.ParseJson でJSON文字列を「DictionaryやCollectionの入れ子」に変換できます。

ここでは、すでに JsonConverter が使える前提で話を進めます。

例題：シンプルなJSONをシートに展開する

APIから、こんなJSONが返ってくるとします。

{
  "userId": 1,
  "id": 1,
  "title": "sample title",
  "body": "sample body"
}

これを Result シートの A1:B4 に展開するテンプレです。

Sub ApiGet_JsonToSheet()
    Dim http As Object
    Dim url As String
    Dim response As String
    
    url = "https://jsonplaceholder.typicode.com/posts/1"
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send
    
    If http.Status <> 200 Then
        MsgBox "HTTPエラー: " & http.Status & " " & http.StatusText
        Exit Sub
    End If
    
    response = http.responseText
    
    Dim json As Object
    Set json = JsonConverter.ParseJson(response)
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Result")
    ws.Cells.ClearContents
    
    ws.Range("A1").Value = "キー"
    ws.Range("B1").Value = "値"
    
    ws.Range("A2").Value = "userId"
    ws.Range("B2").Value = json("userId")
    
    ws.Range("A3").Value = "id"
    ws.Range("B3").Value = json("id")
    
    ws.Range("A4").Value = "title"
    ws.Range("B4").Value = json("title")
    
    ws.Range("A5").Value = "body"
    ws.Range("B5").Value = json("body")
End Sub
VB

ここでの重要ポイントは、こうです。

responseText はただの文字列
ParseJson に渡すと、「キーでアクセスできるオブジェクト」に変わる
json(“title”) のように、キー名で値を取り出せる

最初は「1件のJSON」をシートに展開するところから慣れていくと、理解が早いです。

コア部品3：配列形式のJSONをテーブルとして展開するテンプレ

例題：配列JSONを行方向に展開する

今度は、APIからこんなJSONが返ってくるとします。

[
  { "id": 1, "name": "山田", "age": 30 },
  { "id": 2, "name": "佐藤", "age": 25 }
]

これを Users シートに、
1行目にヘッダー（id, name, age）、2行目以降にデータ、という形で展開するテンプレです。

Sub ApiGet_JsonArrayToSheet()
    Dim http As Object
    Dim url As String
    Dim response As String
    
    url = "https://example.com/api/users"   ' 仮のURL
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send
    
    If http.Status <> 200 Then
        MsgBox "HTTPエラー: " & http.Status & " " & http.StatusText
        Exit Sub
    End If
    
    response = http.responseText
    
    Dim json As Object
    Set json = JsonConverter.ParseJson(response)   ' json は Collection になる想定
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Users")
    ws.Cells.ClearContents
    
    If json.Count = 0 Then
        MsgBox "データがありません。"
        Exit Sub
    End If
    
    Dim firstItem As Object
    Set firstItem = json(1)
    
    Dim colIndex As Long
    colIndex = 1
    
    Dim key As Variant
    For Each key In firstItem.Keys
        ws.Cells(1, colIndex).Value = CStr(key)
        colIndex = colIndex + 1
    Next key
    
    Dim rowIndex As Long
    rowIndex = 2
    
    Dim item As Object
    For Each item In json
        colIndex = 1
        For Each key In firstItem.Keys
            ws.Cells(rowIndex, colIndex).Value = item(key)
            colIndex = colIndex + 1
        Next key
        rowIndex = rowIndex + 1
    Next item
End Sub
VB

ここでの重要ポイントを整理します。

配列JSONは ParseJson すると Collection になる
各要素（item）は Dictionary のようにキーでアクセスできる
最初の要素の Keys をヘッダーとして使い、全行に同じ順番で展開する

このテンプレを持っておけば、「APIから配列JSONをもらって、Excelに表として出す」処理はほぼコピペで済みます。

コア部品4：POSTリクエスト（JSON送信）のテンプレ

例題：シートの内容をJSONにしてPOSTする

SendData シートに、A列：name、B列：age が入っているとします。
これを1件ずつAPIにPOSTするイメージです。

Sub ApiPost_FromSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("SendData")
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    If lastRow < 2 Then Exit Sub
    
    Dim url As String
    url = "https://example.com/api/users"   ' 仮のURL
    
    Dim i As Long
    For i = 2 To lastRow
        Dim name As String
        Dim age As Long
        
        name = CStr(ws.Cells(i, 1).Value)
        age = CLng(ws.Cells(i, 2).Value)
        
        Dim jsonBody As String
        jsonBody = "{""name"":""" & Replace(name, """", "\""") & """,""age"":" & CStr(age) & "}"
        
        Dim http As Object
        Set http = CreateObject("MSXML2.XMLHTTP")
        
        http.Open "POST", url, False
        http.setRequestHeader "Content-Type", "application/json"
        http.Send jsonBody
        
        If http.Status <> 200 And http.Status <> 201 Then
            MsgBox "行" & i & "でエラー: " & http.Status & " " & http.StatusText
            Exit Sub
        End If
    Next i
End Sub
VB

ここでの重要ポイントは、こうです。

POST のときは Content-Type ヘッダーを設定する（多くは application/json）
JSON文字列は、VBA側で組み立てる（ダブルクォートのエスケープに注意）
Status が 200/201 以外ならエラーとして扱う

実務では、1件ずつPOSTするのではなく、まとめて送るAPIも多いですが、
まずは「1行→1リクエスト」の型で慣れると理解しやすいです。

コア部品5：設定シート方式で「URL・APIキー」を外出しする

ConfigApiシートで管理する

実務でAPI連携をするとき、
URLやAPIキーをコードにベタ書きすると、環境変更やキー更新のたびにVBAを触ることになります。

そこで、ConfigApi シートを用意して、こういう形で持たせます。

A列：キー名（BaseUrl, ApiKey など）
B列：値

そして、VBA側では「設定を読む小さな関数」を用意します。

Function GetApiConfig(ByVal keyName As String) As String
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("ConfigApi")
    
    Dim rng As Range
    Set rng = ws.Range("A:A").Find(What:=keyName, LookAt:=xlWhole)
    
    If rng Is Nothing Then
        GetApiConfig = ""
    Else
        GetApiConfig = CStr(rng.Offset(0, 1).Value)
    End If
End Function
VB

これを使えば、API呼び出し側はこう書けます。

Dim baseUrl As String
baseUrl = GetApiConfig("BaseUrl")

Dim apiKey As String
apiKey = GetApiConfig("ApiKey")

Dim url As String
url = baseUrl & "/users?apikey=" & apiKey
VB

「接続先やキーが変わったら ConfigApi だけ直す」という状態にしておくと、
API連携テンプレが“現場で回る仕組み”になります。

重要ポイントの深掘り：API連携テンプレを“怖くないもの”にする

ステータスコードとエラーメッセージを必ず見る

API連携で一番やりがちなのが、「responseText だけ見て、Status を見ない」ことです。
必ず、こういう型を徹底します。

If http.Status <> 200 Then
    MsgBox "HTTPエラー: " & http.Status & " " & http.StatusText & vbCrLf & http.responseText
    Exit Sub
End If
VB

Status と StatusText を見るだけで、「URLが間違っている」「認証エラー」「サーバー側エラー」などの切り分けがしやすくなります。

タイムアウトやネットワークエラーを想定しておく

APIは「必ず返ってくる」とは限りません。
ネットワークの問題で固まることもあります。

MSXML2.XMLHTTP ではタイムアウト設定が弱いので、
本格的にやるなら WinHttp.WinHttpRequest を使って SetTimeouts を設定するパターンもあります。
まずは「固まったらEscで止める」でも構いませんが、
“APIは落ちることもある”前提で、エラー時のメッセージを丁寧にしておくと、運用が楽になります。

「Excelでやること」と「APIに任せること」を分ける

API連携を始めると、「全部Excelで加工したくなる」か「全部APIでやりたくなる」か、極端になりがちです。

現実的には、こう分けるのがおすすめです。

大量データの絞り込み・集計・検索条件の適用は、できるだけAPI側（サーバー側）
見せ方の調整・レイアウト・細かい加工は、Excel側

この線引きを意識すると、「APIはデータの入口、Excelはフロント」という役割分担がはっきりして、
設計がすっきりします。

まとめ：API連携テンプレは「HTTP＋JSON＋設定シート」の3点セット

今日のAPI連携テンプレを一言でまとめると、こうなります。

MSXML2.XMLHTTP で GET/POST を送る基本型を持つ
JSONレスポンスは ParseJson でオブジェクト化し、シートに展開する型を持つ
URLやAPIキーは ConfigApi シートに逃がし、コードからは GetApiConfig で読む

この“3点セット”さえ一度作ってしまえば、
天気API、為替API、社内REST APIなど、違うサービスでも「やることは同じ」に見えてきます。

スポンサーリンク
タイトルとURLをコピーしました