JenkinsでExcel VBAを動かす方法

Jenkins VBA Excel

あまり幸福でない開発をしていると、プロジェクトのユビキタスフォーマット（）がExcelになっていて、そのうちJenkins上でExcelVBAを動かしたくなります。

サーバ上でExcelを動かすには諸問題があり、できればサーバサイドでExcelを動かさないに越したことはありません。

そこで、たとえばJavaであればPOIなどExcelブックを扱えるライブラリを検討するところですが、POIは下記のような問題もあり、そこまで魅力的ではありません。

  • 数式の計算がむずい（僕はやったことないです）
  • 新規行のセル書式の引き継ぎなどExcelVBAでの動きと多少異なる
  • セルの型の扱いが面倒くさい
  • 実装が面倒くさい
  • VBAが動かせない

（反面、POIは処理が高速であるというメリットがあります）

こういうこともあり、ExcelVBAマクロがJenkinsで動いてくれたらいいなあと思う人も大勢いると思い、このエントリーを書きました。

別にExcelVBAだけの手法ではないので、幅広くOfficeのオートメーション化に使えると思います。

動作環境の前提

  • Jenkins あるいは Jenkinsスレーブ が動作するWindowsマシン (今回はWindows7使いました。Linuxがマスターとかの場合はスレーブをWindows上で動くようにしてください。)
  • PowerShell がインストールされていること
  • Excel がインストールされていること

今回はJenkinsからPowerShellのスクリプトを起動、PowerShellのスクリプトからExcelVBAを起動して制御したいと思います。

実践

任意の新規PowerShellスクリプトを実行できるようにする

管理者権限でPowerShellスクリプトコンソールを開き、下記のコマンドを実行して任意の新規PowerShellスクリプトを実行できるようにしてください。

Set-ExecutionPolicy Unrestricted LocalMachine

JenkinsにてPowerShell Pluginを入れる

別にバッチの実行からpowershellコマンド叩いてもいいですが、専用のプラグインを入れてPowerShellを実行したいと思います。

マクロブックとPowerShellスクリプトを用意する

とりあえず例として、下記のようなマクロがモジュールとして入っているExcelのマクロブック(mymacro.xlsm)を適当なフォルダに用意します。

Sub outputFile(ByVal destPath$)

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    With fso.CreateTextFile(destPath)
        
        .WriteLine Application.Version
        
        .Close
        
    End With

End Sub

指定されたパスにExcelのバージョンを書き込むだけの簡単なマクロです。

このマクロを呼び出すため、同じフォルダに下記のようなPowerShellスクリプト(invoke-mymacro.ps1)を用意します。

# 各種変数の定義
$macroBook = ".\mymacro.xlsm"
$procedureName = "outputFile"
$outputFilePath = ".\output.txt"

$macroBookFullPath = (ls $macroBook).FullName
$macroProcName = (ls $macroBook).Name + "!" + $procedureName
$outputFileFullPath = Join-Path (pwd).Path $outputFilePath

# Excelの起動
$excelApp = New-Object -com "Excel.Application"

try {
    # マクロの起動
    $excelApp.DisplayAlerts = $false
    $excelApp.Workbooks.Open($macroBookFullPath)
    $excelApp.Run($macroProcName, $outputFileFullPath)
} finally {
    # Excelの終了
    $excelApp.DisplayAlerts = $false
    $excelApp.Quit()
}

やってることとしては、

  • Excelを立ち上げて(New-Object -com "Excel.Applicaiton")
  • Excelマクロブックを開いて($excelApp.Open(...))
  • プロシージャを引数とともに実行するように指示している($excelApp.Run(...))

だけですね。ExcelVBAができる人ならごく自然にわかると思います。

ためしに動かしてみる

上記スクリプトとマクロを保存したフォルダをPowerShellで開いて下記のようなコマンドを実行してみましょう。

.\invoke-mymacro.ps1

正しくセットアップできていれば、問題なくoutput.txtが同フォルダにできているはずです。

あとでわけわからなくなるので、output.txtは消しましょう。

原理的に、これを膨らませれば皆さんの使っているマクロがコマンドラインから動くことが理解できると思います。

JenkinsからExcelVBAを動かすPowerShellスクリプトを実行してみる

さて、問題はここからです。

まずは、Jenkinsで上記のフォルダをワークスペースとするジョブを作ってみましょう。

Jenkinsは特に "どこかのディレクトリ\ジョブのディレクトリ" をワークスペースにする必要はなく、必要に応じてディレクトリのパスを直接指定することができます。

下記のような感じになると思います。(今回はC:\jenkins-workspace にいろいろファイルを置きます)

f:id:knjname:20140323023944p:plain

ビルドのところに、Windows PowerShellを追加して、下記のようなコマンドを入れましょう。

$ErrorActionPreference = "Stop"
.\invoke-mymacro.ps1

1行目の$ErrorActionPreference = "Stop"はPowerShellスクリプトでエラーがあったら即スクリプトをエラーで終了させるという意味で、これを入れないとエラーでもどんどこ正常終了してしまうので、ぜひいれましょう。

入れ終わったら、実行してみましょう。

※なお、この時点でテンポラリのPowerShell自体の動作に問題がある場合は、Windowsバッチコマンドの実行で下記を実行するとうまくいくかもしれません。

powershell "set-executionpolicy unrestricted -force"

動かないよー！

たぶん下記のようなメッセージが出て動かないと思います。

ユーザーanonymousが実行
ビルドします。 ワークスペース: C:\jenkins-workspace
[jenkins-workspace] $ powershell.exe "& 'C:\Windows\TEMP\hudson5340555559273927371.ps1'"
"1" 個の引数を指定して "Open" を呼び出し中に例外が発生しました: "ファイル 'C:\j
enkins-workspace\mymacro.xlsm' にアクセスできません。次のいずれかの理由が考えら
れます。
? ファイル名またはパスが存在しません。
? ファイルが他のプログラムによって使用されています。
? 保存しようとしているブックと同じ名前のブックが現在開かれています。"
発生場所 C:\jenkins-workspace\invoke-mymacro.ps1:13 文字:26
+     $excelApp.Workbooks.Open <<<< ($macroBookFullPath)
    + CategoryInfo          : NotSpecified: (:) []、ParentContainsErrorRecordEx 
   ception
    + FullyQualifiedErrorId : ComMethodTargetInvocation
 
Build step 'Windows PowerShell' marked build as failure
Finished: FAILURE

それらしいエラーメッセージとともに、Excelの起動後、マクロのブックを開くところで失敗しています。

実はこのエラーメッセージは正しくありません。 ~~ 単純にLocal Serviceアカウントで動いているJenkinsプロセスがインタラクティブに動作しないため、関係のないエラーメッセージが出ているだけです。 ~~

~~ では、プロセスをインタラクティブにしましょう。 ~~

~~ サービスの一覧より、Jenkinsのサービスのプロパティを開いて、ログオンのところで、"デスクトップとの対話をサービスに許可"にチェックします。 ~~

f:id:knjname:20140323024043p:plain

~~ しかし、これをやった後にもう一度実行しても、同じエラーが出続けると思われます。 ~~

→ 2014/03/23追記 再度インタラクティブ解除してやってみましたが、特にインタラクティブにする必要はないみたいです。

ここで、もう一手必要です。下記を実行してください。

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

再度実行してみましょう。

下記のように、成功すると思います。

ユーザーanonymousが実行
ビルドします。 ワークスペース: C:\jenkins-workspace
[jenkins-workspace] $ powershell.exe "& 'C:\Windows\TEMP\hudson1185974244837218878.ps1'"


Application                      : System.__ComObject
Creator                          : 1480803660
Parent                           : System.__ComObject
AcceptLabelsInFormulas           : False
ActiveChart                      : 
ActiveSheet                      : System.__ComObject
Author                           : owner
AutoUpdateFrequency              : 0
AutoUpdateSaveChanges            : 
ChangeHistoryDuration            : 0
BuiltinDocumentProperties        : System.__ComObject
Charts                           : System.__ComObject
CodeName                         : ThisWorkbook
_CodeName                        : ThisWorkbook
CommandBars                      : 
Comments                         : 
ConflictResolution               : 1
Container                        : 
CreateBackup                     : False
CustomDocumentProperties         : System.__ComObject
Date1904                         : False
DialogSheets                     : System.__ComObject
DisplayDrawingObjects            : -4104
FileFormat                       : 52
FullName                         : C:\jenkins-workspace\mymacro.xlsm
（略）
PivotTables                      : System.__ComObject
Model                            : System.__ComObject
ChartDataPointTrack              : True
DefaultTimelineStyle             : System.__ComObject



Finished: SUCCESS

実際、ワークスペースを見れば、output.txtができているのが見えると思います。

これでExcel VBAがJenkins上で動きました。やったね！

（普通に使うときは、マクロ一式はリポジトリに入れて、ワークスペースは固定しないでつかってくださいね。）

まとめ

下記を満たせばだいたいExcelVBAは動くと思います。

  • ~~ Jenkinsを普通のユーザアカウントかLocal Serviceをインタラクティブ状態で動かす。 ~~
  • C:\Windows\system32\config\systemprofile\Desktop フォルダを作る。

ついでにマクロは下記を意識して作りましょう。

  • マクロの入出力ファイルパスをすべて指定できるようにする。
  • それらパスをすべてフルパスで外部から指定できるようにする。
  • 動作についてのパラメータもすべて指定できるようにする。手で動かすためのプロシージャとJenkins用のフルオートメーション用プロシージャをわけて定義するといいでしょう。
  • マクロからインタラクティブな要素（ポップアップなど）を一切排除できるようにする。当然ですが、Jenkins上で動いているマクロはユーザの入力をもらえません。

残る課題

  • Excel VBAが途中でエラーになる、ダイアログを出すなどすると、永久にJenkinsビルドが終了しない。
  • 上記が原因かわからないけど、なんかよくわからないままブック開きっぱなしで終わってたりする。
  • 上記の結果、Excelプロセスのごみが残る。手動でプロセス殺したりする必要があるのはイヤだ。

これを解消するために、ジョブが一定時間たったら殺すプラグインいれる、Excelのプロセスを定期的に掃除する、ちゃんとジョブ中でExcelプロセスを殺しきってあげるなど工夫が必要です。

また、Excelマクロがどこまで進んだか、外部にでないのでわかりづらいという問題もあります。（Debug.PrintがJenkinsに出ればいいのに。）

これについては、解消方法があるんで、後日紹介できたらと思います。

あと、今回はLocal ServiceでJenkinsを動かしましたが、個人的にはLocal Serviceじゃなくて、特定の実在アカウントで動かしたほうがいろいろ楽だと思います。普通にLocal Serviceだとユーザプロファイルに依存する設定がしづらいと思います。

さいごに

こんなことしなくていい世界にな～れ！

追記 2014/05/13

64bit環境だと求められるsystemprofile\Desktopフォルダの位置が異なるようです。

PowerShell とタスク スケジュールを使ってExcelファイルの自動処理・印刷を行う方法 - 元「なんでもエンジニ屋」のダメ日記 Excel 2007 automation on top of a Windows Server 2008 x64

C:\Windows\SysWOW64\config\systemprofile\Desktop にフォルダを作成しましょう。