VBA - Shell a process and acquire its StdIn, StdOut, StdErr pipes

Juts a quickie. On Stackoverflow some code posted which shells a process and reads the piped output purely using Window API calls, quite impressive but seems to be tripping up on some 64-bit issue. Actually VBA developers need not wrestle Windows API on this one and can in fact use the Windows Script Host Object Model library instead.

Option Explicit

Function ShellAndGetText() As String

    '* Tools -> References
    '* Windows Script Host Object Model

    Dim oWshShell As IWshRuntimeLibrary.WshShell
    Set oWshShell = New IWshRuntimeLibrary.WshShell


    Dim oWshExec As IWshRuntimeLibrary.WshExec
    
    Dim sComSpec As String
    sComSpec = Environ$("COMSPEC")
    
    Dim sReturnText As String
    Set oWshExec = oWshShell.Exec(sComSpec & " foo.exe")
    
    While oWshExec.Status = WshRunning
        DoEvents
    Wend
    If oWshExec.Status = WshFinished Then
        '* success
        sReturnText = oWshExec.StdOut
    Else
        '* failure
        sReturnText = oWshExec.StdErr
    End If

    ShellAndGetText = sReturnText

End Function

VBA - Shelling Microsoft Edge and other Protocol Activation

Just a quickie, Microsoft Edge is a new generation windows app and is activated using a protocol. So from the command line we go

start microsoft-edge:http://www.cnn.com

And this launches Microsoft Edge with a new tab with CNN web page inside. But tring to shell to this using VBA gives the error file not found. What is required is to add the command specifier C:\WINDOWS\system32\cmd.exe which is also set as the environment variable comspec. So now we can shell from VBA if we precede with the comspec thus ...

Sub ShellMicrosoftEdge()
    VBA.Shell Environ$("comspec") & " /c start microsoft-edge:http://www.cnn.com"
End Sub

Python code

For a bonus we can read this trick across to Python code. Again add the comspec ...

import os 
from subprocess import Popen

browserJob=Popen([os.environ['comspec'],'/c','start' , 'microsoft-edge:http://www.cnn.com' ])

Thoughts

As these new activation protocols proliferate with Window 8 onwards apps, don't worry it is still possible to launch/shell in a traditional manner. The links below give some further reading.

Links

• Jerry Nixon on Windows_ Walkthrough_ using Windows 8 Custom Protocol Activation
• How to script Microsoft's Edge browser _ ITworld
• windows 10 - How to open URL in Microsoft Edge from the command line_ - Stack Overflow

Use Shell to give you multitasking

Often on StackOverflow I see questions regarding long-running file handling using Scripting Runtime.  They really should learn to shell out to the command line, this way their VBA code does block.

So the simplest case is Shell and Forget here is some code.  You need to read the ComSpec environment variable and you need to pass the command switches /S /C.

Here we are opening the workbook's home folder in Windows Explorer.

Sub ExploreMyHomeDir()
    Shell (Environ("comspec") & " /s /c explorer.exe " & ThisWorkbook.Path)
End Sub