VBA - PowerShell stdOut and waitOnReturn Required

[Zakkaroo]

I'm not the best at this, but it appears there are (at least) two ways to call a PowerShell script from vba one is to use .Run and the other is to use .Exec

RUN let's you add a parameter called waitOnReturn
EXEC let's you read the output of the script via .StdOut.ReadAll

But I need to do both at the same time.

Effectively, i'll be looping through a list of files, running the script multiple times - the script itself will have a delay that will be indeterminate - so i can't just use a normal VBA wait for xx seconds.

So, my question is - how do I both wait for the script to finish AND read the output of the script?

Here's an example of .Run (that i found on this forum) using the waitOnReturn

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim errorCode As Integer
    wsh.Run "POWERSHELL.EXE " & "\\PathToYourPowerShellScript\PowerShellScript.ps1", windowStyle, waitOnReturn
    MsgBox "Finished"

Here's an example of using .Exec and .StdOut

    strCommand = "Powershell -file ""C:\PowerShellScriptName.ps1"""
    Set WshShell = CreateObject("WScript.Shell")
    Set WshShellExec = WshShell.Exec(strCommand)
    strOutput = WshShellExec.StdOut.ReadAll
    Sheets("Sheet1").Range("A1").Value = strOutput

 

[John_w]

Try using Run and the PowerShell redirection operator (>) or the -Out-File cmdlet to redirect the output to a file and read that file:

Sub Test()

    Dim wsh As Object
    Set wsh = VBA.CreateObject("WScript.Shell")
    Dim waitOnReturn As Boolean: waitOnReturn = True
    Dim windowStyle As Integer: windowStyle = 1
    Dim errorCode As Integer
    Dim tempFile As String
        
    tempFile = ThisWorkbook.Path & "\Powershell_Output.txt"
    
    'Either
    wsh.Run "POWERSHELL.EXE " & "\\PathToYourPowerShellScript\PowerShellScript.ps1 > '" & tempFile & "'", windowStyle, waitOnReturn
    
    'Or
    'wsh.Run "POWERSHELL.EXE " & "\\PathToYourPowerShellScript\PowerShellScript.ps1 | Out-File -filePath '" & tempFile & "' -encoding ASCII", windowStyle, waitOnReturn
    
    Sheets("Sheet1").Range("A1").Value = CreateObject("Scripting.FileSystemObject").OpenTextFile(tempFile).ReadAll
    
    MsgBox "Finished"
    
End Sub

PS - the single quotes around tempFile might need to be double quotes.

 

[Zakkaroo]

Thanks for this John_w - that appears to be working perfectly (at least in my simple test)

Just to confirm, I had to use the 2nd option (Out-File) - as the first option runs, but reads nonsense characters back into Excel

 

[Zakkaroo]

This seems like a trivial additional question, but I can't seem to get it to work ... where / how would I put in the things I want to pass across as Parameters?

Specifically, where the Script is called here (see code below) - if i had a variable called "ScriptParam" that contained a string I wanted to pass through when running the PowerShell script, where do I put it in the below?

'wsh.Run "POWERSHELL.EXE " & "\\PathToYourPowerShellScript\PowerShellScript.ps1 | Out-File -filePath '" & tempFile & "' -encoding ASCII", windowStyle, waitOnReturn

I don't know if it's the combination of quotation marks etc. but wherever i put it, it doesn't seem to like it.

 

[Zakkaroo]

As a sample ... the line below runs and calls a PS script that basically spits back at you, whatever you send to it.
This bit of code works

wsh.Run "POWERSHELL.EXE C:\Basic-Input-Output.ps1 [B]Testing123 [/B]| Out-File -filePath '" & tempFile & "' -encoding ASCII", windowStyle, waitOnReturn

But instead of 'Testing123' - I'll have some text stored in a variable that I need to go here. I just can't seem to get that to work.

 

[Zakkaroo]

Just as an end-point to the thread - I figured it out ... it was all about the placement of quotation marks etc.


In this code, c.ODBCConnection.CommandText is what i needed to be passed to the script, but you could easily replace this with a Variable containing any string you like.

wsh.Run "POWERSHELL.EXE C:\PowerShell_Script.ps1 " & "'" & c.ODBCConnection.CommandText & "'" & "| Out-File -filePath '" & tempFile & "' -encoding ASCII", windowStyle, waitOnReturn