powershell invoke-command exchange mgmt shell from vba with stdout and stderr retrieval and no credential...











up vote
1
down vote

favorite












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










share|improve this question




























    up vote
    1
    down vote

    favorite












    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










    share|improve this question


























      up vote
      1
      down vote

      favorite









      up vote
      1
      down vote

      favorite











      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










      share|improve this question















      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







      vba powershell exchange-server wsh






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited Nov 22 at 17:39

























      asked Nov 22 at 16:13









      Jeff T. Raccoon

      84




      84
























          2 Answers
          2






          active

          oldest

          votes

















          up vote
          0
          down vote



          accepted










          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.






          share|improve this answer























          • Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
            – Jeff T. Raccoon
            Nov 26 at 14:06


















          up vote
          0
          down vote













          @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 ;)






          share|improve this answer























            Your Answer






            StackExchange.ifUsing("editor", function () {
            StackExchange.using("externalEditor", function () {
            StackExchange.using("snippets", function () {
            StackExchange.snippets.init();
            });
            });
            }, "code-snippets");

            StackExchange.ready(function() {
            var channelOptions = {
            tags: "".split(" "),
            id: "1"
            };
            initTagRenderer("".split(" "), "".split(" "), channelOptions);

            StackExchange.using("externalEditor", function() {
            // Have to fire editor after snippets, if snippets enabled
            if (StackExchange.settings.snippets.snippetsEnabled) {
            StackExchange.using("snippets", function() {
            createEditor();
            });
            }
            else {
            createEditor();
            }
            });

            function createEditor() {
            StackExchange.prepareEditor({
            heartbeatType: 'answer',
            convertImagesToLinks: true,
            noModals: true,
            showLowRepImageUploadWarning: true,
            reputationToPostImages: 10,
            bindNavPrevention: true,
            postfix: "",
            imageUploader: {
            brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
            contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
            allowUrls: true
            },
            onDemand: true,
            discardSelector: ".discard-answer"
            ,immediatelyShowMarkdownHelp:true
            });


            }
            });














            draft saved

            draft discarded


















            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434803%2fpowershell-invoke-command-exchange-mgmt-shell-from-vba-with-stdout-and-stderr-re%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown

























            2 Answers
            2






            active

            oldest

            votes








            2 Answers
            2






            active

            oldest

            votes









            active

            oldest

            votes






            active

            oldest

            votes








            up vote
            0
            down vote



            accepted










            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.






            share|improve this answer























            • Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
              – Jeff T. Raccoon
              Nov 26 at 14:06















            up vote
            0
            down vote



            accepted










            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.






            share|improve this answer























            • Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
              – Jeff T. Raccoon
              Nov 26 at 14:06













            up vote
            0
            down vote



            accepted







            up vote
            0
            down vote



            accepted






            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.






            share|improve this answer














            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.







            share|improve this answer














            share|improve this answer



            share|improve this answer








            edited Nov 23 at 3:44

























            answered Nov 23 at 3:38









            Mike Twc

            736211




            736211












            • Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
              – Jeff T. Raccoon
              Nov 26 at 14:06


















            • Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
              – Jeff T. Raccoon
              Nov 26 at 14:06
















            Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
            – Jeff T. Raccoon
            Nov 26 at 14:06




            Great stuff, thanks a lot for pointing me into the right direction. Since comments are limited to a really short number of characters, please see my answer below as well ;)
            – Jeff T. Raccoon
            Nov 26 at 14:06












            up vote
            0
            down vote













            @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 ;)






            share|improve this answer



























              up vote
              0
              down vote













              @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 ;)






              share|improve this answer

























                up vote
                0
                down vote










                up vote
                0
                down vote









                @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 ;)






                share|improve this answer














                @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 ;)







                share|improve this answer














                share|improve this answer



                share|improve this answer








                edited Nov 27 at 7:10

























                answered Nov 26 at 14:08









                Jeff T. Raccoon

                84




                84






























                    draft saved

                    draft discarded




















































                    Thanks for contributing an answer to Stack Overflow!


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.





                    Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


                    Please pay close attention to the following guidance:


                    • Please be sure to answer the question. Provide details and share your research!

                    But avoid



                    • Asking for help, clarification, or responding to other answers.

                    • Making statements based on opinion; back them up with references or personal experience.


                    To learn more, see our tips on writing great answers.




                    draft saved


                    draft discarded














                    StackExchange.ready(
                    function () {
                    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53434803%2fpowershell-invoke-command-exchange-mgmt-shell-from-vba-with-stdout-and-stderr-re%23new-answer', 'question_page');
                    }
                    );

                    Post as a guest















                    Required, but never shown





















































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown

































                    Required, but never shown














                    Required, but never shown












                    Required, but never shown







                    Required, but never shown







                    Popular posts from this blog

                    What visual should I use to simply compare current year value vs last year in Power BI desktop

                    How to ignore python UserWarning in pytest?

                    Alexandru Averescu