Ytukyg

The following requirement(s) I have:

As domain admin user logged on to an administrative client machine
I want to perform some changes on an exchange server using calls
from vba(excel 2013) via powershell to an exchange server (2013).
The client machine runs Windows 10 (1809) and powershell v5.1.17763.1

Upon a button press in the vba excel form I want to perform a trivial
task like getting all info for a specific mailbox user, reading
the results back in from stdout/stderr using WSH.Shell, later on more to come.

Executing the command below does what it shall, with the following two drawbacks:

1) the credentials are still asked again for though already passed to the ScriptBlock as $Cred via -ArgumentList

2) the powershell window does not close automatically after processing, it needs
to be closed actively by the user

Finally, the retrieved stdout/stderr gets me what I want (by the way, is there a direct connection possible as to have the powershell objects retrieved into a vba collection?)

WORKS on commandline (a "one-liner"), yet have to provide credentials via popup:

powershell -Command { $Username = 'MYDOMAINAdministrator'; $Password = 'foobar'; $pass = ConvertTo-SecureString -AsPlainText $Password -Force; $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass; Invoke-Command -ComputerName Exchange -ArgumentList $Cred -ScriptBlock { $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://exchange.somewhere.com/PowerShell/ -Authentication Kerberos -Credential $Cred; Import-PSSession $Session; Get-Mailbox MYUSER; Remove-PSSession $Session } }

WORKS from vba via WSH.Shell Exec, yet have to provide credentials via popup and have to actively close the powershell console window
(and see me avoiding double quotes (") within the powershell script, havent figured out yet how to escape them correctly ("" doesnt work)):

powershell -Command "& { $Username = 'MYDOMAINAdministrator'; $Password = 'foobar'; $pass = ConvertTo-SecureString -AsPlainText $Password -Force; $Cred = New-Object System.Management.Automation.PSCredential -ArgumentList $Username,$pass; Invoke-Command -ComputerName Exchange -ArgumentList $Cred -ScriptBlock { $Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri http://exchange.somewhere.com/PowerShell/ -Authentication Kerberos -Credential $Cred; Import-PSSession $Session; Get-Mailbox MYUSER; Remove-PSSession $Session } }"

So basically it is writing
powershell -Command "& { ... }"
in vba when called via 'wsh shell exec' instead of
powershell -Command { ... }
on the commandline, this seems to be required to retrieve stdin/stdout correctly, would be glad for suggestions why this is the case or if there is an alternative style to write this, too.

Any suggestions how to get rid of the powershell popup asking for the credential
and how to get rid of the powershell window not going away automatically?

Thanks,
Jeff

P.S.:
For your reference, the vba method to do the powershell call (End Function and #if stuff is broken in the code block, you'll figure it out though):

Public Function execPSCommand(ByVal psCmd As String, Optional ByVal label As String = "Debug") As String()

Dim oShell, oExec



Dim retval(2) As String

retval(0) = ""

retval(1) = ""



Set oShell = CreateObject("WScript.Shell")

Set oExec = oShell.Exec(psCmd)



oExec.stdin.Close ' close standard input before reading output



Const WshRunning = 0



Do While oExec.Status = WshRunning

    Sleep 100 ' Sleep a tenth of a second

Loop



Dim stdout As String

Dim stderr As String



stdout = oExec.stdout.ReadAll

stderr = oExec.stderr.ReadAll



retval(0) = stdout

retval(1) = stderr



execPSCommand = retval()

End Function

' Sleep (fractions of a second, milliseconds to be precise) for VBA

'#If VBA7 Then
' Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'#Else
' Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'#End If

I think you are not passing $cred argument properly to the scriptblock. The scriptblock should start with param($cred) if you want to use that local variable. Why not define $cred inside the scriptblock though? You can also use Using modifier to push local variable to the remote command (like $Using:cred, see more details https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_remote_variables?view=powershell-6 )

Regarding exiting powershell at the end, I guess you can just type "Exit" or "Stop-Process $pid" at the end of your command.

@Mike: Great stuff, thanks a lot for pointing me into the right direction.

The solution for me was to add "param([PSCredential]$Cred); " as suggested by you.
Of course I could have created $Cred inside the ScriptBlock as well ;)

Furthermore I remembered to have read somewhere that a PSSession should be
closed by a Remove-PSSession command afterwards in order to free up resources.

BUT: this approach clashed with the way I was busy waiting for the wsh shell command to finish (in vba) and then reading stdout/stderr from the process
expecting to close soon - so I removed the busy wait in vba for this particular
case (remote ps session).

It turned out that I did not need to call Remove-PSsession at all, cross-checked
that with a Start-Sleep 60 as the last command (instead of Remove-PSsession)
and executed "Get-PSsession -ComputerName exchange" once in a while on a
real powershell console while execution went on in vba; as soon as the 60 seconds
did pass the session was cleaned up automatically (no more sessions listed).

So the short story is: omit busy waiting when a remote PSSession is done in the ps script (something behind the scenes seems to already have removed that process or anything else not yet clear to me gets in the way in a blocking manner) - why oExec.Status is still left on WshRunning in the 'busy waiting
case' is beyond me, I was expecting it to be either WshFinished or WshFailed,
but that way it caused a blocking powershell window waiting forever.

Anyway, hardcoded vba password is gone as well, read in instead using an inputbox now,
happy powershelling may continue ;)