Run an External Program
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"
