VBA: WshShell.Exec
Excelマクロから簡単にPowerShellの出力結果を取得する方法を考える
' Excel 2019, Windows 11 (2024年4月頃) ' 参照設定 ' Windows Script Host Object Model Option Explicit Public Function f(ByVal cmd As String) Dim exitCode As Long Dim scOut As New VBA.Collection Dim scErr As New VBA.Collection Dim i As Long ' WshShell Object Dim wshShell As Object ' IWshRuntimeLibrary.wshShell Set wshShell = VBA.Interaction.CreateObject("WScript.Shell") ' WshScriptExec Object Dim wshScriptExec As Object ' IWshRuntimeLibrary.wshExec Set wshScriptExec = wshShell.Exec("PowerShell.exe -NoProfile -NonInteractive -WindowStyle Hidden -Command -") ' TextStream Object With wshScriptExec.StdIn .WriteLine cmd .Close End With ' TextStream Object With wshScriptExec.StdOut Do Until .AtEndOfStream scOut.Add .ReadLine() Loop .Close End With ' TextStream Object With wshScriptExec.StdErr Do Until .AtEndOfStream scErr.Add .ReadLine() Loop .Close End With Set wshScriptExec = Nothing Set wshShell = Nothing For i = 1 To scOut.Count Step 1 Debug.Print scOut.Item(i) Next i For i = 1 To scErr.Count Step 1 Debug.Print scErr.Item(i) Next i f = exitCode End Function Public Sub main() Debug.Print f( _ "Set-StrictMode -Version Latest" & VBA.Constants.vbCrLf & _ "Set-Location -LiteralPath ""${env:USERPROFILE}\Desktop""" & VBA.Constants.vbCrLf & _ "(Get-ChildItem).Name" & VBA.Constants.vbCrLf & _ "Write-Error ""Errorテスト""" & VBA.Constants.vbCrLf & _ "exit" _ ) End Sub
PowerShell: WshShell.Exec
COMオブジェクトの解放は気にしない
# PowerShell 5.1, Windows 11 (2024年4月頃) Set-StrictMode -Version Latest $sb = { param([Parameter(Mandatory)][ValidateNotNullOrEmpty()][string]$cmd) # StringCollection $scOut = [System.Collections.Specialized.StringCollection]::new() $scErr = [System.Collections.Specialized.StringCollection]::new() # WshShell Object $wshShell = [System.Activator]::CreateInstance([System.Type]::GetTypeFromProgID("WScript.Shell")) # WshScriptExec Object $wshScriptExec = $wshShell.Exec("PowerShell.exe -NoProfile -NonInteractive -WindowStyle Normal -Command -") # TextStream Object $textStreamIn = $wshScriptExec.StdIn $textStreamIn.WriteLine($cmd) $textStreamIn.Close() $textStreamIn = $null # TextStream Object $textStreamOut = $wshScriptExec.StdOut while (-not $textStreamOut.AtEndOfStream) { $null = $scOut.Add($textStreamOut.ReadLine()) } $textStreamOut.Close() $textStreamOut = $null # TextStream Object $textStreamErr = $wshScriptExec.StdErr while (-not $textStreamErr.AtEndOfStream) { $null = $scErr.Add($textStreamErr.ReadLine()) } $textStreamErr.Close() $textStreamErr = $null $exitCode = $wshScriptExec.ExitCode $wshScriptExec = $null $wshShell = $null $scOut | Write-Host -ForegroundColor Cyan $scErr | Write-Host -ForegroundColor Magenta return $exitCode } Invoke-Command -ScriptBlock $sb -ArgumentList @' Set-StrictMode -Version Latest Set-Location -LiteralPath "${env:USERPROFILE}\Desktop" (Get-ChildItem).Name Write-Error "Errorテスト" exit '@
PowerShell: ProcessStartInfo
Start-Processコマンドレットの-WaitパラメーターではなくProcess.WaitForExitメソッドを使いたい場合があるのでProcessStartInfoクラスを使う
# PowerShell 5.1, Windows 11 (2024年4月頃) Set-StrictMode -Version Latest $sb = { param([Parameter(Mandatory)][ValidateNotNullOrEmpty()][string]$cmd) $scOut = [System.Collections.Specialized.StringCollection]::new() $scErr = [System.Collections.Specialized.StringCollection]::new() # Process $p = [System.Diagnostics.Process]::new() # ProcessStartInfo $p.StartInfo.FileName = "PowerShell.exe" $p.StartInfo.Arguments = "-NoProfile -NonInteractive -WindowStyle Normal -Command -" $p.StartInfo.UseShellExecute = $false $p.StartInfo.RedirectStandardInput = $true $p.StartInfo.RedirectStandardOutput = $true $p.StartInfo.RedirectStandardError = $true $null = $p.Start() $p.StandardInput.WriteLine($cmd) $p.StandardInput.Close() $p.WaitForExit() # StreamReader while (-not $p.StandardOutput.EndOfStream) { $null = $scOut.Add($p.StandardOutput.ReadLine()) } $p.StandardOutput.Close() # StreamReader while (-not $p.StandardError.EndOfStream) { $null = $scErr.Add($p.StandardError.ReadLine()) } $p.StandardError.Close() $exitCode = $p.ExitCode $p.Dispose() $scOut | Write-Host -ForegroundColor Cyan $scErr | Write-Host -ForegroundColor Magenta return $exitCode } Invoke-Command -ScriptBlock $sb -ArgumentList @' Set-StrictMode -Version Latest Set-Location -LiteralPath "${env:USERPROFILE}\Desktop" Get-ChildItem | Format-Table -AutoSize | Out-String -Width 80 | Write-Host Write-Error "Errorテスト" exit '@
