Excel VBA 逆引き集 | Power Automate連携

Excel VBA
スポンサーリンク

ねらい：VBAとPower Automateをつないで「人手いらずの定時運用」を実現する

Power Automateはクラウド側の「起動・連携・通知」を担い、VBAはローカルExcelの「高速前処理・整形・集計」を担います。役割分担を決め、CSVやExcelファイルを契約（ヘッダー・文字コード・場所）で受け渡すと、夜間・定時・イベント駆動で安定して回せます。初心者でも貼って動くように、VBAの入口テンプレ、Power Automateの流れ、連携の落とし穴と対策まで、例題でかみ砕いて解説します。

重要ポイントの深掘り

最初に「契約を固定」します。受け渡しはUTF-8 CSVかExcelテーブル、必ずヘッダー行を含めます。Power Automateは「トリガー→ファイル取得→Office ScriptsやVBA起動→出力保存→通知」の型に揃え、VBAは「開始・終了枠＋配列I/O＋Fail Fast」で対話なしに完了させます。これで壊れにくく、再試行も容易になります。

構成の全体像：三位一体で運用する

連携の役割分担

  • ローカルVBA：大量データの前処理・整形・集計。結果をCSV／Excelに出力。
  • Power Automate：トリガー（スケジュール／ファイル作成／手動）で起動し、ファイルの取得や保存、通知を担当。
  • Office Scripts（任意）：クラウドでのテーブル化・共有ブック更新・軽い整形や集計。Power Automateから直接呼べます。

連携のデータ契約

  • 文字コードはUTF-8（CSV）。Excelはテーブル化（ListObject）で列名ベース操作。
  • ヘッダーは必須。列名でIndexを取る設計にし、列順変更に強くします。
  • 受け渡し場所はOneDrive/SharePointの既定フォルダに固定。権限と同期の前提を最初に決めます。

VBA側テンプレ：前処理と出力の入口を作る

開始・終了枠とCSV I/O

' 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出力：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(ByVal outCsvPath As String)
    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

    WriteArrayToCsv outCsvPath, arr
    AppLeave
    Exit Sub
EH:
    AppLeave
End Sub
VB

重要ポイントの深掘り

外部起動に耐えるため、MsgBoxなどの対話を排します。引数で出力パスを受け取り、失敗時もAppLeaveで確実に復帰させます。処理は配列I/Oに統一し、セル往復をなくすと数万行でも安定します。

Power Automateの基本フロー：スケジュール→ファイル→スクリプト→通知

例題フローの設計（クラウド中心）

  • スケジュールトリガー（毎日 02:00）で起動。
  • OneDrive/SharePointから「入力CSVを取得」。
  • Office Scripts「processCsvToTable」を実行し、テーブル化・正規化・別シートへ集計（必要なら）。
  • 処理結果を「共有ブックへ保存」し、Teams/メールで通知。

例題フローの設計（ローカルVBA連携）

  • スケジュールトリガーで起動。
  • PowerShellアクションでローカルExcelをヘッドレス起動し、Application.RunでVBA入口（Run_Preprocess_ToCsv）を実行。出力をOneDriveフォルダへ保存。
  • その後Office Scriptsを呼び、クラウド側で共有・最終整形・通知を実施。

Power AutomateからOffice Scriptsを呼ぶテンプレ

Office Scriptsの最小例（CSVテキストをテーブル化）

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

  const lines = csvText.split(/\r?\n/).filter(l => l.length > 0);
  const head = parseCsv(lines[0]);
  const rows = [head];
  for (let i = 1; i < lines.length; i++) rows.push(parseCsv(lines[i], head.length));

  ws.getRangeByIndexes(0, 0, rows.length, head.length).setValues(rows);
  const table = workbook.addTable(`${sheetName}!A1:${String.fromCharCode(64 + head.length)}${rows.length}`, true);
  table.setName("tblImported");
}
function parseCsv(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側の接続ポイント

  • 「ファイルコンテンツを取得」でCSVを文字列に。
  • 「Office Scriptsを実行」で引数 sheetName と csvText を渡す。
  • 完了後「ファイルを保存」「通知」を続ける。

重要ポイントの深掘り

Power AutomateはファイルI/Oに強いので、Office Scriptsへはテキストやブック参照を渡せばよいです。列名（ヘッダー）基準のテーブル化をすれば、列順変更に強く、後工程が壊れません。

Power AutomateからローカルVBAを起動するテンプレ

PowerShellでExcelをヘッドレス起動してRunを呼ぶ

# run_vba.ps1（Power Automateの「スクリプトを実行」から呼ぶ）
param(
  [string]$bookPath,
  [string]$outCsvPath
)

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false

$wb = $xl.Workbooks.Open($bookPath)
$xl.Run("Book1.xlsm!Run_Preprocess_ToCsv", $outCsvPath)
$wb.Close($true)
$xl.Quit()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($wb) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xl) | Out-Null
[GC]::Collect(); [GC]::WaitForPendingFinalizers()

重要ポイントの深掘り

Power Automateのクラウドフローから直接ローカルExcelを起動するには、オンプレミスデータゲートウェイや自動化PCなどの前提が必要です。実行環境を決め、パス・権限・タイムゾーンを統一します。終了時のQuitとCOM解放は必須です。

例題の通し方：VBA→OneDrive→Scripts→通知

手順の流れ

  • Excelで Run_Preprocess_ToCsv を実行し、out_preprocessed.csv をOneDriveの指定フォルダへ置く。
  • Power Automateフローを「新規ファイル作成」でトリガー。
  • 「ファイルコンテンツを取得」→「Office Scriptsを実行」でテーブル化。
  • 処理結果を共有ブックへ保存し、Teamsへ「完了」通知。

確認ポイント

  • CSVがUTF-8であること（文字化けしない）。
  • ヘッダーに必須列が存在すること（列名でIndexを取る）。
  • フローの失敗時に「再試行」が設定され、通知が出ること。

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

権限・場所の不一致

SharePoint／OneDriveのパスは「ドキュメントライブラリの内部パス」を使います。共有リンクや個人OneDriveは権限差で失敗しがち。実行ユーザーと保存先の権限を先に整えます。

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

CSVはUTF-8に固定し、ダブルクォートでエスケープします。Office Scripts側はヘッダー列数に合わせて不足セルを空文字で補うと、行崩れが止まります。

同時実行・二重起動

Power Automateの「同時実行の防止」をオンにし、ジョブIDやロックファイル（.lock）を使って重複処理を抑えます。VBA側の入口でも簡易ロック（名前定義）を組み込むとさらに安全です。

対話ダイアログによる停止

VBAはDisplayAlerts=Falseでダイアログ抑止。保存はSaveCopyAsやCSV出力など「ダイアログ不要の経路」に統一します。

拡張テンプレ：ヘッダー検証・サマリ生成・通知

ヘッダー検証でFail Fastにする

' ModHeaderCheck.bas
Option Explicit
Public Sub RequireHeaders(ByVal data As Variant, ByVal expected As Variant)
    Dim i As Long: For i = LBound(expected) To UBound(expected)
        Call IndexByHeader(data, CStr(expected(i)))
    Next
End Sub
Public Function IndexByHeader(ByVal data As Variant, ByVal headerName As String) As Long
    Dim j As Long
    For j = 1 To UBound(data, 2)
        If StrComp(CStr(data(1, j)), headerName, vbTextCompare) = 0 Then IndexByHeader = j: Exit Function
    Next
    Err.Raise 9100, , "ヘッダーがありません: " & headerName
End Function
VB

Office Scriptsで状態サマリと通知本文生成

// summarizeToSheet.ts
function main(workbook: ExcelScript.Workbook, tableName: string, outSheet: string) {
  const table = workbook.getTable(tableName);
  const headers = table.getHeaderRowRange().getValues()[0];
  const idxStatus = headers.indexOf("Status");
  const body = table.getRangeBetweenHeaderAndTotal().getValues();
  let todo=0, doing=0, blocked=0, done=0;
  for (const r of body) {
    const s = String(r[idxStatus] ?? "").toUpperCase();
    if (s === "TODO") todo++; else if (s === "DOING") doing++; else if (s === "BLOCKED") blocked++; else if (s === "DONE") done++;
  }
  const ws = workbook.getWorksheet(outSheet) ?? workbook.addWorksheet(outSheet);
  ws.getRange("A1").setValue("TODO");   ws.getRange("B1").setValue(todo);
  ws.getRange("A2").setValue("DOING");  ws.getRange("B2").setValue(doing);
  ws.getRange("A3").setValue("BLOCKED");ws.getRange("B3").setValue(blocked);
  ws.getRange("A4").setValue("DONE");   ws.getRange("B4").setValue(done);
}
TypeScript

導入手順のガイド

最初の一歩

  • VBAの入口（Run_Preprocess_ToCsv）を作り、OneDriveフォルダにCSVを出す。
  • Power Automateで「新規ファイル作成」トリガーのフローを作り、Office Scriptsでテーブル化する。
  • 成果物（共有ブック更新・通知）まで一連の動作を確認する。

次の拡張

  • スケジュールトリガーに切り替え、ローカルVBA起動をPowerShellから行う。
  • ヘッダー検証・再試行・通知のテンプレを入れて、運用品質を揃える。
  • 処理の一部をOffice Scripts（列名操作・共有保存）へ寄せ、クラウドの利点を活用する。

まとめ：契約を決め、VBAは速度・Power Automateは連携で「止まらない流れ」を作る

VBAは配列I/Oと開始・終了枠で高速・確実に前処理し、Power Automateはトリガーとクラウド連携、通知で回す。受け渡しはUTF-8 CSV／テーブルのヘッダー契約に固定し、同時実行防止とFail Fastを備える。これだけで、定時運用やイベント駆動の自動化が「壊れず、追跡しやすく、拡張しやすい」形になります。

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