前提・実現したいこと

VBAからpowershellスクリプトを利用したいです。
尚且つ非同期で実行したいです。

ホントは引数や返値で配列、もっと欲を言えばオブジェクトをやり取りしたいところですが
引数はVBA側で自身の最新状態のコピーを作成してpowershell側でそれを別プロセスで開いてアクセスすればいけそう。。。
返値もpowershell側でExcelを開ければVBA側でオブジェクトを特定できる文字列が返せる気がするので
後回しにします...(でもいい方法があれば知りたいです!)

powershell非表示で実行で調べるとWshのRunを使って非表示で実行する方法が出てくるのですが
返値がテキストファイルに書き出して読み込みとかしないと受け取れなかったりとか
Exec　ならステータスの確認が出来たり,　stdin なるものが何かに使えそうだと思ったので
今のところはそちらを使いたいと考えています。(もっといい方法があればためらいなく乗り換えます。)

試したこと

とりあえず powershellのウィンドウが一瞬表示されてしまうのは
諦めて作成してみました。
windows script host object modelを参照設定しています。

使用例ではStdOut.ReadAllを実行した際処理が終わってなければ勝手に待機してくれることが分かったので
Statusの確認はしていません。

Run等で実行した後にプロセスを取得できないか?等といろいろ探してみたのですがいい方法が見つけられませんでした。

何か良い手はないのでしょうか

作成したコード

vba
1Function MyPowershell(Optional ByVal ScriptName As String, Optional ByVal FunctionName As String, Optional Argument As String, Optional Exec As WshExec, Optional ByVal StdinClose As Boolean = True) As WshExec
2'Powershellスクリプトを実行してWshExecオブジェクトとして返す
3
4  Dim Wsh As New WshShell
5    If StdinClose And FunctionName <> "" And Exec Is Nothing Then
6      '処理一括実行
7      Dim Cmd As String
8        If ScriptName <> "" Then
9            Cmd = ". '" & ScriptName & "';"
10        End If
11        Set Exec = Wsh.Exec("powershell -NoLogo -ExecutionPolicy RemoteSigned -windowstyle hidden -command " & "Set-Location -Path('" & ThisWorkbook.Path & "');" & Cmd & FunctionName & " " & Argument)
12    Else
13      '処理随時実行
14        If Exec Is Nothing Then
15            Set Exec = Wsh.Exec("powershell -NoLogo -ExecutionPolicy RemoteSigned -windowstyle hidden")
16        End If
17        Call Exec.StdIn.WriteLine("Set-Location -Path('" & ThisWorkbook.Path & "')")
18        If ScriptName <> "" Then
19            Call Exec.StdIn.WriteLine(". '" & ScriptName & "'")
20        End If
21        If FunctionName <> "" Then
22            Call Exec.StdIn.WriteLine(FunctionName & " " & Argument)
23        Else
24            StdinClose = False
25        End If
26        If StdinClose Then
27            Exec.StdIn.Close
28        End If
29    End If
30        Set MyPowershell = Exec
31End Function
32
33Function MyPowershellStdOut(ByVal Exec As WshExec) As String
34'Powershellの返値を余分な文字を削除して返す
35
36  '標準出力を受け取る
37  Dim Str As String
38    Str = Exec.StdOut.ReadAll
39
40  '除外対象の文字列の削除
41  Dim BefStr As String
42  Dim RegExp_ As New RegExp
43    RegExp_.Pattern = "PS .:\.+?\n"
44    Do
45        BefStr = Str
46        Str = RegExp_.Replace(BefStr, "")
47    Loop While (BefStr <> Str)
48    RegExp_.Pattern = "PS .:\.+?> $"
49    Str = RegExp_.Replace(BefStr, "")
50    BefStr = Str
51    RegExp_.Pattern = "\n$"
52    Str = RegExp_.Replace(BefStr, "")
53    MyPowershellStdOut = Str
54End Function

実行されるpowershellスクリプト

powershell
1function test([String]$Str){
2    Start-Sleep -s 3; write-host $Str
3}

使用例

vba
1Sub test()
2'LoopCountに設定されている数分同時実行を行う
3'Boundary　の指定以上は随時実行に変更
4
5  Const LoopCount As Long = 10
6  Const Boundary As Long = 5
7
8  Dim Exec() As WshExec
9  Dim i As Long
10    ReDim Exec(LoopCount)
11    For i = 0 To LoopCount
12        If i <= Boundary Then
13            Set Exec(i) = MyPowershell(".\t est.ps1", "test", """入力テスト" & i & "`r`n二行目の入力テスト""")
14        Else
15            Set Exec(i) = MyPowershell(".\t est.ps1")
16        End If
17    Next i
18
19  '追加の実行
20  Dim j As Long
21    For j = 0 To LoopCount
22        If j > Boundary Then
23            Set Exec(j) = MyPowershell(, "test", """入力テスト" & j & "`r`n二行目の入力テスト""", Exec(j))
24        End If
25        
26    Next j
27  
28  '結果の取得
29  Dim Str() As String
30  Dim k As Long
31    ReDim Str(LoopCount)
32    For k = 0 To LoopCount
33        Str(k) = MyPowershellStdOut(Exec(k))
34    Next k
35
36  '結果の表示
37  MsgBox Join(Str, vbCrLf)
38End Sub

行動規範の内容に同意します

回答2件

ベストアンサー

よく使われている（私が知っている）COMオブジェクトの範囲では、条件を完全に満たすものは無いと思われます。

ぱっと思いついた方法を簡単にまとめたので参考になれば。

PowerShellの実行方法

`WshShell.Exec()`で実行し、`WshExec`を受け取る

ウィンドウが一瞬表示される
文字で表現出来る情報しか返せない

という点が許容できれば一番無難な方法。

`VBA.Shell()`や`WshShell.Run()`で終了を待たずに実行

ウィンドウを完全に非表示にしたい場合。
結果を受け取るには外部の何かを使う必要がある。

終了を待たない場合、WshShell.Run()を使う意味はほとんど無い。

結果を受け取る方法

WshExecを使っている場合はそちらから読み取れば良いので割愛。

ファイルに出力

基本文字列情報のやり取り。

ファイル名の生成にはFileSystemObject.GetTempName()を使う手も（到底一意とは言えませんが）。

クリップボード経由で受け取り

ユーザー操作に影響を与える割に扱いにくい。

PowerShellから任意のVBAプロシージャをコールバック

非常にデバッグが困難、お互いの結合が強くなりますが一つの手として。

Excel・PowerPoint・Wordなど一部のVBAホストは、COM操作で外部から任意のVBAのプロシージャを実行出来る機能を提供しています。

PowerShellはCOM操作ができるため、上記の機能でVBAに直接情報を送ることが出来ます。

やり取りとしては以下のようなパターンが考えられます。

直接VBAの処理を呼ぶ（お互いの依存がより強くなる）
適当なSetterを定義しておき、PowerShellはSetter経由で情報を渡す。あとはVBAが適当なタイミングで取りに行く（外乱の影響が大きい）

なお、標準コマンドレットではExcel・Wordの既存のインスタンスを取得できないため、.NETのメソッドを直接呼び出す必要があります（PowerPointはシングルインスタンスなのでOK）。

処理の中断方法

安全性を度外視するならPowerShellプロセスの終了が手っ取り早いでしょう。

WshShell.ExecならTerminate メソッド (WshScriptExec)が使用できます。

Terminate メソッド (WshScriptExec)
Terminate メソッドは最後の手段としてのみ使用します。

WshShell.Runから実行した場合は不可能ですが、VBA.Shellなら返り値がプロセスIDとなります。
そのため、WMIなどを使えば該当プロセスの取得・終了ができるはずです。

プロセスの終了以外となるとPowerShell側からVBAに何か状況を問い合わせる必要があります。

VBAプロシージャコールバックのサンプル（Excel用）

PowerShellを10個起動して、コールバックでそれぞれの番号をDebug.Printさせています。
AsyncPsExecTestを実行するとAsyncPsExecTest終了後にバラバラに結果が出力されるのがわかると思います。

vba
1Sub CallBack(a)
2    Debug.Print a
3End Sub
4
5Sub AsyncPsExecTest()
6    Const PS_CMD_BASE = _
7            "PowerShell.exe -NoProfile -Command " & """" & _
8                "$appXl = [Runtime.InteropServices.Marshal]::BindToMoniker('{0}').Application;" & _
9                "$appXl.Run('{0}!CallBack' , {1} )" & """"
10    
11    Debug.Print "AsyncPsExecTest Start"
12    
13    Dim i As Long
14    For i = 1 To 10
15        Dim psCmd As String
16        psCmd = VBA.Replace(VBA.Replace( _
17            PS_CMD_BASE, _
18                "{0}", ThisWorkbook.Name), _
19                "{1}", i)
20        
21        Dim pId As Double
22        pId = VBA.Shell(psCmd, vbHide)
23        Debug.Print "ProcessID:="; pId, i
24    Next i
25    Debug.Print "AsyncPsExecTest End"
26End Sub

投稿2018/08/11 04:13

編集2018/08/11 05:30

imihito

総合スコア2166

kamikazelight

2018/08/11 04:52

＞WshShell.Exec()で実行し、WshExecを受け取る試したことに書いてあります。クリップボードは思いつきませんでした。ですが上司の指示でExcel機能の履歴管理ができるクリップボードを使っているからクリップボードは使うなと言われていので使えないです。 >PowerShellから任意のVBAプロシージャをコールバックこの場合非同期での利用は厳しいですよね?

imihito

2018/08/11 05:35

非同期でコールバックするサンプルを追加しました

kamikazelight

2018/08/11 05:59 編集

コールバックする方法を知らなかったので勉強になりました。あとVBA.Shellなら非表示で実行出来る上にIDも取得できるのですねただ、毎回powershellからの戻り値の処理内容と利用用途が違うのでこれだとそれぞれの実行結果を合わせて使いたいときどうしたらいいのでしょうか

imihito

2018/08/11 06:05

コールバック処理は値の格納だけして、格納した値をVBA側から参照すれば良いでしょう

kamikazelight

2018/08/11 06:22

なるほど、そうすると格納先はモジュール変数などになるのでしょうか? 同時に異なる処理を複数実行する事が前提なのでそれぞれに格納場所を用意しないといけないですよね? 処理の内容が違うので実行した順番とコールバックの順番が同じとは限らないのでpowershellを実行する時に処理を特定するための情報の付加と戻り値にもその情報が入ってないとどの値がどの処理の結果か分からなくなってしまう気がするのですが、どうなのでしょうか?

imihito

2018/08/11 06:28

結果はモジュール変数などに格納する必要がありますね PowerShellへの引数としてキーとなる文字列を渡して、コールバック時はそのキーを使ってScripting.Dictionaryにでも格納すればよいでしょう

kamikazelight

2018/08/11 07:26

ごめんなさい何度やっても、いろいろ変えてみてもコールバックが働かないです............

imihito

2018/08/11 07:35

ShellのvbHideの指定を外す、-NoExitオプションを付けるなどしてコンソールを残すようにし、エラーが発生していないか確認してみてください非常に強引な方法のため、このようにデバッグが難しく、ある程度自己解決できないと難しいです

kamikazelight

2018/08/13 01:50

Add-Type -AssemblyName Microsoft.Visualbasic を入れなければならないのを見逃してました。まだ、確認途中ですがうまく行きそうです。今回もありがとうございました。