Batch File Controller – part 2

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)

    If founderror Then
        MsgBox "Error: " + vbCrLf + line
        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.


0 Responses to “Batch File Controller – part 2”

  1. Leave a Comment

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

November 2009
« Oct   Dec »

%d bloggers like this: