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 = 'MYDOMAIN\Administrator'; $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 = 'MYDOMAIN\Administrator'; $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://learn.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.