Run Shell Commands In VBA

Run Shell Commands In VBA

We can use shell to execute any programs.


Please be reminded that we may need to execute in an indirect way.

'cmd: execute CMD.exe (Windows Command Processor)  
'/c: with commands  
'dir: show all things in the default directory  
shell ("cmd /c dir")  

To make VBA wait until the execution finish and retrieve the result back to the program, we can use this example.

'Global declaration  
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long  
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long  
Private Declare Function CloseHandle Lib "kernel32 " (ByVal hHandle As Long) As Long  
'Inside program  
Dim PathStr  
PathStr = "C:Program Filesgzip"  
Dim CmdStr  
CmdStr = "cmd /c gzip.exe -dkv " & strFileName  
ChDir PathStr ‘Change the default directory  
ProcessId = Shell(CmdStr, vbNormalFocus) ‘Run shell to execute program and record the process ID  
ProcessHandle = OpenProcess(SYNCHRONIZE, 0, ProcessId) ‘Track the process  
If ProcessHandle <> 0 Then  
    WaitForSingleObject ProcessHandle, INFINITE ‘ Wait process ends  
    CloseHandle ProcessHandle  
End If