アプリ開発ときどきアウトドア

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

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


(adsbygoogle = window.adsbygoogle || []).push({});


(adsbygoogle = window.adsbygoogle || []).push({});

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

執筆者:

関連記事

Javaでサポートする暗号化アルゴリズム

Java暗号化アーキテクチャ Javaで暗号化処理を実装する場合、Java暗号化アーキテクチャ(Java Cryptography Architecture: JCA)と呼ばれるフレームワークを使いま …

Azure AD B2Cの拡張属性とカスタム属性の違い

Azure AD B2Cのユーザアカウントを操作する際、拡張属性(ExtensionProperty)とカスタム属性(Custom Attribute)というキーワードがあり紛らわしい…と …

Java正規表現によるパラメータ置換

mybatis-generatorが生成するクエリカスタマイズのために、生成されたクエリ上のパラメータを置換する方法を調べたので記載しておきます。 サンプルプログラム Java言語の場合、標準ライブラ …

SLF4Jの仕組みと使用方法

何気に使用しているSLF4Jの仕様やその仕組みを整理したいと思います。 前提 元ネタはSLF4J Manualサイトです。 2019年1月での安定版の最新である1.7.25を使用した例を記載します。 …

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

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