PowerShellを使ってExcelのPower Query更新を自動化する

ExcelのPower Queryの更新を自動化する方法がありますが、Excelを開いた状態でないと自動で更新されません。

「Excelを開く」というのは手間なので、できるだけ簡単に更新したいと思います。

方法として考えられるのは、以下2点あります。

Power Automateを使って特定のスケジュールでExcelを開く
Power Queryを実行するPowerShellスクリプトを作成する。

この記事では、PowerShellを使った場合の自動化について解説します。

PowerShellとは
PowerShellを使う準備
1. 初期設定
2. テスト実行　Hello World
PowerShellからクエリ更新

PowerShellとは

PowerShellを開発したMicrosoft公式サイトには以下の記載があります。

PowerShell は、コマンドラインシェル、スクリプト言語、および構成管理フレームワークで構成されるクロスプラットフォームのタスク自動化ソリューションです。 PowerShell は Windows、Linux、および macOS 上で実行されます。

PowerShellとは

PowerShellはコマンドプロンプトと出来ることは似ていますが、コマンドプロンプトより高機能であり後継ツールとされています。

PowerShellを使う準備

初期設定

Windowボタンの右横にある検索バーに「PowerShell」を入力します。

検索にヒットした一覧の中で「PowerShell 7 (x64)」を右クリックし、「管理者として実行」をクリックします。

PowerShellが立ち上がります。

現在の実行ポリシーの設定を確認します。「Get-ExecutionPolicy」を入力し、Enterキーを押下します。

「Restricted」になっていれば、スクリプトは許可されないため、今回行いたいPower Queryを実行するスクリプトが実行できません。「Set-ExecutionPolicy RemoteSigned」を入力し、Enterキーを押下します。

「Get-ExecutionPolicy」を入力して、実行ポリシーが「RemoteSigned」になっていることを確認します。

テスト実行　Hello World

スクリプトが実行できるか確認するため、「Hello World」というテキストが出力されるスクリプトを作成します。スクリプト作成するために「Windows PowerShell ISE」を起動します。

Windowボタンの右横にある検索バーに「PowerShell」を入力します。

検索にヒットした一覧の中で「Windows PowerShell ISE」をクリックします。

「新規作成」ボタンをクリックします。

Hello Worldを出力するためのコード「Write-Host “Hello World”」を入力し、「実行」ボタンをクリックします。実行結果に「Hello World」が表示されればスクリプトが実行できることが確認できました。

PowerShellからクエリ更新

Excelのクエリプロパティ「バックグラウンドで更新する」をオフにする

PowerShellでクエリを更新するとき、「バックグラウンドで更新する」のチェックがついていると、エラーになります。

事前にチェックを外し、設定をオフにします。

「データ」タブを開き、「クエリと接続」をクリックします。

クエリを右クリックし、「プロパティ」をクリックします。

「バックグラウンドで更新する」のチェックを外し、OKをクリックします。

設定が終わったら、Excelファイルを保存して閉じます。

クエリを実行するPower Shellを記述する

「Windows PowerShell ISE」で以下のスクリプトを記述します。

Excelファイルのパスとログシート名（赤字部分）は必要に応じて変更しましょう。

#############################################################################
# 初期処理
#############################################################################
# Excelオブジェクトの作成
$excel = New-Object -ComObject Excel.Application

# Excelを画面表示設定
# $true：Excelを画面に表示して実行
# $false：Excelを画面に非表示で実行
$excel.Visible = $false

# Excelファイルのパス定義
set-variable -name file_name -value "C:\PowerQuery\クエリ更新.xlsx" -option Constant

# ログシート名の定義
set-variable -name log_sheet_name -value "LOG" -option Constant

#############################################################################
# メイン処理
#############################################################################
# Excelファイルのオープン
$book = $excel.Workbooks.Open($file_name)

# シート移動
$sheet = $excel.Worksheets.Item($log_sheet_name)

# 更新日記入
$log = Get-Date -Format "yyyy/MM/dd HH:mm"
$range =$sheet.Range("A1")
$range.Value ="更新日時: $log"

# クエリ更新
$book.refreshall()

# Excelファイルの保存
$book.Save()

#############################################################################
# 終了処理
#############################################################################
# Excelファイルのクローズ
$excel.Quit()

# プロセスの削除
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel)
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($sheet)