2

I have a VBA script that is running PowerShell and submitting the results. The problem I am having is that the GUI window pops up and then disappears. Is there a way to keep the PowerShell GUI hidden completely while it runs?

Here is the meat of the code. I added -WindowStyle Hidden, which made the GUI disappear, but it still flashes up for a few seconds then goes away.

Do Until i = LRow + 1

ADGroup = Cells(i, 2)

' Construct PowerShell Command (PS syntax)
strPSCommand = "Get-ADGroupMember -Identity " & ADGroup & " -Recursive |select name"
Debug.Print strPSCommand

' Consruct DOS command to pass PowerShell command (DOS syntax)
strDOSCommand = "powershell -WindowStyle Hidden -command " & strPSCommand & ""

' Create shell object
Set objShell = CreateObject("Wscript.Shell")

' Execute the combined command
Set objExec = objShell.Exec(strDOSCommand)

' Read output into VBS variable
strPSResults = objExec.StdOut.ReadAll

Cells(i, 3).Value = strPSResults

i = i + 1

Loop
byte me
  • 770
  • 6
  • 13
Tyler East
  • 21
  • 3
  • 1
    It's not the powershell window, it's the shell window that's trying to execute powershell that stays on the screen. It's the `Exec()` method that brings it up and there isn't much you can do about that one – Jawad Feb 28 '20 at 13:45
  • Try Run with `,0(Hidden)` rather than Exec `Set objExec = objShell.Run(strDOSCommand),0` – jfrmilner Feb 28 '20 at 14:56

1 Answers1

1

powershell.exe is a console application. The console window is automatically created by the OS when the process starts. The powershell.exe code that processes -WindowStyle Hidden is therefore executed after the console window is opened hence the flash. To fix this, we would need the equivalent of wscript i.e. a win32 host application instead of a console host application.

wraith
  • 370
  • 4
  • 16