Get output from Powershell commands in VB or VBA.

		
$ProcessInfo = New-Object -TypeName System.Diagnostics.ProcessStartInfo
$ProcessInfo.FileName = 'myprogram.exe'
$ProcessInfo.RedirectStandardError = $true
$ProcessInfo.RedirectStandardOutput = $true
$ProcessInfo.UseShellExecute = $false
$ProcessInfo.WindowStyle = 'Hidden'
$ProcessInfo.Arguments = 'Normal command line arguments here'
$Process = New-Object -TypeName System.Diagnostics.Process
$Process.StartInfo = $ProcessInfo
		
$null = $Process.Start()
$Process.WaitForExit()

Select allOpen in new window

Option Explicit

Const ForReading   = 1
Const ForWriting   = 2
Const ForAppending = 8

Const TristateUseDefault = -2
Const TristateTrue       = -1
Const TristateFalse      = 0

Sub e (str)
	WScript.Echo str
End Sub

Function ErrorReport (str)
	If Err.Number Then
		ErrorReport = True
		e "Error 0x" & CStr (Hex (Err.Number)) & " occurred " & str
		If Err.Description <> "" Then
	                e "Error description: " & Err.Description & "."
		End If
		Err.Clear
	Else
		ErrorReport = False
	End If
End Function

Function TmpFolder
    Dim temp: temp = oShell.ExpandEnvironmentStrings("%Temp%")
    e "TmpFolder = " & temp
    TmpFolder = temp
End Function

Function TmpFileName
    Dim objRan

    Set objRan = CreateObject("System.Random")
    Dim ran:  ran  = objRan.Next_2( 1, 10000 )
    Set objRan = Nothing

    Dim temp: temp = TmpFolder
    Dim file: file = temp & "\" & ran

    '' file is the name of the file where your PowerShell output is redirected
    e "TmpFileName = " & file

    TmpFileName = file
End Function

Function DummyPowerShellScript
    Dim file, oFile

    file = TmpFolder & "\" & "dummy.ps1"
    e "DummyPowerShellScript = " & file

    Set oFile = oFS.OpenTextFile(file, ForWriting, True, TristateFalse)
    If ErrorReport ("while creating " & file) Then
        WScript.Quit 1
    End If

    oFile.WriteLine "write-host -fore green powershell output"
    oFile.WriteLine "dir"
    oFile.Close
    If ErrorReport ("while closing " & file) Then
        WScript.Quit 1
    End If

    Set oFile = Nothing

    DummyPowerShellScript = file
End Function

Function DummyBatch (psFile, temp)
    Dim file, oFile

    file = TmpFolder & "\" & "dummy.bat"

    Set oFile = oFS.OpenTextFile(file, ForWriting, True, TristateFalse)
    If ErrorReport ("while creating " & file) Then
        WScript.Quit 1
    End If

    oFile.WriteLine "powershell.exe -NoExit -Command " & Chr(34) & _
    "& { " & _
        "[Console]::Title='RC PS Processor'; " & _
        "[IntPtr]::Size; " & _
        psFile & " | Out-File -LiteralPath '" & temp & "' -Encoding ascii; " & _
        "sleep 2; " & _
        "exit; " & _
    "}" & _
    Chr(34)

    oFile.Close
    If ErrorReport ("while closing " & file) Then
        WScript.Quit 1
    End If

    Set oFile = Nothing

    DummyBatch = file
End Function

Sub ReadTmpFile(file)
    Dim objFile, strLine

    WScript.Echo "About to open = " & file

    ''On Error Resume Next

    Set objFile = oFS.OpenTextFile(file, ForReading)
    If ErrorReport ("while opening " & file) Then
        WScript.Quit 1
    End If

    Do While Not objFile.AtEndOfStream
        strLine = objFile.ReadLine()
        '' Do something with strLine
        e "strLine = " & strLine
    Loop

    objFile.Close
    If ErrorReport ("while closing " & file) Then
        WScript.Quit 1
    End If

    Set objFile = Nothing
End Sub

Sub Wait (sec)
    Dim temp

    temp = Timer
    Do While Timer-temp<sec
    Loop
End Sub

    ''
    '' Main
    ''

    ''On Error Resume Next

    Dim oShell, oFS
    Dim temp, file, psFile

    Set oShell = WScript.CreateObject("WScript.Shell") 
    If ErrorReport ("while creating WScript.Shell") Then
        WScript.Quit 1
    End If

    Set oFS = CreateObject("Scripting.FileSystemObject")
    If ErrorReport ("while creating Scripting.FileSystemObject") Then
        WScript.Quit 1
    End If

    psFile = DummyPowerShellScript
    temp   = TmpFileName

    file   = DummyBatch(psFile, temp)

    '' use /k if you want the cmd.exe session to hang around
    ''oShell.Run "cmd.exe /k " & file
    oShell.Run "cmd.exe /c " & file

    Wait 1
    ReadTmpFile(temp)

    Set oFS    = Nothing
    Set oShell = Nothing

Select allOpen in new window