ExcelVBAからコマンドプロントの呼び出し

概要

　ExcelVBAからJavaプログラムを呼び出すとき色々ググってました。
　一応見つかるには見つかるのですが、気になった他の情報を調べてたりしてしまったので、自分なりにまとめてみました。

1.参照設定をする

　VBAのメニューバー>ツール>参照設定からWindowsScriptHostObjectModelを参照するようにします。

WindowsScriptHostObjectModelにはVBAからコマンドを呼び出すためのWshShellクラス等が含まれています。

2.サンプルソースと実行例

　VBAのサンプルソースは以下のものです。

VBAサンプルソース：選択したセルのコマンドを実行する。

'コマンド実行関数の戻り値の構造体です。
Type CommandOutput
    '実行したコマンドの標準出力を格納します。
    outLst As New Collection
    '実行したコマンドの標準エラー出力を格納します。
    errLst As New Collection
End Type


'入力された引数commandをコマンドプロントで実行し、
'CommandOutputに標準出力と標準エラー出力を格納して返却します。
Function コマンド実行関数(command As String) As CommandOutput
    Dim winShell As New IWshRuntimeLibrary.WshShell
        
    Dim response As WshExec: Set response = winShell.exec("%ComSpec% /c " & command)
    
    Dim output As CommandOutput
    Do While response.StdOut.AtEndOfStream = False
        output.outLst.Add (response.StdOut.ReadLine)
        DoEvents
    Loop
    
    Do While response.StdErr.AtEndOfStream = False
        output.errLst.Add (response.StdErr.ReadLine)
        DoEvents
    Loop
    
    コマンド実行関数 = output
End Function

'選択したセルに記載されたコマンドを実行し
'実行後、下のセルに実行結果を出力します。
Sub コマンド実行()
    Dim targetCell: Set targetCell = ActiveCell
    Dim result As CommandOutput: result = コマンド実行関数(targetCell.Value)
    Set targetCell = targetCell.Offset(1, 0)    
    targetCell.Value = "***標準出力***"

    Dim line As Variant
    For Each line In result.outLst
        Set targetCell = targetCell.Offset(1, 0)
        targetCell.Value = line
    Next line
    If result.errLst.Count > 0 Then
        Set targetCell = targetCell.Offset(1, 0)
        targetCell.Value = "***エラー出力***"
        For Each line In result.errLst
            Set targetCell = targetCell.Offset(1, 0)
            targetCell.Value = line
        Next line
    End If
    MsgBox "コマンドが完了しました"

End Sub

　実行方法はコマンドを記述したセルを選択して、Subプロシージャの"コマンド実行"を呼び出すと結果が下のセル以降に出力されます。
　正常に終了する場合の実行例は以下のものです。
実行前

実行後

　echoコマンドは入力された文字列をそのまま返却するコマンドなので、hogeが結果として出力されます。
　エラーの場合の実行例は以下のものです。
実行前

実行後

　echooというコマンドは存在しないのでエラーが出力されます。

3.解説

3.1.コマンドの実行部分

サンプルソース#コマンド実行関数呼び出し部分

Sub コマンド実行()
    Dim targetCell: Set targetCell = ActiveCell
    Dim result As CommandOutput: result = コマンド実行関数(targetCell.Value)

　上記部分は選択したセルの値をコマンド実行関数の引数として渡して呼び出してます。
　選択したセルはActiveCellで取得してから一時変数targetCellに格納し、Value属性で値を取得しコマンド実行関数に引き渡してます。

サンプルソース#コマンド実行関数冒頭

'入力された引数commandをコマンドプロントで実行し、
'CommandOutputに標準出力と標準エラー出力を格納して返却します。
Function コマンド実行関数(command As String) As CommandOutput
    Dim winShell As New IWshRuntimeLibrary.WshShell

    Dim response As WshExec: Set response = winShell.exec("%ComSpec% /c " & command)

　上記は実際にコマンドプロントからコマンドを実行してます。Dim winShell As New IWshRuntimeLibrary.WshShellでWshShellオブジェクトを作成してます。
　WshShellオブジェクトのexecメソッドに入力引数commandを渡してコマンドを実行します。
　%ComSpec%にはcmd.exeのパスが格納されており、/cはcmd.exeのオプションです。cmd /c 実行するコマンドでコマンドが実行できます。
　cmdについてのMicroSoft公式ガイドのリンク先は以下のものです。
リンク先：https://docs.microsoft.com/ja-jp/windows-server/administration/windows-commands/cmd

3.2.実行結果の取得部分

サンプルソース#コマンド実行関数の戻り値の一時変数宣言部分

    Dim output As CommandOutput

上記部分の型は次の部分で宣言した構造体を使用してます。

サンプルソース#戻り値の構造体

'コマンド実行関数の戻り値の構造体です。
Type CommandOutput
    '実行したコマンドの標準出力を格納します。
    outLst As New Collection
    '実行したコマンドの標準エラー出力を格納します。
    errLst As New Collection
End Type

　標準出力と標準エラー出力をそれぞれ１行ずつ可変長配列で格納したいのでCollectionクラスを指定してます。
　また、CollectionクラスのMicrosoft公式ガイドのリンク先は以下のものです。
リンク先：https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/collection-object

サンプルソース#コマンド実行結果取得

    Do While response.StdOut.AtEndOfStream = False
        output.outLst.Add (response.StdOut.ReadLine)
        DoEvents
    Loop
    
    Do While response.StdErr.AtEndOfStream = False
        output.errLst.Add (response.StdErr.ReadLine)
        DoEvents
    Loop
    
    コマンド実行関数 = output
End Function

　上記部分では実行結果を戻り値の構造体に格納してます。
　WshExec.StdOutとWshExec.StdErrはTextStreamクラスでできてます。
　TextStreamクラスのMicrosoft公式ガイドのリンク先は以下のものです。
リンク先：https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/textstream-object
　TextStreamクラスの.ReadLineメソッドでコマンド１行を読み取ってます。
　読み取った文字列はCollectionクラスの.addメソッドにて構造体で定義したコレクションオブジェクトのoutLstとerrLstに格納してます。
　TextStreamクラスの.AtEndOfStream はTextStreamクラスが出力を最後まで読み取るとTrueを返却するため、Do While～Loop文にて最後までコマンドを読み取ることができます。
　また、DoEvents関数は他の操作を処理させることができる関数なので、コマンド実行中にExcelを操作可能にできるようになります。DoEvents関数を削除すると、コマンドの実行が完了するまで操作できなくなります。
　DoEvents関数のMicrosoft公式ガイドのリンク先は以下のものです。
リンク先：https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/doevents-function
　そして最後にコマンド実行関数 = outputで関数プロシージャ名に戻り値を代入することにより値を返却してます。

3.3.実行結果取得の捕捉

　余談ですが、読み込み部分についてですが以下のようなTextStreamクラスの.ReadAllメソッドを用いたコードによる紹介のされ方も多く拝見します。

ReadAllを用いたコマンド実行結果取得サンプルコード

    Dim response As WshExec: Set response = winShell.exec("%ComSpec% /c " & command)
    Dim outputString As String
    Do While response.Status = WshRunning
        DoEvents
    Loop
    outputString = response.StdOut.ReadAll

　WshExecクラスのStatusはコマンドが実行中であればWshRunningを返却します。WshRunningは先ほど参照したWindowsScriptHostObjectModelで定義されている定数で、実際の値は0です。したがって、Do While response.Status = WshRunningについては以下のような記載もできます。

WshRunningを書き直したサンプルコード

Do While response.Status = 0

　WindowsScriptHostObjectModelにはStatus用の定数として他にもWshFinished(実際の値は1)とWshError(実際の値は2)が定義されてます。WshFinishedはコマンドの終了、WshErrorはコマンドのエラー終了とされているようです。しかし、前述の実行例のechoo hogeのようなエラー出力がされるコマンドでもStatusが1(WshFinished)になってしまいましたので、どのようなタイミングで2(WshError)になるのかはわかりませんでした。そのため、実行中か実行が完了したかの判定はWshRunningかWshRunning以外かで判定するのが堅実のようです。
　また、ReadAllの場合、コマンドプロントに出力される文字数2047文字を超えると.Statusの値がWshRunningのままになり、コマンドプロントの応答が反って来なくなる問題があります。出力される文字数についてのバッファはTextStreamクラスのReadLineメソッドはバッファに貯めてから文字列を読み取るらようで、自分の環境では１行１万文字以上もWshRunningのままになることなく可能でした。

　コマンドの文字数制限についてのMicrosoft公式ガイドリンク先は以下のものです。
リンク先：https://docs.microsoft.com/ja-jp/troubleshoot/windows-client/shell-experience/command-line-string-limitation

　また、以下のようにTextStreamクラスのreadメソッドを使うことでもコンソールの文字数制限は回避可能なようです。

Readを用いたコマンド実行結果取得サンプルコード

    Dim outputString As String
    Do While response.Status = WshRunning
        outputString = outputString & response.StdOut.Read(1)
        DoEvents
    Loop
    outputString = outputString & response.StdOut.ReadAll

　TextStreamクラスのreadメソッドの引数は取得する文字数です。outputString = outputString & response.StdOut.Read(1)で文字列を１文字ずつ結合してます。最後のresponse.StdOut.ReadAllでまだ読み取ってない文字列を全て読み取ってます。

3.4.実行結果の出力部分

サンプルソース#文字列「標準出力」部分

    Dim result As CommandOutput: result = コマンド実行関数(targetCell.Value)
    Set targetCell = targetCell.Offset(1, 0)
    targetCell.Value = "***標準出力***"

　一時変数targetCellには選択していたセルのオブジェクトが設定されているため、Set targetCell = targetCell.Offset(1, 0)で選択したセルの１行下のセルのオブジェクトを取得し、再度一時変数targetCellに設定しています。その後、targetCell.Valueによって文字列を書きこんでます。

サンプルソース#標準出力の出力部分

    Dim line As Variant
    For Each line In result.outLst
        Set targetCell = targetCell.Offset(1, 0)
        targetCell.Value = line
    Next line

　Dim line As VariantではCollectionクラスに格納されたオブジェクトはVariant型になってしまうため、Variant型を指定してます。
　For Each line In result.outLstでCollectionクラスに対してFor Each～Nextを実行することにより、.addメソッドによって格納した要素全ての書き込みを実現してます。ここでの一時変数lineはCollectionクラスに.addメソッドで格納した要素です。

サンプルソース#エラー出力の出力部分

    If result.errLst.Count > 0 Then
        Set targetCell = targetCell.Offset(1, 0)
        targetCell.Value = "***エラー出力***"
        For Each line In result.errLst
            Set targetCell = targetCell.Offset(1, 0)
            targetCell.Value = line
        Next line
    End If

　CollectionクラスのCountは要素の数を示しています。If result.errLst.Count > 0 Thenでエラー出力がない場合は文字列エラー出力を出力しないようにしてます。
　また捕捉ですが、標準出力の先頭の要素一つを取り出したい場合はCollectionクラスのインデックスは１から始まるので、以下のように記載します。

１行目だけ取り出す場合

'resultはサンプルソースで記載した戻り値の構造体CommandOutput
'outLstがCollectionクラス
result.outLst.Item(1)

おわりに

今回の投稿ではExcelVBAからコマンドプロントの呼び出しまで記載しました。Javaの呼び出し実行例や実行速度の検証についてはまた今度書こうと思います。