VBAでPower Queryを更新すると固まる・エラーになる原因と対処法【完全ガイド】

目次

• 1. はじめに：VBA×Power Queryの自動化で立ちはだかる壁
• 2. VBAでPower Queryを更新すると固まる・エラーになる4つの原因
  • 2-1. 【最大の原因】「バックグラウンド更新」による非同期処理の衝突
  • 2-2. 膨大なデータ処理と「再計算」の連鎖によるメモリ不足
  • 2-3. プライバシーレベルや資格情報のダイアログが裏で待機している
  • 2-4. 複数のクエリ間の依存関係による更新順序の矛盾
• 3. 【手動設定】バックグラウンド更新を無効にしてエラーを防ぐ手順
  • 3-1. クエリのプロパティから設定を変更する
• 4. 【VBA制御】マクロで安全にPower Queryを更新する基礎テクニック
  • 4-1. SleepやWait関数で待機するのはNGな理由
  • 4-2. VBAで接続設定を上書きし、バックグラウンド更新を強制オフにする
• 5. フリーズ（応答なし）を回避するためのパフォーマンス最適化
  • 5-1. 更新中の「画面更新」と「自動計算」を停止する
  • 5-2. プライバシーレベルの設定を「無視」にしてダイアログを防ぐ
• 6. 【コピペ推奨】Power Queryを絶対に固まらせない完全版VBAコード
  • 6-1. 実戦投入用：全自動・安全更新マクロのコード
  • 6-2. VBAコードの詳細な解説とカスタマイズ方法
• 7. それでもエラーになる・遅い場合の最終チェック項目
  • 7-1. クエリ自体のパフォーマンス（Table.Bufferの活用など）
  • 7-2. 32ビット版Excelのメモリ上限（2GBの壁）
• 8. まとめ：正しいVBAの書き方でPower Queryの真の力を引き出そう

1. はじめに：VBA×Power Queryの自動化で立ちはだかる壁

複数のCSVデータを統合したり、Webやデータベースから最新の情報を取得してクレンジングしたりする際、Excelの「Power Query（パワークエリ）」は魔法のような威力を発揮します。さらに、そのPower Queryの更新作業をVBA（マクロ）を使って自動化すれば、ボタンを一つ押すだけで「データの取得→加工→レポートの出力」までを完全に無人化することができます。

しかし、VBAで ActiveWorkbook.RefreshAll などのコードを書いてPower Queryを更新しようとした途端、多くのユーザーが深刻なトラブルに直面します。
「マクロを実行するとExcelが応答なしになって完全に固まる（フリーズする）」
「更新が終わっていないのに次のVBAの処理が進んでしまい、データが存在しないというエラーが出る」
「手動で『すべて更新』を押した時はうまくいくのに、VBAから実行した時だけ失敗する」

これらの現象は、ExcelのバグやあなたのPCのスペック不足が原因ではありません。ExcelとPower Query、そしてVBAがどのように連携して動いているかという「仕様」を正しく理解していないために発生しています。

本記事では、VBAでPower Queryを更新する際に発生するフリーズやエラーの根本的な原因を解明し、手動での設定変更から、VBAコードを使った完璧な制御方法までを徹底的に解説します。最後には、実務でそのままコピー＆ペーストして使える「絶対に固まらない・エラーを起こさない安全な更新VBAコード」も公開しています。この記事を読めば、Power Queryを用いた自動化システムの安定性が劇的に向上するはずです。

2. VBAでPower Queryを更新すると固まる・エラーになる4つの原因

問題を解決するためには、まず「なぜVBAから実行した時だけトラブルが起きるのか」という原因を知る必要があります。主な原因は以下の4つに分類されます。

2-1. 【最大の原因】「バックグラウンド更新」による非同期処理の衝突

VBAからPower Queryを更新してエラーになる原因の90%以上がこれです。
ExcelのPower Query（および外部データ接続）は、デフォルトで「バックグラウンドで更新する」という設定がオンになっています。これは、データ更新という時間のかかる処理を裏側で行いながら、ユーザーが表の編集など別の作業を続けられるようにするための親切な機能です。

しかし、VBAにとってはこれが致命的な罠になります。VBAで RefreshAll を実行すると、VBAは「更新を開始しろ」という命令だけを出して、更新の完了を待たずにすぐ次の行のコードを実行してしまいます。
結果として、Power Queryが裏で必死にデータを読み込んでいる最中に、VBAが「新しく読み込まれたデータを別シートにコピーする」「ファイルをPDF化して保存する」といった処理を行おうとし、「データがまだ無い」「オブジェクトがロックされている」として実行時エラーを吐いて停止するのです。また、VBAの処理とPower Queryの処理がリソースを取り合うことで、Excelがデッドロック状態に陥りフリーズすることもあります。

2-2. 膨大なデータ処理と「再計算」の連鎖によるメモリ不足

Power Queryで数十万行のデータを読み込む際、そのデータを元にしたピボットテーブルや、VLOOKUP、SUMIFSなどの複雑な数式がシート上に大量に存在すると、データが1行更新されるたびにExcelが画面の描画と数式の再計算を行おうとします。
VBAからの更新実行時にこの「更新」と「再計算」が同時並行で走ると、CPUとメモリのリソースが一瞬で枯渇し、「応答なし」の状態に陥ります。

2-3. プライバシーレベルや資格情報のダイアログが裏で待機している

新しいデータソースに接続した際や、ファイルパスが変更された際、Power Queryは「このデータソースにアクセスするための認証情報（ID/パスワード）を入力してください」あるいは「プライバシーレベルを設定してください」という確認のポップアップダイアログを表示します。
VBAで処理を全自動化している最中にこのダイアログの表示要求が発生すると、ダイアログがExcelの画面の裏側に隠れてしまったり、VBAの実行制御と競合したりして、ユーザーからは「何も操作できず完全にフリーズした」ように見えてしまいます。

2-4. 複数のクエリ間の依存関係による更新順序の矛盾

Power Query内に「クエリA」の結果を参照して「クエリB」を作成しているといった依存関係がある場合、本来はA→Bの順で更新されなければなりません。しかし、VBAで単にすべてをバックグラウンド更新しようとすると、タイミングによってはクエリBが古いクエリAのデータを参照したまま処理を終えてしまう、あるいは循環参照のような状態に陥ってエラーが発生することがあります。

3. 【手動設定】バックグラウンド更新を無効にしてエラーを防ぐ手順

前述の通り、諸悪の根源である「バックグラウンド更新」を無効化（オフ）にすることで、VBAは「Power Queryの更新が完全に終わるまで待機してから、次のコードへ進む」ようになります（同期処理）。まずはExcelの画面上から手動で設定を変更する方法を解説します。

3-1. クエリのプロパティから設定を変更する

1. Excelの上部メニューから「データ」タブを選択します。
2. 「クエリと接続」をクリックし、画面右側に「クエリと接続」ペインを表示させます。
3. 対象となるクエリの上で右クリックし、「プロパティ」を選択します。
4. 「接続のプロパティ」というダイアログボックスが表示されます。
5. 「使用法」タブの中にある、「バックグラウンドで更新する」のチェックを外します。
6. 「OK」をクリックして閉じます。

ファイル内に複数のクエリが存在する場合は、すべてのクエリ（接続）に対してこの操作を行う必要があります。これにより、VBAで ActiveWorkbook.RefreshAll を実行した際、すべてのクエリの更新が完了するまでVBAの処理が一時停止し、エラーやフリーズを確実に防ぐことができます。

4. 【VBA制御】マクロで安全にPower Queryを更新する基礎テクニック

手動で「バックグラウンド更新」のチェックを外す方法は確実ですが、「クエリが20個もあって一つずつ設定するのが面倒」「他の人が新しいクエリを追加した時に設定を忘れてエラーになる」といった運用上の課題が残ります。
そこで、VBAのコード自身に「バックグラウンド更新を一時的に無効にしてから更新を実行する」という命令を組み込むのが、プロの自動化アプローチです。

4-1. SleepやWait関数で待機するのはNGな理由

よくある誤った対処法として、VBAの Application.Wait やWindows APIの Sleep 関数を使って、「更新が終わるまで適当に10秒くらい待機させる」というコードを書く人がいます。

' 【NG例】時間を指定して待機する方法
ActiveWorkbook.RefreshAll
Application.Wait Now + TimeValue("00:00:10") ' 10秒待つ
' 次の処理...

この方法は絶対に避けてください。データ量が少ない日は5秒で終わるかもしれませんが、ネットワークが混雑していて更新に15秒かかった日は、待機時間が足りずに結局エラーになります。逆に、1秒で終わる処理に対しても毎回10秒待つことになり、作業効率が著しく低下します。

4-2. VBAで接続設定を上書きし、バックグラウンド更新を強制オフにする

正しいアプローチは、ブック内に存在するすべての「データ接続」をループ処理で取得し、VBA側からプロパティを書き換えてバックグラウンド更新を無効化（False）にした上で、更新コマンドを発行することです。

Dim conn As WorkbookConnection
' ブック内のすべての接続をループ
For Each conn In ActiveWorkbook.Connections
    If conn.Type = xlConnectionTypeOLEDB Then
        ' OLEDB接続（Power Queryなど）のバックグラウンド更新を無効化
        conn.OLEDBConnection.BackgroundQuery = False
    ElseIf conn.Type = xlConnectionTypeODBC Then
        ' ODBC接続の場合も無効化
        conn.ODBCConnection.BackgroundQuery = False
    End If
Next conn

' すべての接続のバックグラウンド更新をオフにした状態で更新を実行
ActiveWorkbook.RefreshAll
' ※この行に到達した時点で、すべての更新は完全に終了していることが保証されます。

このコードを組み込めば、誰がどんな設定のクエリを追加しようとも、実行時には必ず同期処理（待機状態）で更新が行われるようになります。

5. フリーズ（応答なし）を回避するためのパフォーマンス最適化

バックグラウンド更新の問題を解決しても、データ量や数式が多すぎるためにExcel自体が悲鳴を上げてフリーズしてしまうことがあります。これを防ぐための追加設定をVBAに組み込みます。

5-1. 更新中の「画面更新」と「自動計算」を停止する

VBAの処理を高速化するための基本中の基本ですが、Power Queryの更新時にも絶大な効果を発揮します。
データが数万行単位で書き換わる際、Excelはその変化を画面に描画し、関連する数式をすべて再計算しようとします。この処理を一時的に停止させることで、メモリのパンクを防ぎます。

' 画面の描画を停止
Application.ScreenUpdating = False

' 数式の自動計算を手動に変更
Application.Calculation = xlCalculationManual

' イベントの発生を停止（Worksheet_Changeなどが暴発するのを防ぐ）
Application.EnableEvents = False

更新が完了した後に、これらの設定を必ず元の状態（True および xlCalculationAutomatic）に戻すことを忘れないでください。戻し忘れると、Excel上で関数を入力しても結果が反映されなくなってしまいます。

5-2. プライバシーレベルの設定を「無視」にしてダイアログを防ぐ

「原因2-3」で触れたプライバシーレベルの警告ダイアログによって処理が止まるのを防ぐためには、Excelのオプション設定を変更しておく必要があります。これはVBAコードから直接変更することが難しいため、事前の設定が必須です。

1. 「データ」タブ ＞ 「データの取得」 ＞ 「クエリ オプション」を開きます。
2. 左側メニューの「グローバル」の下にある「プライバシー」を選択します。
3. プライバシーレベルの項目で、「常にプライバシー レベルの設定を無視する」を選択します。
4. 「現在のブック」の下にある「プライバシー」も選択し、「プライバシー レベルを無視し、パフォーマンスを向上させる」にチェックを入れます。
5. 「OK」をクリックします。

これにより、複数の異なるデータソース（ローカルファイルとWebデータなど）を結合する際のファイアウォール的な確認処理がスキップされ、VBA実行中にダイアログで止まる現象を回避できます（※機密性の高いデータを扱う場合は、セキュリティ要件に反しないか社内ルールを確認してください）。

6. 【コピペ推奨】Power Queryを絶対に固まらせない完全版VBAコード

これまで解説してきた「バックグラウンド更新の強制無効化」「画面更新の停止」「自動計算の停止」に加え、万が一エラーが発生した際に設定を確実に戻すための「エラーハンドリング」をすべて盛り込んだ、プロ仕様の堅牢なVBAコードを紹介します。実務ではこのコードをコピーして使用してください。

6-1. 実戦投入用：全自動・安全更新マクロのコード

Sub SafeRefreshPowerQuery()
    Dim conn As WorkbookConnection
    Dim originalCalcMode As XlCalculation

    ' エラーが発生した場合は ErrorHandler ラベルに飛ぶ
    On Error GoTo ErrorHandler

    ' 現在の計算モードを記憶しておく
    originalCalcMode = Application.Calculation

    ' --- 【パフォーマンス最適化】 ---
    Application.ScreenUpdating = False      ' 画面更新を停止
    Application.Calculation = xlCalculationManual ' 数式の自動計算を停止
    Application.EnableEvents = False        ' イベントを停止

    ' ステータスバーに進捗を表示
    Application.StatusBar = "Power Queryのデータを更新中です。しばらくお待ちください..."

    ' --- 【バックグラウンド更新の無効化】 ---
    For Each conn In ActiveWorkbook.Connections
        If conn.Type = xlConnectionTypeOLEDB Then
            conn.OLEDBConnection.BackgroundQuery = False
        ElseIf conn.Type = xlConnectionTypeODBC Then
            conn.ODBCConnection.BackgroundQuery = False
        End If
    Next conn

    ' --- 【データ更新実行】 ---
    ' バックグラウンド更新がFalseなので、完全に処理が終わるまでここで待機する
    ActiveWorkbook.RefreshAll

    ' 更新後に計算を強制実行して最新の数式結果を反映させる
    Calculate

    ' --- 【終了処理】 ---
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = originalCalcMode
    Application.EnableEvents = True

    MsgBox "データの更新が正常に完了しました。", vbInformation, "処理完了"
    Exit Sub

ErrorHandler:
    ' 万が一エラーで停止した場合でも、Excelの設定を元の状態に復旧させる
    Application.StatusBar = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

    MsgBox "データ更新中にエラーが発生しました。" & vbCrLf & _
           "エラー番号: " & Err.Number & vbCrLf & _
           "詳細: " & Err.Description, vbCritical, "エラー"
End Sub

6-2. VBAコードの詳細な解説とカスタマイズ方法

• On Error GoTo ErrorHandler：途中でネットワーク切断などの予期せぬエラーが起きた場合、処理が中断してしまいます。その際、Application.Calculation = xlCalculationManual（手動計算）のまま放置されると、ユーザーがパニックになります。エラーハンドリングを入れることで、異常終了時でも必ず設定を元に戻す（復旧する）ように設計しています。
• originalCalcMode：ユーザーが普段から手動計算モードで使っている可能性も考慮し、マクロ実行前の計算モードを変数に記憶させ、処理完了後にその状態に戻すという丁寧な作りになっています。
• Calculateコマンド：RefreshAll でPower Queryの表が書き換わった後、計算モードが手動になっているとシート上のVLOOKUPなどの結果が更新されません。そのため、マクロの内部で一度強制的に計算（Calculate）を実行し、データの整合性を担保しています。

この SafeRefreshPowerQuery を実行した後に、別ファイルの保存やPDF出力のコードを書き足せば、途中でエラーになることなく完璧な自動化が実現できます。

7. それでもエラーになる・遅い場合の最終チェック項目

上記の最強VBAコードを使ってもまだExcelが固まって落ちる、あるいは数時間経っても処理が終わらない場合、もはやVBAの制御の問題ではなく、Power Queryの設計やPCのスペック環境に限界が来ています。

7-1. クエリ自体のパフォーマンス（Table.Bufferの活用など）

Power Queryのステップ（加工手順）が非効率に作られていると、処理に天文学的な時間がかかります。特に、複数の巨大なテーブルを「マージ（結合）」したり、カスタム列で複雑な条件分岐を行ったりしている場合です。
マージ処理が重い場合は、詳細エディター（M言語）でマージされる側のテーブルに Table.Buffer() という関数を噛ませることで、データを一度メモリ上に展開（キャッシュ）し、結合速度を数百倍に高速化できるケースがあります。

7-2. 32ビット版Excelのメモリ上限（2GBの壁）

お使いのExcelが「32ビット版」の場合、Excelというアプリケーションが使用できるメモリの上限は約2GBに制限されています。数百MBある複数のCSVファイルをPower Queryで読み込んで結合しようとすると、あっという間に2GBの壁に到達し、「メモリ不足」で強制終了（クラッシュ）してしまいます。

Excelの「ファイル」＞「アカウント」＞「Excelのバージョン情報」をクリックして確認し、もし32ビット版を使用している場合は、社内のシステム部門に相談して「64ビット版」のOfficeを再インストールすることを強く推奨します。64ビット版であれば、PCに搭載されているメモリ（8GBや16GBなど）をフルに活用できるため、フリーズの発生率が激減します。

8. まとめ：正しいVBAの書き方でPower Queryの真の力を引き出そう

VBAを利用してPower Queryを更新する際に発生する「固まる・エラーになる」トラブルについて、その原因と完全な対処法を解説しました。内容を振り返ります。

• 最大の原因：Power Queryの「バックグラウンド更新」が有効なため、VBAが更新の完了を待たずに次の処理へ進んでしまうこと。
• NGな対処法：VBAの Wait や Sleep で数秒間待機させる方法は、処理時間のブレに対応できないため使ってはいけない。
• 正しい対処法：VBAのコード内で BackgroundQuery = False を指定し、更新処理を同期的に実行させること。
• フリーズ対策：同時に ScreenUpdating（画面更新）と Calculation（自動計算）を一時停止することで、メモリのパンクを防止する。
• 環境の最適化：プライバシーレベルの設定を「無視」にし、可能であれば64ビット版のExcelを使用する。

Power Queryはデータ処理において最強のツールですが、VBAと組み合わせる際には「非同期処理」というプログラム特有の壁を意識する必要があります。この記事で紹介した完全版のVBAコードを活用し、エラーやフリーズに怯えることのない、堅牢で快適な自動化システムを構築してください。