Some macros need to run an external program (or script) as part of its execution. VBA provides the Shell function for this task. While this may be a simple to use function its main limitation is that it does not wait for the external program to complete. The Windows Script Host Object Model provides a Shell object with a Run method that will wait for the external program to complete before continuing. The two methods are shown below.
'
'MacroRun – launches Notepad and immediately moves on to completion
'
Sub MacroRun()
Shell "Notepad"
MsgBox "Complete"
End Sub
'
'MacroRunAndWait – launches Notepad and waits for user to exit
'the Notepad application before continuing.
‘
'This macro requires a reference to:
'Windows Script Host Object Model (C:\Windows\System32\wshom.ocx)"
'
Sub MacroRunAndWait()
Dim myShell As WshShell
Set myShell = New WshShell
myShell.Run "Notepad", , True
Set myShell = Nothing
MsgBox "Complete"
End Sub
Note: to call a program or script not in the system path provide the full path, and any arguments to the file inside the double quotes.
Example: Shell "C:\Company\Scripts\Process.bat 1 2 3"