You've successfully subscribed to Nicholas Workshop
Great! Next, complete checkout for full access to Nicholas Workshop
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Run Shell Commands In VBA

Nicholas Wong
Nicholas Wong

We can use shell to execute any programs.

shell("iexplorer")  
shell("cmd")  

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  
Unsorted

Nicholas Wong

Fullstack software engineer with strong background in computer science and extensive experience in software engineering and architecture. Studied in NYU, worked in Yahoo, Rakuten and Manulife.