VBA: WshShell.Exec("PowerShell -Command -")

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
'@