CODEWITHSUNDEEP
vbapowershell
Brendan McDonnell
Brendan McDonnell

Reputation: 3

Powershell Console output not available

I am trying to run a pwershell script from excel, which i can do and it works, but the powershell window does not show the information from the write-host comands in the script. If i run the file from the cmd prompt i get all the write-host texts appearing in the console window, like below.

Powershell run from command prompt
[enter image description here]

However if i use this code from excel.

Sub RunAndGetCmd()

    strCommand = "Powershell -File ""C:\PSFiles\TestOutput.ps1"""
    Set WshShell = CreateObject("WScript.Shell")
    Set WshShellExec = WshShell.Exec(strCommand)

End Sub

I get no texts shown in the console window, just the a flashing cursor like the following

Same Powershell ran from excel enter image description here

I know the script runs and dooes everything, but i would like to output the texts as the script runs so i can see how far it has gotten.

Any ideas greatly appreciated.

Upvotes: 0

Views: 629

Answers (1)

mclayton
mclayton

Reputation: 10125

The WScript.Shell Exec command redirects the stdin, stdout and stderr streams so you can access them from your application. As a result, anything written to stdout by the external application (e.g. using write-host in PowerShell) gets redirected instead of being displayed in the external application's window.

If you want the output displayed in the application's window you can use the Run method instead - e.g.

Option Explicit

Sub RunAndGetCmd()

    Dim strCommand As String
    Dim WshShell As Object

    strCommand = "Powershell -File ""C:\PSFiles\TestOutput.ps1"""

    Set WshShell = CreateObject("WScript.Shell")
    WshShell.Run strCommand

End Sub

but if you're doing that, you might as well just use the built-in VBA Shell function:

Option Explicit

Sub RunAndGetCmd()

    Dim strCommand As String

    strCommand = "Powershell -File ""C:\PSFiles\TestOutput.ps1"""

    VBA.Shell strCommand

End Sub

Note also that you might want an -ExecutionPolicy RemoteSigned in your command in case the machine has it set to Restricted:

strCommand = "Powershell -ExecutionPolicy RemoteSigned -File ""C:\PSFiles\TestOutput.ps1"""

Upvotes: 2

Related Questions