今回はWindows PowerShellを使って、Excelをバックグラウンドで開きパワークエリの更新を行います。バックグラウンド処理なので画面には表示されませんが、背後のプロセスでデータを更新します。
こちらのPowerShellスクリプトを元にタスクスケジューラやPower Automate Desktopで定期的な自動更新を目指します。
PowerShellの起動と初期設定
PowerShellでスクリプトを実行できるようにするための初期設定を行います。具体的には実行ポリシーを変更します。
実行ポリシーの変更
まずWindowsボタン隣の検索で「PowerShell」と入力します。
Windows PowerShellが表示されたら、右クリックを押して管理者として実行を選択します。
以下のPowerShellコンソールが表示されます。
現在の実行ポリシーの設定を確認するため、以下のコマンドレットを入力します。
Get-ExecutionPolicy
変更する前は下記のように「Restricted」となっており、スクリプトが実行できない設定になっています。
ちなみにこの状態で通常のユーザーがスクリプトを実行すると、以下のようなエラーメッセージが表示されます。
スクリプトの実行を可能にするため、以下のコマンドレットを入力します。質問には「Y」で回答します。
Set-ExecutionPolicy RemoteSigned
設定ができたら、「exit」と入力してPowerShellコンソールを閉じます。
PowerShell ISEによるスクリプトの実行テスト
再び左下の検索で「PowerShell」と入力しますが、今度はスクリプトを作成するためWindows PowerShell ISEを起動します。
画面上部のスクリプトウィンドウに以下のテキストを入力し、「▶」をクリックしてスクリプトを実行します。
Write-Host "Hello World"
すると、画面下部のコンソールにスクリプトの内容と実行結果が表示されます。
今度は保存ボタンを押してスクリプトを保存します。
ファイル名は「HelloWorld.ps1」とします。PowerShellのスクリプトファイルの拡張子は「ps1」にします。
保存されるとスクリプトウィンドウのタブにファイル名が表示されます。
次にスクリプトとして実行するため「×」をクリックしてこのスクリプトを閉じます。
Shiftキーを押しながら先ほど作成したスクリプトを右クリックし、パスのコピーを選びます。
再びPowerShell ISEに戻り以下のボタンをクリックして新しいスクリプトウィンドウを開きます。
スクリプトウィンドウが開いたら、先ほどのパスをコピーし先頭と末尾のダブルクォートを削除して実行します。
すると下のコンソールに正常に実行結果が表示されます。
実行結果が確認できたので、スクリプトウィンドウを閉じます。
PowerShellでExcelを開く
スクリプトの動作確認ができたので、いよいよパワークエリの更新に移ります。
クエリの「バックグラウンドで更新する」をオフにする
PowerShellでクエリを更新するときバックグラウンドで更新するの設定をオフにしておかないとクエリの更新が完了する前に次の処理に進み、エラーが発生するのでオフにします。
最初にパワークエリでクエリを作成したxcelを開きクエリと接続ペインを表示します。
対象のクエリを右クリックし、プロパティを選択します。
クエリ プロパティが表示されたらバックグラウンドで更新するのチェックを外します。
すべて更新でこの接続を更新するにはチェックを付けたままにしておきます。
Excelブックを保存して閉じます。
なおこの設定を行わない場合、更新の途中で以下のエラーメッセージが表示されてしまいます。
Excelを表示しながら実行
PowerShell ISEに戻り、新しくスクリプトウィンドウを開き、以下のスクリプトを貼り付けます。
この時、以下2点のみご自身の環境に応じて変更してください。
# Excelファイルのオープン（要変更）
$book = $excel.Workbooks.Open("C:\PowerS_TEST\キャンペーン.xlsx")
⇒ パワークエリのあるファイルの絶対パスを入力します。
# シート移動（要変更）
$sheet = $excel.Worksheets.Item("Home")
⇒ 更新日時を記入するためのシートを入力します。
# Excelオブジェクト作成
$excel = New-Object -ComObject Excel.Application
# Excelの表示（ここで表示かバックグラウンドか）
$excel.Visible = $true
# Excelファイルのオープン（要変更）
$book = $excel.Workbooks.Open("C:\PowerS_TEST\キャンペーン.xlsx")
# シート移動（要変更）
$sheet = $excel.Worksheets.Item("Home")
# 更新日記入
$log = Get-Date -Format "yyyy/MM/dd HH:mm"
$range =$sheet.Range("A1")
$range.Value ="更新日時: $log"
# クエリ更新
$book.refreshall()
# 上書き保存
$book.Save()
# 閉じる
$excel.Quit()
#プロセスの削除
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)これで実行するとExcelが自動的に立ち上がり、以下の作業を行った後、ファイルを保存して閉じます。
１．HomeシートのA1セルに更新日時を記入（ $range.Value ="更新日時: $log" ）
２．クエリの更新（ $book.refreshall() ）
PowerShell ISEの後ろに隠れていますが、Excelが一連のタスクを行っています。
Excelを表示せずにバックグラウンドで実行
今度は同じ処理をExcelを表示させずにバックグラウンドで実行します。
前のスクリプトと異なるのは以下の部分のみです。
$visible = false
# Excelオブジェクト作成
$excel = New-Object -ComObject Excel.Application
# Excelの表示（ここで表示かバックグラウンドか）
$excel.Visible = $false
# Excelファイルのオープン（要変更）
$book = $excel.Workbooks.Open("C:\PowerS_TEST\キャンペーン.xlsx")
# シート移動（要変更）
$sheet = $excel.Worksheets.Item("Home")
# 更新日記入
$log = Get-Date -Format "yyyy/MM/dd HH:mm"
$range =$sheet.Range("A1")
$range.Value ="更新日時: $log"
# クエリ更新
$book.refreshall()
# 上書き保存
$book.Save()
# 閉じる
$excel.Quit()
#プロセスの削除
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)
今回は画面上には現れませんが、タスクマネージャーには確かにExcelのプロセスが現れ、処理が終わった後消えていきます。
なお、何らかの理由で途中でプロセスが止まってしまった場合はタスクマネージャーでExcelを右クリックしてタスクの終了を実行して強制的に落とします。
Excelファイルを開き、クエリの最終更新時刻を見るとこちらも更新されています。
ここまで来たらスクリプトウィンドウの保存ボタンを押して、保存します。
PowerShellスクリプトの実行
コマンドプロンプトから実行する
コマンドプロンプトから実行する場合には、以下のコマンドになります。
PowerShell C:\PowerS_TEST\PowerQueryRefresh.ps1
このコマンドをバッチファイルとして保存すれば、ダブルクリックで実行することもできます。
Power Automate Desktopから実行する
Power Automate Desktop実行する場合は、アクションでシステムのPowerShellスクリプトの実行を選び、実行する PoweShell コードに上のスクリプトの中身をそのまま貼り付ければ実行できます。
参考資料
今回は以下の記事を参考にさせていただきました。ありがとうございました。
PowerShellでExcelを操作するのはVBAによく似ているそうなので、VBAが得意な方は利用してみるのも良いかと思います。
