Skip to content

Instantly share code, notes, and snippets.

@knjname

knjname/ExcelOnJenkinsTips(没).md

Created February 14, 2015 02:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save knjname/b8900bcdcb87efa5b432 to your computer and use it in GitHub Desktop.
Save knjname/b8900bcdcb87efa5b432 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
ExcelOnJenkinsTips(没).md

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

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

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

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

Windowsでいえば、対話無しのバッチファイルかPowerShellスクリプトといったものですね。

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

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

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

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

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

対話を避けよう

Jenkins上のVBA内でメッセージがポップしたり、ダイアログが開くと、もちろん誰にも応答ができないので、そもそも自動化させたいマクロでこういう対話要素は一切使うのはやめましょう。 （ちなみにマクロでシンタックス・ランタイムエラー等が出ても誰にも応答できません…。）

個人的な好みを言わせてもらえば、対人間であってもマクロでダイアログは開いてほしくないものです。開発者向けのマクロに、ダイアログはほとんどの場合、要らない。 （処理終了のMsgBoxを鳴らしたいなら選べるようにしてくれるとありがたい。）

特にディレクトリを選ぶパスもペーストできない、あのダイアログをわざわざWin32API使って呼ぶ奴は頭がイカれてると断言していいと思います。 そういう腐った発想のマクロを一日数十回以上使わされる人の気持ちも想像できないんでしょうかね。

マクロの設定値が必要ならセルに書かせりゃいいんです。セルから値を拾うのが面倒なら、値の部分を名前付き範囲にすればいいんです。レイアウトを変えても平気。

マクロの構成としては実際、対人間用と対Jenkins用にマクロの入り口のプロシージャを分けるのが現実的でしょう。対Jenkins用は対話要素をOFFるための初期処理などさせればいいです。

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

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

  • コンパイルのいる言語でCOMを一生懸命呼び出す。
  • WSHで呼び出す。(cscript.exe とかで)
  • PowerShellで呼び出す。

僕はPowerShellで呼び出す、という選択肢を選びました。Windows7以降は標準で ≧ 2.0 が使えますし。

理由としては、Jenkinsのビルドプロセス中、VBAの呼び出し以外、 PowerShellを使った複雑な処理も多少は必要になる、あるいは複雑な処理の中にVBAを含めることもあり、 COMと.NETがあわさった手軽かつ強力なスクリプティング環境になっているからですね。

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

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

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

$macroBook = $excelApp.Workbooks.Open(マクロブックへの絶対パス)
$excelApp.Run("$($macroBook.name)!macroProcedure", "arg1", "arg2")

こういうマクロを呼び出すスクリプトをJenkinsから呼び出せばExcel VBAをJenkinsから呼び出すことができます！はい！話は簡単！

ちなみにPowerShellの呼び出しはPowerShell用のプラグインがJenkinsにあるのでそれを使いましょう。 ただしその時にSet-ExecutionPolicyでJenkinsが発行するテンポラリのPowerShellスクリプトを実行できるようにしておきましょう。(システム管理者権限が必要。)

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

bashの-eフラグのように処理中に何らかのエラーがあった場合に即時終了してほしい場合は下記のようにすればいいです。

$ErrorActionPreference="Stop"

EXCEL.EXE が増える…！増える！

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

ちゃんと後始末しましょう。ということで、下記のようなコードを用意しました。

try {
  $excelApp = New-Object -com "Excel.Application"
  // $excelApp を使った処理
} finally {
  if ($excelApp) {
    $excelApp.Quit()
  }
}

はい。ここで皆さんに悪いお知らせがあります。なんと $excelApp.Quit() してもプロセスが溜まっていきます。Quit() したのにプロセス終了しないことがある！

function Get-NumberOfExcels(){ (Get-Process | Where "ProcessName" -Is "excel").Length }

$s = Get-NumberOfExcels
(1..10) | % { (New-Object -com "ExcelApplication").Quit() }
$e = Get-NumberOfExcels

"$($e - $s) 個のExcelが死んでないよ!!"

そもそもビルドが完了した時点でExcelくんには完全に死んでいていほしいので、強制終了させてしまいましょう。

PowerShellであるプロセスを強制終了させるのは簡単で、プロセスのPIDがわかっていれば、下記で強制終了させることができます。

Stop-Process -Id プロセスのPID -Force

じゃあ、ExcelのプロセスのPIDはどうやってとるかって？$excelAppからダイレクトにはとれません。これをとるには、いくつか方法がありますが…

  • New-Object -com "Excel.Application" 前後のEXCEL.EXEのプロセスオブジェクトの集合の差をとって(Get-Processコマンドレットでとれます)、差分が出た部分を起動したExcelのプロセスとする。 → サーバがビジー状態の時、割りと誤爆します。関係ないEXCEL.EXEを強制終了する事故につながります。
  • Get-Process の結果中、 MainWindowHandle プロパティが $excelApp.HWND と等しいプロセスをExcelのプロセスとする。 → Excelを可視状態にしないと成り立たない条件です。

一番いいのは、Win32APIを使って $excelApp.HWND からプロセスIDを特定してしまう方法です。下記のような関数を使いましょう

Add-Type -TypeDefinition @"
using System;
using System.Runtime.InteropServices;

public static class Win32Api
{
  [System.Runtime.InteropServices.DllImportAttribute( "User32.dll", EntryPoint =  "GetWindowThreadProcessId" )]
  public static extern int GetWindowThreadProcessId ( [System.Runtime.InteropServices.InAttribute()] System.IntPtr hWnd, out int lpdwProcessId );
}
"@

function Get-ProcessIdFromHWND(){
  param($hwnd)

  $mypid = [IntPtr]::Zero
  [Win32Api]::GetWindowThreadProcessId( $hwnd, [ref] $mypid )
  $mypid
}

# Usage
Stop-Process -Id (Get-ProcessIdFromHWND $excelApp.HWND) -Force

http://stackoverflow.com/questions/4024113/get-pid-of-com-server

これで今のところ誤爆無しで EXCEL.EXE を殺せています。

起動前後のプロセスの差を取る方法の練度を高めるためにセマフォなど検討しましたが、面倒すぎる、跡が残るで諦めました。

これでも日々の運用には心臓に悪いので、1日1回Officeアプリケーションだのを全部殺すクリーニングジョブを流すこともおすすめしておきます。

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

ビルドの×ボタンを押された場合、大変残念ですが、PowerShellはそこで即座に息絶え、内部でEXCEL.EXEは死にません。アボートのトラップも少なくともPowerShell上では不可能です。何もできません。

というのも、JenkinsのWindows版のプロセスキラーの実装(https://github.com/kohsuke/winp/blob/master/native/winp.cpp) が TerminateProcess (https://msdn.microsoft.com/ja-jp/library/cc429376.aspx)を呼んでいるからですね。プロセスの強制終了です。finallyなど効くわけがなかろう！

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

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

とまあ、そもそもロックなんか気にしなくて済むように、読み取り専用で開いて済むものはなるべくそれで処理するようにしましょう。

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

WindowsはPIDを再利用します。OS起動後、プロセスごとに必ずユニークなPIDが振られると思ったら大間違いだ！

ということで、一筋縄ではうまくいかないでしょう。

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

PowerShellから起動されたExcelのインスタンスはシェルの相対パスなど理解してくれないので、マクロのブックのパスは絶対パスで渡す必要があります。

同様にExcel VBA内も今自分がどこにいるかなどといった情報は無いので（あるけど使えない）、 たとえば、マクロの処理対象となるフォルダや、処理結果出力先を渡す場合は、それも絶対パスで渡す必要があります。とにかく絶対パスを渡しておけば間違いありません。

以下は駄目な例と良い例。

# 駄目な例 (相対パスは使えない)
$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を起動できるのはわかった。でも、無言のままジョブが走っていても本当に処理されているのかわからない。今何をしているのかコンソールに表示したい。

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

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

' VBA

consoleObj As Object

Public Sub RegisterConsole(ByVal envConsoleObj As Object)
  Set consoleObj = envConsoleObj
End Sub

Public Sub DebugLog(ByVal logMessage$)
  If Not consoleObj Is Nothing Then
    consoleObj.
  End If
  Debug.Log logMessage$  
End Sub

# PowerShell

ConsoleがVBA上だとCOMInteropの無情なマングリング（？）のせいで、訳の分からないメソッド名になってますね。

まとめ

Windowsの知識が割りとあったら解決できそうな問題ばかりでした。僕にはないです。

もし同じことをもっとうまくやっている人がいたら、是非あなたも公開してほしい。

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment