Learning VBA

Interesting post (and comments) over at Daily Dose of Excel (referencing another post). Basically saying that people should be learning and using (a bit of) VBA, as part of their basic efficiency/productivity – assuming they use Office apps, of course. For example, you wonder how many people copy-and-paste quantities of data manually (probably making mistakes) on a regular basis, when a few lines of code would do it for them.

Unfortunately, it is part of our culture that programming is regarded as something terribly technical and obscure, not for ordinary people. Microsoft themselves are at fault for putting ever more layers of user interface in front of the application functionality. Lots of PC users do not know what a Command Prompt is. I admit that VBA as a language is a bit quirky and inconsistent (the Set Keyword, for example), but it’s not unusable.

There’s a similar issue over in the domain of XML documentation. There are technical writers (‘technical’, note) who feel that everything should be entirely wrapped up in a GUI. This might be feasible if you are prepared to do what the tools let you do, and nothing else. But I think having some knowledge of the underlying languages is helpful.

It also allows you to use the many free tools, which tend to be light on GUIs. For example, a set of XML document files on disk can be queried (and even updated) using the XQuery language, effectively turning the files into a content database. XQuery is a very clean, functional language, with a SELECT-like construct to do the querying (much easier than VBA!). There are free (versions of) tools that index files and run queries.

So, yes, I think that there should be greater encouragement for ‘serious’ users of tools to roll their sleeves up and get into some (gentle or not so gentle) programming. At least they’d appreciate a bit more what developers do!

Multiple Selected Worksheets

Here’s a question that came up on a recent course. In Excel, suppose that you have shift-clicked or control-clicked to select multiple worksheets. Are they all Active? If so, what does ActiveSheet return? If not, where is the set of selected sheets?

The answer is that there is only one ActiveSheet: the one on the top of the stack, with the name in bold. The set of selected sheets is held by the SelectedSheets property (reasonably enough) of a Window object. This is a Sheets collection, which can contain both worksheets and (free-standing) Charts. The Workbook holds the full Sheets collection, of which a Window’s SelectedSheets is obviously a subset.

The Window objects are held both by a Workbook (usually just one) and Application (the full set for all open Workbooks). A Window holds appearance-related properties, such as scrolling and splitting. A Window does not raise any events, so there does not seem to be anything corresponding to the shift-click or control-click actions (not that I can think what you would want to do on handling them).

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)
    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.

Batch File Controller

I’ve recently been getting involved with the DITA documentation framework. There are various free (or free for personal use) tools to support it, but they are often command-line tools. Fancy user interfaces cost!

The tool that converts DITA XML to XHTML, PDF, etc, is such a command-line tool, with lots of options to control its behaviour. You have to specify an input map (a build manifest), and an output target. From my content base, I want to build different outputs, in different formats. So I am accumulating a folder full of .bat files, each containing a particular command, with options and file parameters. Running a bat file, in verbose mode, generates a few screen-fulls of output messages, which might or might not contain error messages.

So what I’d like is a simple front end that allows me to select a particular combination of content and format, run the command, and check the output for errors. Since I’ll want a list of the available bat files, and a bit of user interaction, I’ll do it in Excel (more as a vehicle than because I need many of Excel’s capabilities).

The key is the VBA.Interaction.Shell method. Details tomorrow.