NDW

アプリ開発やトラブルシューティング等のノウハウ、キャンプや登山の紹介や体験談など。

1. システムエンジニアリング Excel/Word

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

投稿日:2019年12月8日 更新日:

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






-1. システムエンジニアリング, Excel/Word

関連記事

VULTRでRHEL8をインストール(2/2)

VULTRのVPSにRHEL8.3をインストールする手順を説明します。 概要 ここでは、VULTRにRHEL用のVPSを構築した状態から、RHELをインストールする手順を説明します。 VULTRにRH …

.NET Core(C#): DateTimeの使い方サンプル

はじめに サンプルは.NET Core 3.1(Windows 10)で動作確認しています。 型が分かりやすいよう変数宣言ではvarを使用していません。実装時はvarを使用することをおすすめします。 …

.NET Core(C#): Stopwatchで経過時間測定(ミリ秒, マイクロ秒, ナノ秒)

はじめに 次の環境を使用して動作確認しています。 ハードウェア CPU: AMD Ryzen 5 3400G, MEM: 16GB, SSD: 130GB OS Windows 10(64ビット) I …

mavenマルチモジュールプロジェクトの構成例

システム開発でよく使用するmavenマルチモジュールプロジェクトの構成サンプルを説明します。 構成方針 複数のサブシステムをもつシステム開発を想定しています。システム名はzzz、サブシステムはf10, …

wildflyへのwarデプロイの自動化

更新したWebアプリをWildflyにデプロイするのが面倒なのでスクリプトを作成してみました。 前提 実行環境はCentOS Linux 7です。 JavaEEのWebアプリの配布形式であるwarファ …