Run shell commands in VBA

We can use shell to execute any programs.

[sourcecode language=”vb”]

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

[sourcecode language=”vb”]
‘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.

[sourcecode language=”vb”]
‘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
[sourcecode language=”vb”]
‘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

%d bloggers like this: