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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: