In the previous post, I was wondering how to run .bat files from VBA. This is in order to give me a bit of a front-end, with a list of the available commands, with comments, and a Run button:
The key operation is the procedure VBA.Interaction.Shell. This takes a path to the executable file, plus an optional window mode (default is minimized with focus). The file name is picked up from the current selection (in column A), and the location is assumed to be that of the workbook. Obviously, you could get fancier than this, maybe with a form.
batdir = ActiveWorkbook.Path
ChDir batdir
batfile = "dir.bat"
batpath = batdir + "\" + batfile
taskid = Shell(batpath)
The Shell procedure returns a taskid, if successful.
The batch commands produce lots of verbose output, which is written to a text file “out.txt” in the working directory. It might or might not contain ERROR lines, so it would be useful to have these detected. The call to Shell is therefore followed by a call to a procedure that checks the output file:
Sub ErrorCheck()
Dim fso As New Scripting.FileSystemObject
Dim f As Scripting.File
Dim ts As Scripting.TextStream
Dim line As String
Set f = fso.GetFile(outfile)
Set ts = f.OpenAsTextStream
Dim founderror As Boolean
line = ts.ReadLine
founderror = CheckLine(line)
Do Until ts.AtEndOfStream Or founderror
line = ts.ReadLine
founderror = CheckLine(line)
Loop
If founderror Then
MsgBox "Error: " + vbCrLf + line
Else
MsgBox "Command " & batfile & " successful"
End If
End Sub
This uses the FileSystemObject, File and TextStream classes from the MS Scripting Runtime Library (find this in the >Tools >References dialog).
However, there’s a problem: a batch command can take several seconds to run, so I want to pause the VBA execution until it finishes, otherwise ErrorCheck won’t get access to the output file.
The trick is to use the task id returned by Shell, to get a process handle for the running command, and then check the process’s status repeatedly until it exits. The functions required are Windows functions, not VBA: OpenProcess and GetExitCodeProcess. The standard code to use these is wrapped up in a procedure called RunAppWait. It is this that we call from our code, instead of calling Shell directly.
You can find more information on this on MSDN: Running Other Applications from Access 2007. (There’s nothing Access-specific in this stuff). The modules can be downloaded via the first link at the end of the article.





