【実務・中級編】【上級者向け】Word表のデータをJSON形式に変換し、Web API経由でSQL Serverへ直接保存する連携術 – Word VBA解析バイブル

Word VBAを「単なる自動化」から「エンタープライズ・データパイプライン」へ昇華させる設計論

業務自動化の現場で、多くのエンジニアが陥る致命的な罠があります。それは、「Word VBAから直接SQL Serverへ接続しようとする」という、設計思想の欠如です。

ADO（ActiveX Data Objects）を使って直接DBにクエリを投げる手法は、小規模な個人用ツールなら機能するでしょう。しかし、それが企業の基幹システムに関わるものであれば、話は別です。セキュリティ、ネットワークの瞬断、DB接続の管理、そしてVBAの不安定なランタイム――これら全ての脆弱性を、Wordという「ドキュメント作成ツール」に背負わせることになるからです。

真のプロフェッショナルが構築すべきは、「Wordはデータ構造をJSONとしてパッケージ化し、Web APIというゲートウェイへ投げるだけ」という、疎結合なアーキテクチャです。

本記事では、Wordの表データを堅牢に抽出し、JSONへとシリアライズ（直列化）した上で、`WinHttp.WinHttpRequest`を用いてWeb APIへと送信する、プロダクションレベルの連携術を伝授します。

1. アーキテクチャ設計：なぜ「API経由」でなければならないのか

まず、設計思想を整理しましょう。私たちが目指すのは、以下の3層構造です。

1. Presentation Layer (Word VBA): 表データの抽出、バリデーション、JSON化、HTTP送信。
2. Integration Layer (Web API – ASP.NET Core / Python FastAPI 等): 受信データの検証、認証、DBへのルーティング。
3. Data Layer (SQL Server): データの永続化。

この設計がもたらす圧倒的な優位性

• セキュリティ: Word側にDBの接続文字列（ID/PW）を持たせる必要がなくなる。
• 堅牢性: DBのスキーマ変更や接続仕様の変更が、Word側のコードに影響を与えない。
• 保守性: データの整合性チェックを、VBAではなくサーバーサイド（API）で集中管理できる。

2. 実装の核心：堅牢なデータ抽出とJSONシリアライズ

VBAでJSONを手書きで作ろうとして、`”{“”id””: “”” & val & “””}”` のような文字列結合を繰り返すのは、初歩的なミスです。セル内にダブルクォーテーションや改行が含まれた瞬間、生成されるJSONは壊れ、APIはエラーを吐きます。

ここでは、「データ型を意識したオブジェクト指向的なアプローチ」を採用します。

プロダクションコード例

以下のコードは、単なるコピペ用ではありません。オブジェクトのライフサイクルと、エラーハンドリング、そしてJSONの整合性を担保するための設計に基づいています。

# A. データの器となるクラスモジュール (`clsTableData`)

まず、表の1行を表現するクラスを作成します。これにより、データの型定義が明確になります。

' Class Module: clsTableData
Option Explicit

Public ID As String
Public UserName As String
Public Amount As Double
Public EntryDate As String

' JSON形式の文字列を生成するメソッド
' 文字列内のダブルクォーテーションをエスケープする処理を含めるのがプロの流儀
Public Function ToJSON() As String
    Dim json As String
    json = "{" & _
           """id"": """ & EscapeJSON(ID) & """, " & _
           """userName"": """ & EscapeJSON(UserName) & """, " & _
           """amount"": " & Amount & ", " & _
           """entryDate"": """ & EscapeJSON(EntryDate) & """" & _
           "}"
    ToJSON = json
End Function

' JSONエスケープ処理（改行、ダブルクォート等の対策）
Private Function EscapeJSON(ByVal str As String) As String
    Dim res As String
    res = Replace(str, "\", "\\")
    res = Replace(res, """", "\""")
    res = Replace(res, vbCr, "\r")
    res = Replace(res, vbLf, "\n")
    res = Replace(res, vbTab, "\t")
    EscapeJSON = res
End Function

# B. メインロジック (`modMain`)

表の走査、APIへの送信を担当します。

' Standard Module: modMain
Option Explicit

' APIのエンドポイント設定（実際には環境に応じたURLを指定）
Private Const API_ENDPOINT As String = "https://api.your-company.com/v1/data/import"
Private Const API_TOKEN As String = "Bearer YOUR_SECURE_TOKEN"

Public Sub ExportTableToAPI()
    Dim doc As Document
    Dim tbl As Table
    Dim rowIdx As Integer
    Dim dataList As Collection
    Dim item As clsTableData
    Dim jsonPayload As String
    Dim http As Object
    
    Set doc = ActiveDocument
    
    ' 1. 表の存在確認
    If doc.Tables.Count = 0 Then
        MsgBox "対象となる表が見つかりません。", vbCritical
        Exit Sub
    End If
    Set tbl = doc.Tables(1) ' 最初の表を対象とする
    
    Set dataList = New Collection
    
    On Error GoTo ErrorHandler
    
    ' 2. 表データの抽出（ヘッダーを除いた2行目から開始）
    For rowIdx = 2 To tbl.Rows.Count
        Set item = New clsTableData
        
        ' セル内の不要な改行コード(Chr(13) & Chr(7))を除去して取得
        item.ID = CleanCellText(tbl.Cell(rowIdx, 1).Range.Text)
        item.UserName = CleanCellText(tbl.Cell(rowIdx, 2).Range.Text)
        ' 数値変換時のエラーを考慮
        item.Amount = Val(CleanCellText(tbl.Cell(rowIdx, 3).Range.Text))
        item.EntryDate = CleanCellText(tbl.Cell(rowIdx, 4).Range.Text)
        
        dataList.Add item
    Next rowIdx
    
    ' 3. JSON配列の構築
    jsonPayload = "["
    For rowIdx = 1 To dataList.Count
        jsonPayload = jsonPayload & dataList(rowIdx).ToJSON
        If rowIdx < dataList.Count Then jsonPayload = jsonPayload & ","
    Next rowIdx
    jsonPayload = jsonPayload & "]"
    
    ' 4. WinHttpによる非同期的な送信（同期実行だがタイムアウト設定を適切に行う）
    Set http = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    With http
        .Open "POST", API_ENDPOINT, False ' Falseで同期通信
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Authorization", API_TOKEN
        .SetTimeouts 5000, 5000, 10000, 10000 ' Resolve, Connect, Send, Receive (ms)
        
        Debug.Print "Sending Payload: " & jsonPayload
        .Send jsonPayload
        
        ' 5. レスポンスの検証
        Select Case .Status
            Case 200, 201
                MsgBox "データの送信に成功しました。", vbInformation
            Case 400
                MsgBox "リクエストエラー（データ形式不正）: " & .ResponseText, vbExclamation
            Case 401
                MsgBox "認証エラー: APIトークンを確認してください。", vbCritical
            Case Else
                MsgBox "サーバーエラー: " & .Status & " - " & .ResponseText, vbCritical
        End Select
    End With
    
    Exit Sub

ErrorHandler:
    MsgBox "予期せぬエラーが発生しました:" & vbCrLf & Err.Description, vbCritical
End Sub

' セル内の制御文字（末尾の改行マーク）を取り除く補助関数
Private Function CleanCellText(ByVal txt As String) As String
    ' Wordのセル末尾には Chr(13) & Chr(7) が必ず含まれる
    Dim clean As String
    clean = Replace(txt, Chr(13), "")
    clean = Replace(clean, Chr(7), "")
    CleanCellText = Trim(clean)
End Function

3. プロフェッショナルが意識すべき「極限の知見」

コードを書いた後、以下の3点について自問自答してください。これができていなければ、そのコードは「動くが、壊れやすい」コードです。

① セル内の「目に見えない文字」への対処

Wordの表のセル（`Cell`オブジェクト）の`Text`プロパティには、必ず末尾に `Chr(13) & Chr(7)` という制御文字が含まれます。これを単純にJSONに含めると、API側でのパースエラーや、DBへの不要な改行混入を引き起こします。`CleanCellText`のような、データの「洗浄（Sanitization）」工程を必ず挟んでください。

② タイムアウトとリトライ戦略

ネットワークは常に不安定です。`WinHttp`を使用する際は、`SetTimeouts`を明示的に指定してください。デフォルトの挙動に依存するのは、プロフェッショナルの仕事ではありません。また、本番環境では、通信失敗時にユーザーに「再試行」を促す、あるいはログを記録する仕組みが不可欠です。

③ メモリ管理とオブジェクトのライフサイクル

VBAはガベージコレクションが強力ではありません。`Set http = Nothing` や `Set dataList = Nothing` を適切に行う、あるいはスコープを限定することで、メモリリークを防ぎ、大規模な表を処理する際のパフォーマンス低下を回避します。

結論

Word VBAを単なる「マクロ」として扱うか、それとも「堅牢なデータ・エージェント」として設計するか。その差は、「データの型を定義し、通信の境界線を明確に引けるか」にかかっています。

APIという抽象化レイヤーを挟むことで、あなたの作成するツールは、単なる自動化ツールから、企業のデータインフラを支える信頼性の高いシステムへと進化するのです。