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

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

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

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

投稿日:

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

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

執筆者:

関連記事

DB操作フレームワーク はJPA or mybatis?

開発に向けた準備で、開発標準を準備するフレームワーク(FW)チーム、それらを使って実装を行う業務チームが集まって、「DB操作を行うためのFWは何を使うか?」という協議になった。 FWチームは、FW・J …

wildflyアップロードサイズ上限

多くのWebサーバやAPサーバでは、サーバリソースの過剰な消費やDoS攻撃に対する対処として、アップロードの最大サイズが決められている。(厳密には、ブラウザから入力値やファイル等のデータを送信するため …

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

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

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

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

postgresqlでの拡張のインストール

PostgreSQL10で暗号化関数を使用するため拡張(pgcrypto)をインストールした際の出来事を記載します。 事象 pgcryptoの拡張をインストールしたり、インストールの確認を行うと、その …

プロフィール ゆっきーです。
都内でシステムエンジニアをやっています。
もっと詳細を見る