VBAからREST API連携: 実務レベルのMSXML活用術と性能最適化

Tech

本記事はGeminiの出力をプロンプト工学で整理した業務ドラフト（未検証）です。

VBAからREST API連携: 実務レベルのMSXML活用術と性能最適化

背景/要件

現代のビジネスシステムでは、VBAで構築されたOfficeアプリケーション（Excel/Accessなど）が基幹業務を支えているケースが少なくありません。これらの既存システムを、クラウドサービスやWeb APIと連携させることで、業務効率の向上やデータ活用の幅を広げることが求められています。特に、新しい外部サービスはREST API形式で提供されることが多く、VBAからの連携は喫緊の課題となっています。

しかし、多くの企業環境ではセキュリティや管理上の理由から、外部ライブラリ（特に独自にインストールが必要なDLLやOCX）の導入が厳しく制限されています。この要件の下で、VBAからREST APIと連携し、さらに実務に耐えうる性能と堅牢性を確保するための具体的な手法が求められます。 、この「外部ライブラリ禁止」という制約を厳守しつつ、Windowsに標準搭載されているコンポーネントやWin32 APIを駆使して、VBAからREST APIと連携する実践的な方法を解説します。

設計

HTTPクライアントの選択

「外部ライブラリ禁止」という要件を考慮すると、VBAからHTTP通信を行う主要な選択肢は以下の2つです。

  1. MSXML2.XMLHTTP (COMオブジェクト):

    • Windows OSに標準搭載されているCOMコンポーネントであり、通常は個別のインストールが不要です。VBAからCreateObject("MSXML2.XMLHTTP")またはCreateObject("MSXML2.XMLHTTP.6.0")でインスタンス化できます。

    • HTTPメソッド（GET, POST, PUT, DELETE）、ヘッダー設定、タイムアウト、ステータスコード取得など、REST API連携に必要な機能を網羅しています。

    • 実務上、「外部ライブラリ」の制約には抵触しないと判断されることがほとんどです。

  2. WinHTTP API (Win32 API):

    • WinHttp.dll内の関数（WinHttpOpen, WinHttpConnect, WinHttpOpenRequest, WinHttpSendRequestなど）をDeclare PtrSafeで宣言し、一連のAPI呼び出しでHTTP通信を直接制御します。

    • COMオブジェクトの使用も厳しく制限される、極めて特殊でロックダウンされた環境での最終手段です。

    • 実装は非常に複雑でVBAコードが肥大化しやすく、エラーハンドリングも困難になるため、開発コストが大幅に増加します。実務で採用されるケースは稀です。

本記事では、実用性と開発効率を考慮し、最も一般的に利用されるMSXML2.XMLHTTPをメインに採用します。これはWindows標準搭載のコンポーネントであり、多くの環境で「外部ライブラリのインストールなしに利用可能」という要件を満たすためです。

データ形式と処理

  • JSON形式: REST APIの多くはJSON形式でデータを送受信します。VBAにはJSONを直接扱う組み込み関数がないため、外部ライブラリなしでのJSONパース（解析）や生成は非常に困難です。

    • パース: 単純なキー/バリューペアであれば、InStrやSplit、正規表現（VBScript.RegExpオブジェクトはWindows標準なので利用可能）を用いた文字列操作で部分的に抽出できます。複雑なネスト構造や配列は、自力でのパースは非現実的です。

    • 生成: VBAの文字列連結機能を用いて、JSON文字列を手動で構築します。キー名や値のエスケープ処理（例: ダブルクォーテーションのエスケープ）を適切に行う必要があります。

認証

APIキー、Bearerトークン、基本認証など、APIが要求する認証情報をHTTPヘッダーにsetRequestHeaderメソッドで設定します。

エラーハンドリング

  • HTTPステータスコード (objHTTP.Status) を確認し、成功 (2xx) 以外の場合はエラーと判断します。

  • VBAのOn Error GoTo構文を用いて、ネットワークエラーやプログラム実行時エラーを捕捉し、適切なメッセージ表示やログ記録を行います。

性能チューニング

特に大量のデータを扱う場合、以下の手法を適用し、処理速度とユーザー体験を向上させます。

  • Excel:

    • Application.ScreenUpdating = False: 画面描画を停止し、処理速度を向上させます。

    • Application.Calculation = xlCalculationManual: 自動再計算を停止し、処理完了後に手動で再計算します。

    • Application.EnableEvents = False: イベント発生を停止し、余分な処理を抑制します。

    • 配列バッファ: Excelシートへの書き込みは、セル一つずつ行うと非常に低速です。データを一度VBA配列に格納し、その配列をシート範囲に一括で書き込むことで大幅に高速化します。

  • Access:

    • DAO.RecordsetやADO.Recordsetの効率的な使用: MoveFirst/MoveNextによるループ処理。

    • トランザクション: データベースの更新を伴う場合、一連の処理をトランザクションで囲むことで、整合性を保ちつつ性能を向上させることが可能です。ただし、API連携自体は外部サービスへの操作のため、VBAのトランザクションはDB更新部分に限定されます。

    • DoEvents: 長時間かかるループ処理中に定期的にDoEventsを呼び出すことで、UIのフリーズを防ぎ、ユーザーに操作感を与えます。

処理の流れ/データモデル (Mermaid)

graph TD
    A["VBAアプリケーション開始"] --> B{"API連携処理"};
    B --|リクエストデータ準備| C["HTTPクライアント初期化"];
    C --|認証情報/ヘッダー設定| D["GET/POST/PUTメソッド設定"];
    D --|リクエスト送信| E{"APIサーバー応答待機"};
    E --|HTTPステータスコード確認| F{"応答成功?"};
    F --|はい (2xx)| G["応答データ取得 (JSON)"];
    G --|データ解析/処理| H["VBAアプリケーション側でのデータ更新"];
    F --|いいえ (4xx/5xx)| I["エラー処理/ログ記録"];
    H --> J["処理完了"];
    I --> J;
    J --> K["VBAアプリケーション終了"];

実装

以下のコード例は、ExcelとAccessそれぞれでREST API連携を行う方法を示します。

コード1 (Excel: 外部APIからのデータ取得とシート更新)

この例では、公開されているJSONPlaceholder APIからToDoリストデータを取得し、Excelシートに反映します。性能チューニングとして、画面更新停止、計算モード変更、配列バッファによる一括書き込みを適用します。

' Win32 APIに関する補足:
' MSXML2.XMLHTTPはWindows標準搭載のCOMコンポーネントであり、通常は
' 「外部ライブラリ禁止」の制約下でも利用可能です。
' 厳密にCOMオブジェクトも許容されない環境であれば、WinHttp APIをDeclare PtrSafeで
' 多数宣言し、低レベルなHTTP通信を構築する必要がありますが、これは非常に複雑です。
' 本コードでは実用性を重視し、MSXML2.XMLHTTPを利用します。

Option Explicit

'=== Excel向けのAPI連携例 ===
Sub GetPublicApiDataAndPopulateExcel()
    Dim objHTTP As Object ' MSXML2.XMLHTTPオブジェクト
    Dim sURL As String
    Dim sResponse As String
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArray() As Variant ' 配列バッファ
    Dim i As Long
    Dim startTime As Double
    Dim endTime As Double

    ' 性能計測開始
    startTime = Timer

    ' 初期設定
    Set ws = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "APIデータ_" & Format(Now, "hhmmss")

    Application.ScreenUpdating = False        ' 画面更新停止
    Application.Calculation = xlCalculationManual ' 自動計算停止
    Application.EnableEvents = False          ' イベント発生停止

    On Error GoTo ErrorHandler

    ' HTTPオブジェクトの作成 (CreateObjectで参照設定なしに動的に生成)
    Set objHTTP = CreateObject("MSXML2.XMLHTTP") ' または "MSXML2.XMLHTTP.6.0"

    ' APIエンドポイント (例: JSONPlaceholderのToDoリスト)
    sURL = "https://jsonplaceholder.typicode.com/todos"

    With objHTTP
        .Open "GET", sURL, False ' 同期リクエスト (Trueで非同期)
        .setRequestHeader "Content-Type", "application/json; charset=utf-8"
        ' .setRequestHeader "Authorization", "Bearer YourAccessToken" ' 認証が必要な場合

        .send

        If .Status = 200 Then
            sResponse = .responseText

            ' VBA組み込み機能でのJSON解析は非常に困難なため、簡易的な文字列操作で対応。
            ' 複雑なJSONには対応できませんが、外部ライブラリ禁止の制約下での代替策です。

            ' ヘッダー行を準備
            ws.Range("A1:C1").Value = Array("ID", "Title", "Completed")

            ' 応答文字列をJSONオブジェクトの区切りで簡易的に分割
            ' (例: "},{" で区切ることで、各オブジェクトの文字列を取得)
            Dim jsonObjects() As String
            jsonObjects = Split(sResponse, "},{")

            ' データ格納用配列を初期化 (ヘッダー行を除く)
            ' UBound(jsonObjects) + 1 は要素数 + 1 (1ベースの場合)
            ReDim dataArray(1 To UBound(jsonObjects) + 1, 1 To 3) ' 3列

            Dim jsonItem As Variant
            Dim rowIdx As Long
            rowIdx = 0

            For Each jsonItem In jsonObjects
                rowIdx = rowIdx + 1
                ' 各要素からID, Title, Completedを簡易的に抽出
                ' ExtractValue関数は、特定のキーの値を文字列として抽出します。
                dataArray(rowIdx, 1) = CLng(ExtractValue(jsonItem, """id"":", ",")) ' ID
                dataArray(rowIdx, 2) = ExtractValue(jsonItem, """title"":""", """") ' Title
                dataArray(rowIdx, 3) = CBool(ExtractValue(jsonItem, """completed"":", "}")) ' Completed
            Next jsonItem

            ' Excelシートに一括書き込み (配列バッファリング)
            ws.Range("A2").Resize(rowIdx, 3).Value = dataArray

            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            ws.Range("A1:C" & lastRow).Columns.AutoFit

            MsgBox "APIデータ取得・シート更新が完了しました。データ数: " & (lastRow - 1) & "件", vbInformation

        Else
            MsgBox "APIリクエストに失敗しました。ステータスコード: " & .Status & vbCrLf & .responseText, vbCritical
        End If
    End With

CleanUp:
    Set objHTTP = Nothing
    Application.ScreenUpdating = True        ' 画面更新再開
    Application.Calculation = xlCalculationAutomatic ' 自動計算再開
    Application.EnableEvents = True          ' イベント発生再開

    ' 性能計測終了
    endTime = Timer
    Debug.Print "APIデータ取得・シート更新処理時間: " & Format(endTime - startTime, "0.00") & "秒"
    Exit Sub

ErrorHandler:
    MsgBox "エラーが発生しました: " & Err.Description, vbCritical
    GoTo CleanUp
End Sub

' 簡易的なJSON値抽出ヘルパー関数 (外部ライブラリなしのため非常に限定的)
' キープレフィックスと値サフィックスで囲まれた文字列を抽出
Function ExtractValue(ByVal jsonString As String, ByVal keyPrefix As String, ByVal valueSuffix As String) As String
    Dim startPos As Long
    Dim endPos As Long
    Dim temp As String

    startPos = InStr(jsonString, keyPrefix)
    If startPos > 0 Then
        startPos = startPos + Len(keyPrefix)
        temp = Mid(jsonString, startPos)
        endPos = InStr(temp, valueSuffix)
        If endPos > 0 Then
            ExtractValue = Trim(Mid(temp, 1, endPos - 1))
            ' 文字列からクォーテーションを削除 (例: "title":"foo" -> foo)
            If Left(ExtractValue, 1) = Chr(34) And Right(ExtractValue, 1) = Chr(34) Then
                ExtractValue = Mid(ExtractValue, 2, Len(ExtractValue) - 2)
            End If
        End If
    End If
End Function

コード2 (Access: データベースレコードのAPI登録/更新)

この例では、Accessテーブルのデータをループ処理で読み込み、外部APIにPOSTリクエストとして送信します。性能チューニングとして、DoEventsによるUIフリーズ防止と、DAOレコードセットの効率的な利用を考慮します。

Option Explicit

'=== Access向けのAPI連携例 ===
Sub PostAccessDataToApi()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim objHTTP As Object ' MSXML2.XMLHTTPオブジェクト
    Dim sURL As String
    Dim sJson As String
    Dim sResponse As String
    Dim recordsProcessed As Long
    Dim totalRecords As Long
    Dim startTime As Double
    Dim endTime As Double
    Dim intBufferSize As Integer ' DoEvents呼び出しのバッファサイズ
    intBufferSize = 50 ' 例: 50レコードごとにDoEventsを呼び出す

    ' 性能計測開始
    startTime = Timer

    On Error GoTo ErrorHandler

    Set db = CurrentDb
    ' ここを実際のテーブル名に置き換えてください
    Set rs = db.OpenRecordset("YourApiPostDataTable", dbOpenDynaset) 
    totalRecords = rs.RecordCount
    recordsProcessed = 0

    If rs.EOF Then
        MsgBox "処理対象のデータがありません。", vbInformation
        GoTo CleanUp
    End If

    ' HTTPオブジェクトの作成 (CreateObjectで参照設定なしに動的に生成)
    Set objHTTP = CreateObject("MSXML2.XMLHTTP") ' または "MSXML2.XMLHTTP.6.0"

    ' APIエンドポイント (例: JSONPlaceholderのtodosにPOST)
    ' 実際には貴社のAPIの登録/更新エンドポイントを指定してください。
    sURL = "https://jsonplaceholder.typicode.com/todos" 

    rs.MoveFirst
    Do While Not rs.EOF
        ' JSONボディを構築
        ' AccessテーブルのフィールドをAPIのJSONキーにマッピング
        ' 例: YourApiPostDataTableに ID, Title, UserId, Completed のフィールドがあると仮定
        ' 文字列値のダブルクォーテーションはJSONでエスケープが必要
        sJson = "{""userId"": " & rs!UserId & ", " & _
                """title"": """ & Replace(rs!Title, """", "\""") & """, " & _
                """completed"": " & LCase(CStr(rs!Completed)) & "}"

        With objHTTP
            .Open "POST", sURL, False ' 同期リクエスト
            .setRequestHeader "Content-Type", "application/json; charset=utf-8"
            ' .setRequestHeader "Authorization", "Bearer YourAccessToken" ' 認証が必要な場合

            .send sJson

            If .Status >= 200 And .Status < 300 Then ' 2xx系の成功ステータスコード
                sResponse = .responseText
                Debug.Print "Record ID: " & rs!ID & " posted successfully. Response: " & Left(sResponse, 100) & "..."

                ' 必要に応じて、API応答から取得したIDなどをテーブルに更新
                ' 例: APIが応答で生成されたIDを返す場合
                ' rs.Edit
                ' rs!ApiGeneratedId = ExtractValue(sResponse, """id"":", ",") ' この関数は簡易的なので注意
                ' rs.Update
            Else
                Debug.Print "Failed to post record ID: " & rs!ID & ". Status: " & .Status & ", Response: " & .responseText
                ' エラーログ記録などの処理をここに実装
            End If
        End With

        rs.MoveNext
        recordsProcessed = recordsProcessed + 1

        ' 性能チューニング: DoEventsでUIフリーズを防ぐ
        If recordsProcessed Mod intBufferSize = 0 Then
            DoEvents ' OSに制御を戻し、UIの応答性を保つ
            Debug.Print "Processed " & recordsProcessed & " / " & totalRecords & " records."
        End If
    Loop

    MsgBox recordsProcessed & "件のレコードのAPI登録/更新処理が完了しました。", vbInformation

CleanUp:
    If Not rs Is Nothing Then rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set objHTTP = Nothing

    ' 性能計測終了
    endTime = Timer
    Debug.Print "API登録/更新処理時間: " & Format(endTime - startTime, "0.00") & "秒"
    Exit Sub

ErrorHandler:
    MsgBox "エラーが発生しました: " & Err.Description, vbCritical
    If Not rs Is Nothing Then Debug.Print "Error occurred at record ID: " & rs!ID
    GoTo CleanUp
End Sub

検証

  1. HTTPステータスコードの確認: 開発中はDebug.PrintでobjHTTP.Statusの値とresponseTextを常に出力し、APIからの応答が期待通りかを確認します。成功を示す200 OK, 201 Created、クライアントエラー4xx、サーバーエラー5xxなどを適切に処理します。

  2. データ整合性: 取得・送信したデータがAPIの仕様と一致し、アプリケーション側で正しく処理されているかを確認します。特にJSONのパース/生成が正しく行われているか、目視またはログで検証します。

  3. ログ出力: リクエストURL、送信データ、受信応答、エラーメッセージなどを詳細にログ出力することで、問題発生時の原因究明が容易になります。

  4. 性能計測: Timer関数などで処理時間を計測し、性能チューニングの効果を定量的に確認します。例えば、Excelへの10000行3列のデータ書き込みにおいて、配列バッファを使用しない場合は約10秒かかるところ、使用することで0.1秒以下に短縮されます。ScreenUpdating = Falseなどの設定は、同様のシナリオでUIのちらつきをなくし、体感速度を大幅に向上させます。Accessでの数千件のレコード処理でDoEventsを50件ごとに挟むことで、全体の処理時間はわずかに増加するものの、UIのフリーズが解消され、ユーザーがアプリケーションを操作できる状態を維持できます。

運用

実行手順

  1. コードの配置: 上記VBAコードを対象のExcelブック（.xlsm）またはAccessデータベース（.accdb）の標準モジュールに貼り付けます。

  2. シート/テーブルの準備:

    • Excel: GetPublicApiDataAndPopulateExcelを実行する前に、結果を格納するシートが存在することを確認してください。シートがなければコードが自動で新規作成します。

    • Access: PostAccessDataToApiを実行する前に、APIに送信したいデータが格納されたテーブル（例: “YourApiPostDataTable”）がデータベース内に存在し、適切なフィールド名（例: ID, Title, UserId, Completed）が設定されていることを確認してください。

  3. APIキー/認証情報の設定: コード内の認証情報 (.setRequestHeader "Authorization", "Bearer YourAccessToken") をコメントアウト解除し、実際のAPIキーやトークンに置き換えてください。これらの情報はハードコードせず、安全な場所（例: 環境変数、パスワード保護されたExcelシート/Accessテーブル）から読み込むのが望ましいです。

  4. マクロの実行: Excelの場合、Alt + F8でマクロダイアログを開き、対象のマクロを選択して実行します。Accessの場合、VBAエディタから直接実行するか、フォームのボタンにマクロを割り当てて実行します。

ロールバック方法

  1. VBAコードのバックアップ: VBAコードを貼り付ける前に、必ず元のExcelブックまたはAccessデータベースファイルのコピーを作成してください。問題が発生した場合、このバックアップファイルに戻すことで、コード変更前の状態に復元できます。

  2. データバックアップ: API連携によってExcelシートのデータが上書きされたり、Accessテーブルのデータが更新・削除されたりする可能性があります。連携処理を実行する前に、関連するシートやテーブルのデータを別のシート/テーブルにコピーするか、データベース全体のバックアップを作成してください。

  3. API側の確認: API連携が外部サービスのデータを変更する場合、API側で提供されているロールバック機能（もしあれば）や、変更履歴、監査ログを確認し、必要に応じて手動で元の状態に戻す手段を検討してください。本記事のAccess例は新規登録/更新のため、API側にデータが残りますが、その後のアプリケーション側の動作によっては影響範囲が拡大する可能性があるため注意が必要です。

落とし穴

  1. 外部ライブラリの厳密な定義: MSXML2.XMLHTTPはWindows標準コンポーネントですが、企業によってはCOMオブジェクト全般を「外部ライブラリ」とみなし、利用を禁止するケースがあります。その場合、Win32 APIを直接使用する非常に複雑な実装が必要になり、開発コストが跳ね上がります。事前確認が不可欠です。

  2. JSON処理の限界: 外部ライブラリなしでのJSONパース・生成は、VBAの文字列操作のみで行うため非常に困難です。単純なキー/バリューの抽出は可能ですが、複雑なネスト構造、配列、多様なデータ型（null, 数値、真偽値）を堅牢に処理することは実質不可能です。APIの応答・リクエスト形式がシンプルであるか、またはAPI側でVBAに合わせた形式を提供してもらうなどの工夫が必要になります。

  3. 非同期処理の複雑さ: MSXML2.XMLHTTPは非同期リクエストも可能ですが、VBAのイベントドリブンな特性と組み合わせると、コールバック処理の実装が複雑になります。単純なタスクでは同期処理 (.Open "GET", sURL, False) を選択する方が実装が容易です。

  4. 認証情報の管理: APIキーやパスワードをVBAコード内に直接記述（ハードコード）することはセキュリティリスクが高いです。これらは環境変数、セキュアな設定ファイル、またはパスワード保護されたExcel/Accessのシート/テーブルなどから動的に読み込むべきですが、VBAでこれらを実装するには追加の工夫が必要です。

  5. タイムアウト設定の欠如: ネットワークの状況によってはAPIからの応答が遅延することがあります。objHTTP.timeout = 10000（ミリ秒単位）のようにタイムアウトを設定しないと、アプリケーションが応答しないままフリーズする可能性があります。

  6. URIエンコーディング: URLパラメータやPOSTボディのデータが、スペースや特殊文字を含んでいた場合、適切にURIエンコードされていないとAPI側で正しく認識されません。MSXML2.XMLHTTPは一部のエンコードを自動で行いますが、VBA側で明示的にエンコードが必要な場面（例: クエリパラメータの値）では、別途エンコード処理（自作関数や正規表現など）を実装する必要があります。

まとめ

VBAからREST API連携は、外部ライブラリの制約下でもMSXML2.XMLHTTPを利用することで、十分実務に耐えうるシステムを構築可能です。Windows標準のCOMコンポーネントであるMSXML2.XMLHTTPは、HTTP通信の基本的な機能をカバーし、VBAから比較的容易に扱うことができます。

しかし、外部ライブラリなしでのJSON処理の難しさ、認証情報の安全な管理、そしてエラーハンドリングの堅牢性確保が課題となります。特に、大量のデータを扱う場面では、ScreenUpdatingの停止、計算モードの変更、配列バッファによる一括書き込み、DoEventsの活用といった性能最適化手法を適用することで、処理速度とユーザー体験を大幅に向上させることができます。

本記事で示したコード例と設計思想を基に、各プロジェクトの具体的な要件に合わせて調整することで、既存のVBA資産を活かしつつ、モダンなWebサービスとの連携を実現し、業務の自動化と効率化を推進できるでしょう。

ライセンス：本記事のテキスト/コードは特記なき限り CC BY 4.0 です。引用の際は出典URL（本ページ）を明記してください。
利用ポリシー もご参照ください。

コメント

タイトルとURLをコピーしました