OfficeアプリのコントロールはPowerShellかVBAか？

2019年12月8日
2023年1月4日
Excel/Word, システムエンジニアリング

Windowsサーバの監視のお仕事での話です。
月次で各サーバのパフォーマンスモニタのログファイル(.blg)が送られてきます。このデータ群から、各種のグラフや表を作成して、PowerPointでそれなりに綺麗に見せる必要があります。
blgファイルに対する各種操作、Excelへのデータ投入からグラフや表の作成、それらをPowerPointの所定の場所への貼り付け、を行う必要があります。ただの力技なお仕事なので、スクリプトを使って自動化を試みた際の考察です。
なお、VBAによる制御のサンプルはこちらで公開しています。

NDW

ExcelからPowerPointへの図表貼り付けVBA

パフォーマンスモニタの監視データ(blg)に基づいてPowerPointで報告用のレポートを作成する必要がありました。パ…

1 PowerShell vs. VBA
2 （参考）PowerShellからExcel VBAを実行する方法

PowerShell vs. VBA

上記のようにファイル操作やOfficeアプリの操作等、幅広い要件をスクリプトで自動化しようと考えました。
OfficeアプリのコントロールはPowerPointからもできるので、当初はPowerShellに統一してファイル操作やOfficeアプリの操作を実装しようとしましたが、不便で開発効率が悪い所ことが分かりました。
基本はPowerShellで実装するが、Officeアプリの複雑な操作が必要な場合は、Excel等でそのような処理を実行するVBA関数を作成し、PowerShellから呼び出すような方式が良いと思いました。
もちろん、Officeアプリの操作だけで良いのであれば、VBAで十分かと思います。この辺はVBAに、一日の長があると思いました。

起動のしやすさを考えると、一旦アプリケーションを起動してから関数を実行する必要があるVBAより、エクスプローラ上からダブルクリックで実行できるPowerShellが良い。
外部コマンドの実行やその結果を編集する場面では、PowerShellが使い勝手が良い。特に、CSVファイルに対する変換や加工等の操作は便利で使い勝手が良い。
PowerShellの開発環境であるPowerShell ISEでは、PowerShell上のオブジェクトのメソッド名やプロパティ名の補完(Intellisense)を使用できるが、PowerPointやExcelをコントロールするためのCOMオブジェクトに対して既定ではIntellisenseを使用できない。コマンドウインドウで逐次実行すれば、IntelliSenseが有効になるが、毎回これをやるのは辛い…Excel VBAの開発環境の方がよっぼど便利な気がする。
Officeアプリのコントロールに関する情報やサンプルは、PowerShellベースのものより、VBAベースのものが圧倒的に多い。PowerShellでもVBAでも、COMオブジェクトのAPIをコールしているだけなので実質同じことをやっているので、VBAのサンプルを見てPowerShellのプログラムを作成することもできるが、これをやるなら素直にVBAで実装した方が効率が良い。
PowerShellからのOffice操作は、VBAに比べ完成度が高いように思えなかった。VBAではExcelシート上の任意のセルに値を設定する、という基本的な処理で、VBAではすぐに処理が終わるが、PowerShellだと数秒かかったりした。

（参考）PowerShellからExcel VBAを実行する方法

ExcelのCOMオブジェクト(Excel.Application)を作成し、VBAを含むExcelファイルを開いた後、Run関数でプロシージャや関数を実行できます。
終了時にReleaseComObjectで、使用したExcelのオブジェクトを開放しないと、バックグラウンドにExcelのプロセスが残ってしまうので注意のこと。

# スクリプトのパス基準でExcelファイルを指定$base = $PSScriptRoot # PSv3+$file = $base + "\Test.xlsm"# Excelの起動とファイルオープン$excel = New-Object -ComObject excel.application$workbook = $excel.Workbooks.Open($file)$arg1 = "testarg"$arg2 = 1234# プロシージャの実行Write-Host "invoking precedure..."$excel.Run("InvokeSub", $arg1, $arg2)# 関数の実行Write-Host "invoking function..."$result = $excel.Run("InvokeFunc", $arg1, $arg2)Write-Host "Result: $result"# 終了処理$workbook.Close($false) # 保存せずに終了$excel.Quit()# Excelプロセスが残るのを回避[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

# スクリプトのパス基準でExcelファイルを指定

$base = $PSScriptRoot # PSv3+

$file = $base + "\Test.xlsm"

# Excelの起動とファイルオープン

$excel = New-Object -ComObject excel.application

$workbook = $excel.Workbooks.Open($file)

$arg1 = "testarg"

$arg2 = 1234

# プロシージャの実行

Write-Host "invoking precedure..."

$excel.Run("InvokeSub", $arg1, $arg2)

# 関数の実行

Write-Host "invoking function..."

$result = $excel.Run("InvokeFunc", $arg1, $arg2)

Write-Host "Result: $result"

# 終了処理

$workbook.Close($false) # 保存せずに終了

$excel.Quit()

# Excelプロセスが残るのを回避

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null

Function InvokeFunc(str As String, i As Integer) InvokeFunc = "arg1=" & str & ", arg2=" & iEnd FunctionSub InvokeSub(str As String, i As Integer) MsgBox "arg1=" & str & ", arg2=" & iEnd Sub

Function InvokeFunc(str As String, i As Integer)

InvokeFunc = "arg1=" & str & ", arg2=" & i

End Function

Sub InvokeSub(str As String, i As Integer)

MsgBox "arg1=" & str & ", arg2=" & i

End Sub