Excel VBA 逆引き集 | Office Scripts連動

Excel VBA
スポンサーリンク

ねらい：VBAとOffice Scriptsを連動させ、ローカルとクラウドを使い分ける

VBAはデスクトップExcelでの高速・柔軟な自動化、Office ScriptsはExcel on the web（Microsoft 365）のクラウド連携・Power Automateとの統合が得意です。両者を「役割分担＋呼び出し連携」させると、ローカルで下処理→クラウドで共有・配布、あるいはクラウドで収集→ローカルで重処理など、実務の幅が広がります。ここでは初心者向けに、貼って動かせるテンプレで「設計の型・コード例・連携の勘所」を解説します。

重要ポイントの深掘り

連動の鍵は「インターフェース（契約）の固定」です。データはCSV/JSON/テーブル（ListObject）で受け渡し、関数やAPIの引数は“文字列・数値・日付”に絞ると壊れにくくなります。クラウド側はOffice Scripts、フローはPower Automate、ローカルはVBAという三層構造にすると、責務が明確で保守が容易です。

構成の全体像：三層で役割を分ける

ローカル（VBA）の責務

  • 配列I/Oで高速に前処理・集計・整形
  • CSV/Excelテーブルに出力して「受け渡し」に備える
  • エラー時も環境復帰する開始・終了枠とログを持つ

クラウド（Office Scripts）の責務

  • ブラウザExcelでデータ受け取り（OneDrive/SharePointのファイル）
  • 正規化・テーブル化・共有先保存・メール/Teams通知などの連携
  • Power Automateからのパラメータ受け取りに対応

フロー（Power Automate）の責務

  • トリガー（スケジュール/手動/ファイル更新）でOffice Scriptsを起動
  • 必要ならVBAが生成したCSV/Excelを拾って処理する
  • 例外時の通知と再試行のポリシーを持つ

VBA側テンプレ：前処理とCSV出力の標準型

開始・終了の枠とCSVユーティリティ

' ModApp.bas（開始・終了枠）
Option Explicit
Public Sub AppEnter(Optional ByVal status As String = "")
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    If Len(status) > 0 Then Application.StatusBar = status
End Sub
Public Sub AppLeave()
    Application.StatusBar = False
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub
VB
' ModCsv.bas（CSV I/O：UTF-8）
Option Explicit
Public Sub WriteArrayToCsv(ByVal path As String, ByVal data As Variant)
    Dim st As Object: Set st = CreateObject("ADODB.Stream")
    st.Type = 2: st.Charset = "UTF-8": st.Open
    Dim r As Long, c As Long, line As String
    For r = 1 To UBound(data, 1)
        line = ""
        For c = 1 To UBound(data, 2)
            Dim s As String: s = Replace(CStr(data(r, c)), """", """""")
            line = line & IIf(c > 1, ",", "") & """" & s & """"
        Next
        st.WriteText line & vbCrLf
    Next
    st.SaveToFile path, 2
    st.Close: Set st = Nothing
End Sub
VB

配列I/Oで整形してCSVに渡す入口

' ModService.bas（VBA前処理→CSV出力）
Option Explicit
Public Sub Run_Preprocess_ToCsv()
    On Error GoTo EH
    AppEnter "Preprocess"
    Dim arr As Variant: arr = Worksheets("Input").Range("A1").CurrentRegion.Value
    Dim rows As Long: rows = UBound(arr, 1)
    Dim cols As Long: cols = UBound(arr, 2)

    Dim r As Long, c As Long
    For r = 2 To rows
        For c = 1 To cols
            arr(r, c) = Trim$(CStr(arr(r, c)))
        Next
    Next

    Dim path As String: path = ThisWorkbook.Path & "\out_preprocessed.csv"
    WriteArrayToCsv path, arr
    AppLeave
    MsgBox "CSV出力完了: " & path
    Exit Sub
EH:
    AppLeave
    MsgBox "失敗: " & Err.Description, vbExclamation
End Sub
VB

重要ポイントの深掘り

VBAは「速く・確実に」前処理を終え、クラウドに渡しやすい形（UTF-8 CSV or テーブル）で出力します。開始・終了枠で環境復帰を保証し、“対話不要”だと外部連携（スクリプトやフロー）に使いやすくなります。

Office Scripts側テンプレ：CSV/テーブルの受け取りと正規化

基本のシグネチャとテーブル操作

Office ScriptsはTypeScriptで記述します。workbook、worksheets、tablesを使って表操作します。

// processCsvToTable.ts
function main(workbook: ExcelScript.Workbook, inputSheetName: string, csvText: string) {
  const ws = workbook.getWorksheet(inputSheetName) ?? workbook.addWorksheet(inputSheetName);
  ws.getRange("A:Z").clear();

  // CSVを2次元配列へ（簡易パーサ）
  const lines = csvText.split(/\r?\n/).filter(l => l.length > 0);
  const head = parseCsvLine(lines[0]);
  const rows = [head];
  for (let i = 1; i < lines.length; i++) rows.push(parseCsvLine(lines[i], head.length));

  const height = rows.length;
  const width = head.length;
  ws.getRangeByIndexes(0, 0, height, width).setValues(rows);

  // テーブル化（列名はヘッダーから）
  const table = workbook.addTable(`${inputSheetName}!A1:${String.fromCharCode(64 + width)}${height}`, true);
  table.setName("tblImported");

  // 簡易正規化（トリム）
  rows.forEach((r, ri) => {
    if (ri === 0) return;
    for (let ci = 0; ci < r.length; ci++) {
      r[ci] = String(r[ci] ?? "").trim();
    }
  });
  ws.getRangeByIndexes(1, 0, height - 1, width).setValues(rows.slice(1));
}

function parseCsvLine(line: string, width?: number): string[] {
  const res: string[] = [];
  let buf = "", inQ = false;
  for (let i = 0; i < line.length; i++) {
    const ch = line[i];
    if (ch === '"') {
      if (inQ && line[i + 1] === '"') { buf += '"'; i++; } else { inQ = !inQ; }
    } else if (ch === ',' && !inQ) {
      res.push(buf); buf = "";
    } else buf += ch;
  }
  res.push(buf);
  if (width && res.length < width) { while (res.length < width) res.push(""); }
  return res;
}
TypeScript

Power Automateからの呼び出し想定（パラメータ受け取り）

  • Power Automateで「Office Scripts を実行」アクションを作り、inputSheetName（文字列）とcsvText（ファイル内容文字列）を渡す。
  • csvTextは「ファイルコンテンツを取得」で読み、文字列としてスクリプトへ渡す。

重要ポイントの深掘り

Office Scriptsは「表・テーブル操作」に強い一方、ファイルI/OはPower Automateと組むのが定石です。受け渡しは“テキスト（CSV/JSON）”にするか、“Excelファイルを開いてテーブル操作”を選びます。どちらもヘッダー行を基準にすれば、列崩れに強くなります。

連動の実例：VBA→CSV→Power Automate→Office Scripts→共有保存

フローの流れ

  • ローカルで Run_Preprocess_ToCsv を実行（CSV生成）
  • OneDrive/SharePointのフォルダへCSVを配置（手動同期 or 自動配置）
  • Power Automateが「新しいファイル（またはスケジュール）」で起動
  • 「ファイルコンテンツを取得」→「Office Scriptsを実行（csvText引数で渡す）」
  • スクリプトがテーブルに整形→「ファイルを保存／通知」

チェックポイント

  • 文字コード（UTF-8）一致
  • ヘッダー行が必ず存在
  • 列名で操作（位置に依存しない設計）
  • 失敗時の再試行と通知（Power Automate側）

VBAからOffice Scriptsを“間接的”に呼ぶ設計

直接は呼べないため「トリガーファイル」で連携する

  • VBAが「要求ファイル（JSON/CSV）」を指定フォルダに出力
  • Power Automateが「ファイル作成」をトリガーにOffice Scriptsを起動
  • 処理後は「完了ファイル／ログファイル」を残し、VBA側が次のステップへ進む
' ModSignal.bas（トリガーファイルで連携）
Option Explicit
Public Sub WriteTriggerJson(ByVal path As String, ByVal job As String, ByVal dateKey As String)
    Dim s As String
    s = "{""job"":""" & job & """,""date"":""" & dateKey & """}"
    Dim st As Object: Set st = CreateObject("ADODB.Stream")
    st.Type = 2: st.Charset = "UTF-8": st.Open
    st.WriteText s: st.SaveToFile path, 2: st.Close: Set st = Nothing
End Sub
VB

重要ポイントの深掘り

VBAとOffice Scriptsは実行環境が違うため、ファイルトリガーかPower Automateが“橋”になります。契約（JSONキー、CSVヘッダー）を固定し、双方向の状態（完了印）をファイルでやり取りすると、疎結合で壊れにくくなります。

Office Scriptsで集計・出力を行うテンプレ

テーブルから合計・フラグ列を追加して保存する

// summarizeAndSave.ts
function main(workbook: ExcelScript.Workbook, tableName: string, outSheetName: string) {
  const table = workbook.getTable(tableName);
  if (!table) throw new Error(`Table not found: ${tableName}`);

  const headers = table.getHeaderRowRange().getValues()[0];
  const idxScore = headers.indexOf("Score");
  if (idxScore < 0) throw new Error("Score列がありません");

  const body = table.getRangeBetweenHeaderAndTotal().getValues();
  let sum = 0;
  for (let r = 0; r < body.length; r++) {
    const v = Number(body[r][idxScore] ?? 0);
    sum += v;
    const pass = v >= 70 ? "○" : "×";
    body[r].push(pass);
  }

  const outWs = workbook.getWorksheet(outSheetName) ?? workbook.addWorksheet(outSheetName);
  const outRange = outWs.getRange("A1").getResizedRange(body.length + 1, headers.length + 1);
  outRange.clear();
  outRange.getCell(0, 0).setValue(headers.concat(["Pass"]));
  outRange.getOffsetRange(1, 0).setValues(body);

  const msg = `Count=${body.length}, Sum=${sum}`;
  outWs.getRange("A" + (body.length + 3)).setValue(msg);
}
TypeScript

重要ポイントの深掘り

Office Scriptsは「列名で操作」できるため、列順変更に強く、業務の変化に耐えます。重処理はVBAで、共有・通知・最終整形はOffice Scriptsで——という使い分けが現場では相性が良いです。

落とし穴と対策（深掘り）

権限と場所の不一致

OneDrive/SharePointのパス権限がないとフローが失敗します。保存先と実行ユーザーの権限を事前に整えること。共有リンクではなく“ドキュメントライブラリのパス”を使います。

文字コード・区切りのズレ

CSVはUTF-8・ダブルクォートエスケープに統一。Office Scripts側のパーサは“必ずヘッダーの列数に合わせる”ことで崩れに強くします。

同時実行・重複処理

トリガーファイルにロック印（例：拡張子 .lock）やジョブIDを付与して、同時実行を避けます。Power Automate側は“同時実行の防止”設定を有効化すると安全です。

導入手順と検証の道筋

最初の一歩

  • VBAで Run_Preprocess_ToCsv を実行し、CSVが生成されるかを確認
  • Office Scriptsの processCsvToTable を作成し、Power AutomateからcsvTextを渡してテーブル化
  • summarizeAndSave を続けて呼び、出力シートに合計・フラグが出るかを確認

次の拡張

  • トリガーファイル（JSON）の契約を固め、VBA→Power Automate→Office Scriptsの三層連携を定着
  • 失敗時の通知（メール/Teams）と再試行ポリシーをフローへ設定
  • 処理ごとにログを残し、現場の追跡可能性を確保

まとめ：契約を固定し、VBAは速度・Office Scriptsは連携で役割分担する

VBAで配列I/Oによる高速前処理、Office Scriptsでクラウド連携・共有・テーブル整形、Power Automateでその橋渡し。この三位一体を「CSV/JSON＋ヘッダー契約」でつなげば、壊れにくく、変更に強い連動ができます。

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