Excelのクエリプロパティの設定で、クエリ更新（Power Query）を自動化する方法があります。Excelを開いた状態でないと自動で更新されないため、毎週月曜日の9：30に更新するなど、決まったスケジュールで更新できません。
PowerShellを使用すれば、Excelを開かずにPower Queryのデータ更新が可能です。Windows標準のタスクスケジューラと組み合わせることで、手動操作を一切なくし、定期的なデータ更新が可能になります。
本記事では、PowerShell＋タスクスケジューラを使った場合の自動化について解説します。
その他の更新自動化の方法については、「ExcelのPower Query更新を自動化する」で解説しています。
PowerShellとは
PowerShellを開発したMicrosoft公式サイトには以下の記載があります。
PowerShell は、コマンドライン シェル、スクリプト言語、および構成管理フレームワークで構成されるクロスプラットフォームのタスク自動化ソリューションです。 PowerShell は Windows、Linux、および macOS 上で実行されます。
PowerShellはコマンドプロンプトと出来ることは似ていますが、コマンドプロンプトより高機能であり後継ツールとされています。
事前準備
セキュリティ上の理由から、デフォルトではPowerShellが実行できないようになっています。実行できるように設定変更します。また、自動更新したいExcelの設定も変更する必要があります。
PowerShellの初期設定
Windowボタンの右横にある検索バーに「PowerShell」を入力します。
検索にヒットした一覧の中で「PowerShell 7 (x64)」を右クリックし、「管理者として実行」をクリックします。
PowerShellが立ち上がります。
現在の実行ポリシーの設定を確認します。「Get-ExecutionPolicy」を入力し、Enterキーを押下します。
「Restricted」になっていれば、スクリプトは許可されないため、今回行いたいPower Queryを実行するスクリプトが実行できません。「Set-ExecutionPolicy RemoteSigned」を入力し、Enterキーを押下します。
「Get-ExecutionPolicy」を入力して、実行ポリシーが「RemoteSigned」になっていることを確認します。
PowerShellのテスト実行 Hello World
スクリプトが実行できるか確認するため、「Hello World」というテキストが出力されるスクリプトを作成します。スクリプト作成するために「Windows PowerShell ISE」を起動します。
Windowボタンの右横にある検索バーに「PowerShell」を入力します。
検索にヒットした一覧の中で「Windows PowerShell ISE」をクリックします。
「新規作成」ボタンをクリックします。
Hello Worldを出力するためのコード「Write-Host “Hello World”」を入力し、「実行」ボタンをクリックします。実行結果に「Hello World」が表示されればスクリプトが実行できることが確認できました。
Excelのクエリプロパティ「バックグラウンドで更新する」をオフにする
PowerShellでクエリを更新するとき、「バックグラウンドで更新する」のチェックがついていると、エラーになります。
事前にチェックを外し、設定をオフにします。
「データ」タブを開き、「クエリと接続」をクリックします。
クエリを右クリックし、「プロパティ」をクリックします。
「バックグラウンドで更新する」のチェックを外し、OKをクリックします。
設定が終わったら、Excelファイルを保存して閉じます。
実際の手順
手順1：クエリを実行するPower Shellを記述する
「Windows PowerShell ISE」で以下のスクリプトを記述します。Excelファイルのパス（変数$filePath）は必要に応じて変更しましょう。
# 設定：対象のExcelファイルのパス（環境に合わせて変更してください）
$filePath = "C:\PowerQuery\クエリ更新.xlsx"
# Excelアプリケーションの起動
$excel = New-Object -ComObject Excel.Application
$excel.Visible = $false # Excelを画面に表示しない
$excel.DisplayAlerts = $false # 保存時の確認メッセージ等を非表示にする
try {
# ワークブックを開く
$workbook = $excel.Workbooks.Open($filePath)
# データの更新（すべて更新）
# ※事前準備で「バックグラウンドで更新する」をオフにしておく必要があります
$workbook.RefreshAll()
# 保存して閉じる
$workbook.Save()
$workbook.Close()
Write-Host "更新が完了しました。" -ForegroundColor Cyan
}
catch {
Write-Error "エラーが発生しました: $_"
}
finally {
# 終了処理（プロセスを確実に解放する）
# これを行わないとExcelがバックグラウンドに残り続けます
if ($workbook) { [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null }
if ($excel) {
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
}
# ガベージコレクションの強制実行
[GC]::Collect()
[GC]::WaitForPendingFinalizers()
exit 0
}手順2：PowerShellを実行する
「Windows PowerShell ISE」にスクリプトを貼り付けたら、実行ボタンをクリックします。
クエリが実行され、データが最新になっていることを確認します。
手順3：スクリプトを保存する
「保存」ボタンをクリックします。
保存するフォルダを決めてファイル名を入力し、「保存」ボタンをクリックします。
手順4：スクリプトをタスクスケジューラへ登録する
作成したスクリプトを定期実行するために、タスクスケジューラへ登録します。
Windowsのスタートメニューで「タスク」と入力します。
「タスクスケジューラ」をクリックします。
「基本タスクの作成」をクリックします。
「名前」に「Excel自動更新」など、わかりやすい名前を入力します。「説明」には必要に応じてタスクスケジューラの説明を入力し、「次へ」をクリックします。
「毎日」「毎週」など、実行したい頻度を選択し、「次へ」をクリックします。
開始時間を設定し、OKをクリックします。
「プログラムの開始」を選択し、「次へ」をクリックします。
以下を入力し、「次へ」をクリックします。
プログラム/スクリプト：「C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe」
引数の追加：「-Command “C:\PowerQuery\PowerQuery.ps1″」
「完了」をクリックして登録を終了します。
手順5：動作テスト
登録したタスクを右クリックし、「実行する」 をクリックしてください。
エラーが表示されず、Excelファイルの更新日時が変わっていれば設定成功です。
注意点
実行エラーになる場合は、次をチェックしてください。
- PCの電源: タスク実行時間にPCの電源が入っている（またはスリープではない）必要があります。
- サインイン状態: 上記の設定（基本タスク）では、PCにサインインしている時のみ実行されます。サインインしていない状態でも実行したい場合は、タスクのプロパティから「ユーザーがログオンしているかどうかにかかわらず実行する」を選択してください。
（ご参考）保存したスクリプトを実行する
拡張子ps1をPowerShellに関連付けしておけば、保存したスクリプトをダブルクリックするとPowerShellが立ち上がり、スクリプトが実行されます。
あるいは、PowerShellから実行するスクリプトのファイルパスを入力し、Enterキーを押下するとスクリプトが実行されます。
まとめ
今回紹介したPowerShellスクリプトとタスクスケジューラを組み合わせることで、「ファイルを開く・更新ボタンを押す・保存して閉じる」という日々のルーチンワークを完全に自動化できます。
決まったスケジュールで更新が必要なExcelがある場合は、ぜひ日々の業務効率化にお役立てください。
