共有ディレクトリ上にあるExccelマクロを定時実行させる

1. 背景
2. 設計
3. 構築
4. 結果
5. 問題
6. 参考

背景

自分の担当しているところでも進捗管理は絶賛Excelによる管理だ。
そして、Excelを開いて組み込んであるマクロを当てこんだボタンをポチっと押して進捗状況の最新化を行っている。（毎日！）

そしてそれはとてつもなく面倒なので、Jenkinsによる定期実行で自動で進捗更新させることにしたことが始まり。

設計

１．jenkinsが一定の時間になるとローカルにあるbatファイルを起動する
２．batファイルは共有ディレクトリ上にあるvbsファイルを起動する
３．vbsファイルはExcelのマクロを起動する

なんとも回りくどい方法だと思う。
マクロを動かすためにバッチファイルを二つも作らなければならないとは・・・

構築

JenkinsExec_excel.bat

@echo off
setlocal enabledelayedexpansion
cd %~dp0

set CONNECT_IP=\\XXX.XXX.X.XX
set CONNECT_USER=\username
set CONNECT_PASS=password

set EXEDIR="\\XXX.XXX.X.XX\share"
set comment="私がJenkinsおじさんである"

set MACROMAIN=main_batch

set TARGETXLSM=sharedExcel.xlsm

net use %CONNECT_IP% %CONNECT_PASS% /user:%CONNECT_USER% >nul 2<&1

pushd %EXEDIR%

sharedExcel.vbs %EXEDIR%\%TARGETXLSM% %MACROMAIN%

popd

exit 0

主な仕事は共有ディレクトリへの接続作業。
さらにcommentという環境変数を設定し、後にExcelマクロ上で取得して値をセルに設定するようにしている。
その後、接続した共有ディレクトリ上にあるvbsファイルを叩いている。

sharedExcel.vbs

'argument: <ExcelFile> <MacroName>
'ex: "c:\xxx\yyy\zzz.xlsm" Macro1

Dim obj
Set obj=WScript.CreateObject("Excel.Application")

obj.Visible=False
obj.Workbooks.Open WScript.Arguments(0)
obj.Application.Run WScript.Arguments(1)

ここでbatファイルから受け取った引数を元にして起動対象のマクロがあるExcelを起動させている。

sharedExcel.xlsm

Option Explicit

'バッチ起動用マクロ
Sub main_batch()

    'STEP0 引数取得
    Dim args As String
    args = Environ("comment")
    
    'バッチによって実行した時刻をシートに追記するマクロを起動
    Call addData_batch(args)
    
    
    ThisWorkbook.Save
    Application.Quit

End Sub


'バッチによって実行した時刻をシートに追記する

Sub addData_batch(args As String)

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Dim row As Integer
    Dim outputSheet As String
    
    'シート指定
    outputSheet = "Sheet"
        
    '出力行
    row = Worksheets(outputSheet).Cells(Rows.count, "A").End(xlUp).row + 1
    '起動時刻を出力
    Worksheets(outputSheet).Range("A" & row).Value = Now
    
    '「はい」を出力
    Worksheets(outputSheet).Range("B" & row).Value = "はい"
    
    '引数を出力
    Worksheets(outputSheet).Range("C" & row).Value = args


End Sub

'マクロを実行した時刻をシートに追記する

Sub addData()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Dim row As Integer
    Dim outputSheet As String
    
    'シート指定
    outputSheet = "Sheet"
        
    '出力行
    row = Worksheets(outputSheet).Cells(Rows.count, "A").End(xlUp).row + 1
    '起動時刻を出力
    Worksheets(outputSheet).Range("A" & row).Value = Now
    
    '「いいえ」を出力
    Worksheets(outputSheet).Range("B" & row).Value = "いいえ"

End Sub

Jenkinsからマクロを起動
Excelを起動してマクロが設定されているボタンを押下
この2パターンのマクロを組んでみた。
手起動は「いいえ」と追記され、Jenkins起動は「はい」と追記される。
また、commentの内容も追記される。

Jenkins job

カスタムワークスペースとしてbatファイルのある場所を指定してcallしているだけ

結果

更新された。

問題

・JenkinsのあるサーバにExcelが入ってないといけない
（ということで必然的にWindowsOSに乗っかっているJenkinsだけが動作する）

・セキュリティの警告ポップアップが出て自動起動がアクセス拒否される。

　→インターネットオプション→セキュリティ→ローカルイントラネット→サイト→詳細設定→共有ディレクトリが設定されているIPを「このWebサイトをゾーンに追加する」に入力して追加を押す

参考

Excelマクロをバッチから実行する
 ファイルサーバー等の共有ファイル実行時に出る警告を非表示にする/Windows 10