JenkinsでVBAを動かす際のあれこれ

かれこれ1年以上、JenkinsでExcel VBAを動かしているので、そろそろたまっていることを出そうと思いました。

Excel VBAをサービス内で起動させる方法
Excel VBAをコマンドラインから起動させる方法
自動化したいマクロは対話を避けよう。自動化に備えた作りにしよう。
ジョブを実行するたびに excel.exe が増えていく件
JenkinsからPowerShellスクリプトをアボート（停止）させたらどうなるの？
- そういう事故に備えて、前回起動したExcelのPIDをワークスペースのどこかにおいておき、次にジョブがそのワークスペースで起動した時にそのPIDを強制終了しにいけばいいんじゃないの？
マクロ呼び出しに相対パスは使えません
Excel VBAから処理進捗のコンソールログを出力したい
まとめ

Excel VBAをサービス内で起動させる方法

Excel VBAは素直にJenkinsで動くようにできていません。Jenkinsで動くのは基本的には応答のいらないコマンドライン操作だけです。

Windowsでいえば、Jenkinsですんなり動くのは対話無しのバッチファイルかPowerShell スクリプトといったものということですね。

Excel VBAはこのように便利なコマンドラインとはかけ離れた性質を持っており、一般的に対話が必要（対話がいるマクロは死んで欲しい）だったり、実行するにもコマンドラインから素直には実行できなかったりします。

そもそも、一般的にはWindowsでJenkinsやJenkinsスレーブを動かすにあたって、それらをサービスとしてインストールすることが多いと思いますが、 Windowsのサービスで立ち上がるプロセスは普通のクライアントセッション（普通のデスクトップ環境）のプロセスとは違い、 Session 0 Isolationとも呼ばれる対話無しの環境で動作するため、通常のGUIアプリケーションが動かなかったり奇妙な動作をしはじめたりします。

Excelも例に漏れず無対策だと動きませんが、ここらへんは過去に書いたエントリに記載した通り、下記のフォルダを作ってやればちゃんと動くようになります。

C:\Windows\system32\config\systemprofile\Desktop

# (x64環境で32bit Officeを動かしている場合)
C:\Windows\SysWOW64\config\systemprofile\Desktop

（ちなみにログインしているデスクトップ環境のセッションからのプロセスでJenkinsスレーブを開くようにしても、この問題は回避可能です。ここらへんのWindowsサービスから作成されたプロセス特有の問題はWindowsのブラウザやGUIのテストでもぶちあたると思います。）

Excel VBAをコマンドラインから起動させる方法

Excel VBAをコマンドラインから起動させる方法については、たぶん以下の方法が考えられます。

コンパイルのいる言語でコンソールアプリケーションを作って、そこからCOMを一生懸命呼び出す。
WSHで呼び出す。(cscript.exe とかで)
PowerShellで呼び出す。

僕はPowerShellで呼び出す、という選択肢を選びました。

Windows7以降は標準でバージョン ≧2.0 が使えますし、Jenkinsのビルドプロセス中、VBAの呼び出しといったタスク以外にも、 PowerShellを使った複雑な処理が多少は必要になる、あるいは複雑な処理とVBAが絡みあう可能性もあり、 PowerShellがCOMと.NETがあわさった手軽かつ強力なスクリプティング環境になっているからですね。

PowerShellでExcelを呼び出すのは以下のコードだけでできます。

$excelApp = New-Object -com "Excel.Application"

そこからマクロを呼ぶなら、下記のコードでできます。macroProcedureというマクロを引数2つつけて呼んでいる例です。

$macroBook = $excelApp.Workbooks.Open(マクロブックへの絶対パス)
# ' でマクロブック名を囲む必要はありませんが、スペースなどがある場合に備えて囲んでおきましょう。
$excelApp.Run("'$($macroBook.name)'!macroProcedure", "arg1", "arg2")

こういうマクロを呼び出すPowerShell スクリプトをJenkinsから呼び出せば、結果的にExcel VBAをJenkinsから呼び出していることになります。

Jenkins上でのPowerShellの呼び出しはPowerShell用のプラグインがJenkinsにあるのでそれを使いましょう。

ただし、Jenkinsはテンポラリの*.ps1スクリプトをその場で生成してそれを動かそうとするので、そのテンポラリのスクリプトを動かせるよう、 Set-ExecutionPolicyでポリシーを変更しておきましょう。 (システム管理者権限が必要。)

Set-ExecutionPolicy RemoteSigned
# または
Set-ExecutionPolicy Unrestricted

PowerShellの処理について、bashの-eフラグを立てた時のように、処理中に何らかのエラーがあった場合に即時終了してほしい場合は下記のようにしておけば即時終了します。

$ErrorActionPreference="Stop"

自動化したいマクロは対話を避けよう。自動化に備えた作りにしよう。

Jenkins上のVBA内でメッセージがポップアップしたり、ダイアログが開いてしまうと、そこで処理が停止してしまいます。

自動化させたいマクロにこういう対話要素を組み込むのはやめましょう。（ちなみにマクロでランタイムエラー等が出ても誰にも応答できません。）

個人的な好みを言わせてもらえば、対人間であってもマクロでダイアログは開いてほしくないものです。

開発者向けに作ったマクロなんかは、ダイアログはほとんどの場合邪魔になっているケースが多いと思います。

特に、ディレクトリを選ぶパスもペーストできない、あのダイアログをわざわざWin32API使って呼ぶ奴はクレイジーと断言していいでしょう。あのディレクトリツリーを一日数十回以上ポチポチさせられる人の気持ちを想像してほしいです。

マクロの設定値が必要ならセルに書かせりゃいいんです。セルから値を拾うのが面倒なら、値の部分を名前付き範囲にすればいいんです。（ここらへんはいつかエントリにします。）

マクロの構成としては実際、対人間用と対Jenkins用にマクロの入り口のプロシージャを下記のように分けるのが現実的でしょう。

対人間用入り口 → 共通メイン処理
対Jenkins用入り口 → 共通メイン処理

対Jenkins用入り口で対話的要素のフラグをOFFるための初期処理などさせればいいです。

ジョブを実行するたびに excel.exe が増えていく件

PowerShell内で起動したExcelは基本的には自前で終了させるまで起動しっぱなしです。 PowerShellが終了したからといって自動的に強制終了させられたりはしません。（そうだったらどんなに楽だったか）

メモリの肥やしを消す意味でも、また、excel.exeが掴んでいるファイルロックを解除させるためにも、マクロ実行後のExcelの後始末はちゃんとしましょう。

少なくとも下記の条件がすべて満たされていれば、後始末完了とみなして良いようです。

生成したExcel.Applicationおよび内部への参照をPowerShell スクリプトが保持していないこと。（ただ、PowerShell スクリプトからの参照についてはPowerShell スクリプトが終了すれば全て解放されるので、あまり気にする必要はないかも）
$excelApp.Visible = $true にしないこと。可視化されたExcelは解放されません。
開いたブックを全て閉じていること。
最後に $excelApp.Quit() が発行されていること。

後始末が完了していれば、適当なタイミングでexcel.exeが閉じられるようです。適当なタイミングというのは、次にExcel.Applicationを作った時とか、そういう本当に適当なタイミングのようです…。

（もし中途半端に残留するexcel.exeを許せない場合、Excelのアプリから$excelApp.HWNDでハンドラをとりWin32APIでPIDを取得して Stop-Process で強制終了するといった方法がオススメです）

Excelを後始末するには、下記のコードのようにすればよいでしょう。

try {
  $excelApp = New-Object -com "Excel.Application"
  // $excelApp を使った処理
} finally {
  # 後始末
  if ($excelApp) {
    # 抵抗されると面倒なのでイベント・警告OFF、VisibleはOFFになっていると思うがOFF。
    $excelApp.EnableEvents = $false
    $excelApp.DisplayAlerts = $false
    $excelApp.Visible = $false

    # 全ブック保存なしで閉じる。保存など必要な操作はここにくるまでにされているはず。
    $excelApp.Workbooks | % { $_.Close($false) }

    # アプリケーションが終了済みとしてマーク
    $excelApp.Quit()
    # Excelに関する参照を外す（どうせこのスクリプトを終了したら外れるので、書かなくてもいい）
    $excelApp = $null
  }
}

ただ、このように後処理したとして、何かマクロでエラーが残ってしまうなどのことが起これば、プロセスはいともたやすく残留してしまうので、 1日1回程度Officeアプリケーションだのを全部殺すプロセスクリーニングジョブを流すこともおすすめしておきます。 VBAのビルドをしているマシンを毎日再起動できるなら、そうしてもいいでしょう。

また、あるワークスペース上にてExcelのプロセスが中途残留したことにより、たとえばワークスペース内のExcelファイルにファイルロックが残りっぱなしになってしまうなどの事象については、基本的に読み取り専用でワークスペース内のファイルおよびマクロブックを開くように組んでおくことをおすすめします。（ワークスペースのファイルの属性を読み取り専用に変更してしまってもいいでしょう）

JenkinsからPowerShell スクリプトをアボート（停止）させたらどうなるの？

Jenkinsでは走行中のビルド処理を×ボタンを押すことによりビルドを中断（強制終了）させることができます。

ただその場合、大変残念ですが、PowerShellはそこで即座に強制終了させられ、内部で起動していたexcel.exeは死にません。走り続けてしまいます。

https://wiki.jenkins-ci.org/display/JENKINS/Aborting+a+build に書いてあるように、アボート時は子孫プロセスも殺すということで、 PowerShell内で起動したExcelなども死にそうですが、ExcelはPowerShellの子孫として起動しません。

じゃあfinally句あたりでExcelを殺しにいけばいいじゃないかというのも、 JenkinsのWindows版のプロセスキラーの実装（https://github.com/kohsuke/winp/blob/master/native/winp.cpp）が TerminateProcess （https://msdn.microsoft.com/ja-jp/library/cc429376.aspx）を呼んでPowerShellを強制抹殺しにいくので不可能です。

try {
  // 処理…
} finally {
  ' 強制終了させられたらfinallyもクソもなくPowerShellは終了。動作しているExcelのお掃除はできずに終了である。
}

このタイプの強制終了のトラップ（捕捉）はWindows上では不可能です。何もできません。

これで問題になるのは、たとえば、ブックやドキュメントを処理中に読み取り専用で開いたりしていなかった場合、アボートしたジョブのプロセスがファイルにロックを持ったままになってしまうことですね。残ったままのプロセスをどうにかして殺さない限りロックが解除されることはありません。次のジョブが同じワークスペースで動いた時、問題になるでしょう。

そもそもロックなんか気にしなくて済むように、読み取り専用で開いて済むものはなるべくそれで処理するのが一番ですが、何かいい方法ないんですかねー。

これに限らずWindows上でのJenkinsというのは、だいたいファイルのロックに悩まされるのが定番です。最悪の場合はプラグインか何かのせいで、Jenkinsのプロセス自体がワークスペース内のファイルにロックを持ってしまうこともあります。特定のフォルダ以下のロックの強制解除方法探したほうがはやいかもしれません。

そういう事故に備えて、前回起動したExcelのPIDをワークスペースのどこかにおいておき、次にジョブがそのワークスペースで起動した時にそのPIDを強制終了しにいけばいいんじゃないの？

確かにそうなのですが、残念ながらWindowsは以前使ったPIDを再利用します。OS起動後、プロセスごとに必ずユニークなPIDが振られると思ったら大間違い。

正常終了した後でもそのPIDが前回残留したものという保証はありません。ということで、一筋縄ではうまくいかないでしょう。

ここらへんの確実にプロセスを殺す手法についてはいろいろ考え中です。子孫関係のないプロセス、たとえばスケジューラとかにPowerShellが死んだらExcelを殺すように依頼するのが手っ取り早そうですね。 https://wiki.jenkins-ci.org/display/JENKINS/Spawning+processes+from+build も参考になるかも。

マクロ呼び出しに相対パスは使えません

PowerShellから起動されたExcelのインスタンスはシェルの相対パスなど理解してくれないので、PowerShellから与えるパス関連の情報は全て絶対パスで与える必要があります。

以下はパスに関する処理の、駄目な例と良い例。

# 駄目な例 (相対パスは使えない)
$macroBook = $excelApp.Workbooks.Open("macroBook.xlsm")
$excelApp.Run("$($macroBook.name)!macroProcedure", ".\targetFolder", ".\some\destionation\output.xlsx")


# 良い例 (一度相対パスで資材の場所を変数に格納しておき、絶対パスにあとで変換する)
function Make-Absolute(){
  param($relativePath)

  Join-Path (pwd).path $relativePath
}
$macroBookPath = "macroBook.xlsm"
$targetFolder = ".\target"
$destination = ".\some\destination\output.xlsx"

$macroBook = $excelApp.Workbooks.Open((Make-Absolute $macroBookPath))
$excelApp.Run("'$($macroBook.name)'!macroProcedure", (Make-Absolute $targetFolder), (Make-Absolute $destination))

ちなみにUnixなどのシェルスクリプトでもそうですが、作業中広域にわたってcdしてカレントディレクトリを切り替えるのは混乱のもとになるので、やめましょう。

少なくともJenkinsでのスクリプティングで、カレントディレクトリがワークスペースフォルダ直下以外になることは原則ないというように処理したほうがいいでしょう。

Excel VBAから処理進捗のコンソールログを出力したい

PowerShellからExcel VBAを起動できるのはわかった。

でも、無言のままジョブが走っていても今何が処理されているのかわからない。もしかしたら止まっているのかも？今何をしているのかVBA側からコンソールに表示したい。

これは当然の要求ですね。

これをさせるためには、PowerShellのコンソールに出力するメソッドを持つオブジェクトをVBAに渡してあげれば、VBA側からPowerShellのコンソールに出力させることができます。（そのPowerShell スクリプトがJenkins上で動いていれば、VBAからの出力がJenkinsのコンソール画面で見られることになります。）

' VBA (Book1.xlsmというブックにマクロモジュールとして下記プロシージャを中身にしたものを宣言)

Private consoleObj As Object

' PowerShell側から最初に呼んで conosleObj の中身をもらう関数
Public Sub RegisterConsole(ByVal envConsoleObj As Object)
  Set consoleObj = envConsoleObj
End Sub

' VBA内でDebug.Printのかわりに使う関数
Public Sub DebugPrint(ByVal logMessage$)
  If Not consoleObj Is Nothing Then
    ' ちなみに親のPowerShellだけ上記に書いたアボートなどで一方的に死ぬとここで失敗こきます (TypeName(consoleObj) は "Object"を返す)
    consoleObj.WriteLine logMessage$
  End If
  Debug.Print logMessage$  
End Sub


Public Sub SomeProcessing()

  DebugPrint "処理開始しました！"

  ' :
  ' :
  ' :

  DebugPrint "処理終了しました！"  

End Sub

# PowerShell

# AlternativeConsole というクラスをPowerShellで動的生成。
Add-Type -TypeDefinition @"
using System;

public class AlternativeConsole
{
    public void WriteLine(string line)     { Console.WriteLine(line); }
    public void Write(string line)         { Console.Write(line); }
    public void ErroWriteLine(string line) { Console.Error.WriteLine(line); }
    public void ErroWrite(string line)     { Console.Error.Write(line); }
}
"@

try {
  $excelApp = New-Object -com "Excel.Application"

  # 末尾の後ろ２つの引数は： UpdateLinks := $false, ReadOnly = $true
  $wb = $excelApp.Open((Make-Absolute "Book1.xlsm"), $false, $true)

  # AlternativeConsole インスタンスをインジェクト
  $excelApp.Run("'$($wb.name)'!RegisterConsole", (New-Object AlternativeConsole))

  $excelApp.Run("'$($wb.name)'!SomeProcessing")
  # ここで下記のようにPowerShell側のコンソールに出力される:
  #   処理開始しました！
  #   処理終了しました!

} finally {
  if ($excelApp) {
    $excelApp.EnableEvents = $false
    $excelApp.DisplayAlerts = $false
    $excelApp.Visible = $false

    $excelApp.Workbooks | % { $_.Close($false) }
    $excelApp.Quit()
  }
}

VBA内で Debug.Print を呼んでいる箇所を自前の DebugPrint 関数に置き換えるといい具合にコンソールログが出ると思います。

まとめ

要するに下記課題に対応できれば、JenkinsでVBAをスムーズに起動できるでしょう。

Excel VBA自体をサービスプロセス上で動かせるようにする or そもそもサービスプロセス上で起動させない
マクロをコンソール仕様にあわせる。
- マクロに応答要素を入れない。
- マクロからコンソールに処理状況を出力させる。
基本的にマクロとのやり取りは絶対パス。
ファイルのロックやプロセスの残滓が残らないようにする。

もし同じことをもっとうまくやっている人がいたら、是非どうやっているか私に教えてほしいです…。