Excel VBAのパワークエリについてお探しですね。
広告
PowerQueryの更新をボタン1つで！Excel VBAで作る自動化の仕組み
PowerQueryでCSVや別のExcelファイル、データベースからデータを取り込む設定を作っても、毎回手動で「更新」ボタンを押しているという方は多いのではないでしょうか。
特に、複数のクエリを決まった順番で更新したいときや、他の人にも同じ操作をしてもらいたいときは、Excel VBAで更新ボタンを作っておくと便利です。
更新忘れや操作ミスを減らせますし、誰でも簡単に最新データに更新できるようになります。
この記事では、VBAの基本的なコード、ボタンの設置方法、複数のクエリを順番に更新する方法、よくあるトラブルへの対処法まで、実務ですぐに使える内容をまとめて解説します。
PowerQueryの更新をボタン化するとどう便利になる?
PowerQueryは、Excelにデータを取り込むときの手順を記録しておける便利な機能です。
例えば、毎月届くCSVファイルを読み込んで、不要な列を削除して、列名を整えて、集計用のテーブルに変換する…といった作業を一度設定すれば、次回からは「更新」するだけで同じ処理を自動でやってくれます。
ただし、PowerQueryは自動的に勝手に更新されるわけではありません。
通常は「データ」タブの「すべて更新」ボタンや、各クエリの「更新」ボタンを手動で押す必要があります。
ここで役立つのがVBAです。
VBAはExcelに最初から入っているマクロ機能で、手作業でやっている操作をプログラムとして実行できます。
PowerQueryの更新もVBAから呼び出せるので、更新用のマクロを作ってシート上のボタンに登録すれば、ボタンをクリックするだけで最新データに更新できるようになります。
日次レポート、売上集計、在庫表、会計データの加工など、同じExcelファイルを何度も更新する業務では特に効果的です。
この方法の良いところは、PowerQueryの画面を開かなくても更新できることです。
クエリ名や設定を知らない人でも、「更新」ボタンを押すだけで処理が完了します。
画面の更新を止めたり、完了メッセージを表示したりすれば、操作もわかりやすくなります。
ただし、マクロを使うので、ファイル形式は通常の「.xlsx」ではなく、マクロ有効ブックの「.xlsm」で保存する必要があります。
会社のセキュリティ設定によってはマクロが動かないこともあるので、配る前に実行環境を確認しておきましょう。
基本のVBAコードとボタンの作り方
まずは、ブック内のPowerQueryや外部接続をまとめて更新する、一番シンプルな方法から見ていきましょう。
Excel VBAでは、`RefreshAll`という命令を使うことで、ブックに含まれる接続やクエリを一括更新できます。
PowerQueryで作ったクエリをシート上のテーブルに読み込んでいる場合、ほとんどのケースでこのコードで更新できます。
“`vba
Sub PowerQuery更新()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox “PowerQueryの更新が完了しました。
“, vbInformation
Exit Sub
ErrHandler:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox “更新中にエラーが発生しました。
” & vbCrLf & Err.Description, vbExclamation
End Sub
“`
このコードでは、`ThisWorkbook.RefreshAll`でブック内のクエリや接続を更新して、`Application.CalculateUntilAsyncQueriesDone`で処理の完了を待つようにしています。
`ScreenUpdating`は画面のちらつきを抑える設定で、`DisplayAlerts`は不要な確認メッセージを出さないようにする設定です。
最後に完了メッセージを表示することで、ボタンを押した人が「処理が終わった」とわかりやすくなります。
エラー処理も入れているので、データ元のファイルが見つからなかったり、接続先にアクセスできなかったりした場合でも、Excelが固まったように見える状態を避けられます。
ボタンの設置方法
ボタンを作る手順は簡単です。
1. Excelの「開発」タブを表示する（表示されていない場合は、「ファイル」→「オプション」→「リボンのユーザー設定」から「開発」にチェック）
2. 「開発」タブの「挿入」から、フォームコントロールの「ボタン」を選ぶ
3. シート上の好きな場所にボタンを配置する
4. 表示される「マクロの登録」画面で、先ほど作った`PowerQuery更新`を選択する
5. ボタン名を「データ更新」「PowerQuery更新」「最新化」など、わかりやすい名前に変更する
これで完成です。
ボタンをクリックするだけでPowerQueryが更新されるようになります。
複数のクエリを順番に更新したい場合
PowerQueryの更新で注意したいのが、複数のクエリに依存関係があるケースです。
例えば、クエリAで元データを取り込んで、その結果をExcelのテーブルに出力し、そのテーブルをさらにクエリBで読み込んで加工する…という構成の場合、Aの更新が完了してからBを更新しないと、Bが古いデータを参照してしまう可能性があります。
「すべて更新」は便利ですが、クエリが同時に実行されることがあるため、処理の順番が重要なブックでは思った結果にならないことがあります。
順番を指定して更新する方法
このような場合は、対象のクエリを個別に指定して、VBAで順番に更新する方法が有効です。
PowerQueryの読み込み先がExcelテーブルになっている場合は、テーブル名を指定して更新できます。
テーブル名は、テーブル内をクリックしたときに表示される「テーブル デザイン」タブの「テーブル名」で確認できます。
クエリ名とテーブル名は似ていても別物なので、混同しないように注意してください。
“`vba
Sub PowerQueryを順番に更新()
On Error GoTo ErrHandler
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With ThisWorkbook.Worksheets(“集計”)
.ListObjects(“売上取込”).QueryTable.Refresh BackgroundQuery:=False
.ListObjects(“売上加工”).QueryTable.Refresh BackgroundQuery:=False
.ListObjects(“レポート用集計”).QueryTable.Refresh BackgroundQuery:=False
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox “指定した順番でPowerQueryを更新しました。
“, vbInformation
Exit Sub
ErrHandler:
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox “更新に失敗しました。
” & vbCrLf & Err.Description, vbExclamation
End Sub
“`
BackgroundQuery:=Falseの重要性
ここで重要なのが、`BackgroundQuery:=False`という指定です。
これは「バックグラウンドで更新せず、更新が終わるまで次の処理に進まない」という意味です。
これを指定しないと、1つ目の更新が終わる前に2つ目の更新が始まってしまい、依存関係のあるクエリで古い結果を取得してしまうことがあります。
特に、PowerQueryの結果に対してExcel関数を追加して、その表を次のPowerQueryで読み込むような設計では、更新の順番と待機処理がとても重要です。
注意点
すべてのPowerQueryが必ず`ListObjects(“テーブル名”).QueryTable.Refresh`で更新できるとは限りません。
クエリを「接続のみ」にしている場合や、データモデルにのみ読み込んでいる場合は、シート上のテーブルとして存在しないため、この書き方では指定できません。
実務では、まず「クエリと接続」ウィンドウで読み込み先を確認して、更新したいクエリがシート上のテーブルなのか、接続のみなのかを把握してからVBAを書くと失敗が少なくなります。
失敗しないための設定とトラブル対策
PowerQuery更新ボタンを作った後によくあるトラブルは、「自分のPCでは動くけど他の人のPCでは動かない」「更新ボタンを押しても古いデータのまま」「クエリ名を変えたらマクロが止まった」といったものです。
これらは、VBAの書き方だけでなく、Excelファイルの保存形式、接続先のパス、クエリの読み込み設定、マクロのセキュリティ設定が関係していることが多いです。
運用する前に最低限の確認をしておくと、後からの手戻りを減らせます。
保存形式とファイルパス
まず、ファイルは必ず「Excelマクロ有効ブック（.xlsm）」で保存してください。
.xlsx形式で保存すると、作成したVBAコードが消えてしまいます。
また、共有フォルダやOneDrive、SharePoint上で使う場合は、データ元ファイルへのパスが全員からアクセスできるか確認してください。
自分のデスクトップにあるCSVをPowerQueryで参照していると、他の人がボタンを押したときに「ファイルが見つからない」というエラーになります。
複数人で使うなら、共通のフォルダにデータ元を置く設計が安全です。
確認しておきたいポイント
– ファイル形式は「.xlsm」で保存されているか
– PowerQueryの参照元パスは全員がアクセスできる場所か
– 依存関係があるクエリは、順番に更新する設計になっているか
– バックグラウンド更新が必要に応じてオフになっているか
– クエリ名・テーブル名を変更した場合、VBAコードも修正しているか
バックグラウンド更新の設定確認
特に「バックグラウンドで更新する」の設定は見落としやすい項目です。
確認するには、「データ」タブから「クエリと接続」を開き、対象のクエリを右クリックして「プロパティ」を選びます。
そこで「バックグラウンドで更新する」のチェックを外しておくと、更新完了前に次の処理へ進むリスクを減らせます。
ただし、クエリの数が多い場合は、すべてのクエリで同じ確認が必要です。
処理速度を優先してバックグラウンド更新を使う場面もありますが、正確な順番が求められる集計ファイルでは、同期的に更新する方が安定します。
利用者にわかりやすくする工夫
もう一つ大切なのが、マクロを使う人にとってわかりやすくすることです。
更新中にExcelが固まったように見えると、何度もボタンを押したり、途中でファイルを閉じたりすることがあります。
処理が重い場合は、ステータスバーに「更新中です」と表示したり、完了後にメッセージを出したり、必要であればボタンの近くに「更新完了メッセージが出るまで操作しないでください」と書いておくと安心です。
VBAは単に処理を自動化するだけでなく、使う人が迷わず操作できる画面づくりまで含めて考えると、実務で長く使える仕組みになります。
まとめ
Excel VBAでPowerQueryのデータ更新をボタン1つで実行する方法は、シンプルな一括更新なら`RefreshAll`、順番が必要な更新ならテーブルや接続を個別指定する、という考え方で整理できます。
最初は基本の更新ボタンを作って、業務ファイルの構造に合わせて順番制御やエラー処理を追加していくと、無理なく導入できます。
毎回の手作業をボタン化しておけば、更新忘れを防げますし、誰が操作しても同じ結果を得やすくなります。
PowerQueryで作ったデータ加工を実務に定着させるなら、VBAによる更新ボタンはとても相性の良い仕上げの自動化です。
ぜひ試してみてください。
広告
