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.

Adding HTML Help – 2

I was previously wondering how to link a VBA project to HTML Help (that is, linking to a page in a .chm file). I’m thinking of the Help as pages in a User Manual, not control-level Help (“The Delete button deletes the current item”, etc).

Having got some compiled Help (a CHM file), we need to link into it. The simplest way seems to be to use the Application.Help method. This takes a path to a CHM file, and a numerical key – for example:

Application.Help “C:\…….\myhelp.chm”, 1002

There are a couple of issues with this. The first is that each call to this method (on the same file) opens a new Help Viewer window. I haven’t found a way round this. The second issue is that the numerical keys have to be defined in a header file prior to compilation – something like this:

#define VBA_Creating_a_Widget     1001
#define VBA_Filtering_Widgets        1002
…

where the names are those of the topic files in the HTML Help project (minus the .html suffix). This mapping is compiled into the help project to produce the CHM file. However, generation from DITA (XML) source goes straight to the compiled CHM, so there’s no chance of adding in a map. (You could decompile the CHM and then add the map before recompiling).

Fortunately, it doesn’t matter, because there’s another way of linking to the Help from VBA. This is to use a more general function, HtmlHelp, available from the Help OCX (HHCtrl.ocx).

You need to declare this function in a public module:

Declare Function HtmlHelp Lib "HHCtrl.ocx" _
   Alias "HtmlHelpA" _
   (ByVal hwndCaller As Long, _
   ByVal pszFile As String, _
   ByVal uCommand As Long, _
   dwData As Any) As Long

You also need a couple of constants (just to give us meaningful names):

Const HH_DISPLAY_TOPIC = &H0
Const HH_HELP_CONTEXT = &HF

The constant declarations  need to be in each module that calls the function.

We can now call the function from a Command button handler, or whatever:

Private Sub HelpCommand_Click()
  Call HtmlHelp (0, Dotslash("myhelp.chm"), _
      HH_DISPLAY_TOPIC, ByVal "VBA_Filtering_Widgets.html")
End Sub

The zero argument is to do with the windowing – the Help opens in a sibling window. Note the ByVal passing of the string (because it’s to an external library). Dotslash is just my shorthand for an absolute path (which is recommended):

Public Function Dotslash(filename As String) As String
  Dotslash = ActiveWorkbook.Path + "\" + filename
End Function

The other mode argument, HH_HELP_CONTEXT, is for the mapped-id lookup, which is what Application.Help does.

This is just scratching the surface. There’s more information on MSDN.

Adding HTML Help

We’re all familiar with HTML Help for applications – usually in the form of .chm files (with the yellow question-mark icon). Typically, constructing these this involves getting to grips with the Microsoft HTML Help Workshop. This includes a tool for building Help projects, and compiling them into .chm files.

This is okay for fine-grained context-sensitive Help, at the level of individual operations and controls. However, if the material is more in the nature of a User Manual, this is probably not the form in which we want to author it. And we will probably want to generate different forms of output, such as general HTML pages and PDF.

A few posts ago, I mentioned producing documentation using the XML-based DITA framework. One of the benefits of this is that content is held in a generic XML-based format, from which outputs of different types can be generated: XHTML, PDF and CHM (and also Java Help). DITA allows us to select and combine various topics (chunks of content) from our User Manual – perhaps the lower-level task or reference topics – and compile these into a .chm file. Apart from the compiler, which is invoked automatically, this does not involve the HTML Help Workshop.

Note on tools: There is a DITA Open Toolkit, but I have been using tools from XMLMind – the free XML Editor (Personal Edition) and the open-source DITA Converter. And very good they are too.

The selection, structuring and ordering of topics is done using a DITA Map file. HTML Help is specified as the output format via options to the DITA Converter.

Having got our .chm file, the next step is to access it from a VBA project. I’d been vaguely aware that you could do this, but had never investigated further. More tomorrow…

Managing Email Attachments – Design

In the previous posting, I was thinking about how to go through email messages, saving attchments and then removing them, and possibly doing other things to the messages, like moving them. While you can do a certain amount using Outlook rules, and yes there are third-party utilities out there, it’s interesting to think about how to do this in VBA.

As is often the way, you start with some specific requirements, think about it a bit, and come up with a rather more generic mechanism – something with a bit of Design Pattern about it. It seemed to me that I might want to apply multiple rules to each message, depending on properties of the message or attachment.

So I would give a message to a chain of rules, each of which would be applied to the message in turn. When a rule is applied, it evaluates some test condition, and if true performs some actions on the message (like saving and removing its attachment). So here’s the outline design:

The Dispatcher object (these are Class modules) creates and holds on to a RuleChain. When Run is called on the Dispatcher, it goes through the current folder (and potentially its subfolders), getting MailItems, and giving them to the RuleChain:

Sub Run()
    Dim folder As MAPIFolder
    Set folder = Application.ActiveExplorer.CurrentFolder

    Dim item As Object
    For Each item In folder.Items
        Select Case item.Class
            Case olMail
                di_rulechain.Apply item
            Case olFolder
                'recurse ...
        End Select
    Next
End Sub

The RuleChain just iterates over its individual Rules:

Sub Apply(mi As MailItem)
    Dim i As Integer
    For i = 1 To UBound(rc_rules)
        rc_rules(i).Apply mi
    Next
End Sub

A Rule applies itself to a MailItem:

Sub Apply(mi As MailItem)
    If RuleTest(mi) Then RuleAction mi
End Sub

Simples! Now, we want different rules, but they have to be objects of the same class. So the class has to cover all the tests and actions that we might want, with switches/settings to make them specific. For example:

Dim ru_saveattachment As Boolean       'save attachment?
Dim ru_saveattachmentfolder As String  'where?
Dim ru_removeattachment As Boolean     'remove attachment?

So where do the settings for a particular Rule come from? It seems like a good idea if a Rule is represented by something that is (a) persistent between sessions, and (b) editable by a user, without going into the VBA code. One candidate is to use an Outlook Note. We can have a very simple syntax, where a test starts with ‘?’, and an action starts with ‘!’. For example:

This says that if a message is in (has Parent) ‘Inbox’, then save the attachment to /Temp and remove it. Multiple tests are ANDed (multiple Rules provide disjunction). Since we might have ‘ordinary’ Notes, and also so we can disable individual Rules, only pink notes are treated as Rules.

The RuleChain is responsible for going through the Notes folder, creating the Rules:

Private Sub Class_Initialize()    'for RuleChain
    Dim notesfolder As MAPIFolder
    Set notesfolder = ...
    ReDim rc_rules(1 To notesfolder.Items.Count)

    Dim note As NoteItem
    Dim ru As Rule
    Dim i As Integer
    i = 1
    For Each note In notesfolder.Items
        If note.Color = olPink Then  'it is a rule
            Set ru = New Rule
            ru.Load note
            Set rc_rules(i) = ru
            i = i + 1
        End If
    Next

    ReDim Preserve rc_rules(1 To (i - 1))
End Sub

The Rule.Load method is responsible for parsing NoteItem.Body, and setting up the Rule accordingly. Clearly you could get quite sophisticated here.

At present the only actions are saving and removing attachments, and I haven’t thought about adding links/ids, to allow traceability. More (maybe) anon.

Managing Email Attachments

Following on from the previous posting, I’ve been wondering what else to do with Outlook.

One thing that’s I’ve meant to sort out for a long time is managing email attachments (Word documents, PDFs, etc). My outlook.pst file is not far short of half a gigabyte, and presumably this is largely due to lots and lots of  message attachments. Nearly all of these will have been Saved As, but almost never Removed. It’s annoying that there isn’t a Detach operation that does both.

So what I’d like is an Attachments Manager. Yes, there are some out there, but it’s more fun to roll your own, and learn something in the process.

One issue to decide is whether we want the saving/removal to be done automatically, on receipt of the message, or manually initiated as a housekeeping task (or both). I think I’ll start with the latter, as it gives an opportunity to check an incoming message.

We’ll want to scope the messages being processed, for example by:

• Folder (e.g. Inbox, Saved, all folders)
• Date (e.g. older than)
• File types for the attachments (e.g. only MS Office, PDFs)
• Message title (or content)
• Message sender.

We’ll also need to specify target folders for the saved files.

It would be good to maintain a link from the message to the saved file. I’m thinking this can just be a file:/// URL. We get a sort of back-link just by searching for the filename (I use the Lookout search facility).

Given that we want a degree of flexibility in setting up the processing parameters, a front-end user-form would be good. I can see this turning into a bit of a mini-project…

Email Auto-Reply – Solution

In the previous posting, I was wondering how to generate an email auto-reply that contains your next free day, based on Calendar appointments. I had been thinking of using Rules, but a comment by Jon Peltier pointed me in the right direction.

The trigger is to handle the event Application_NewMail (the handler goes in the ThisOutlookSession module):

Private Sub Application_NewMail()
    Dim ib As MAPIFolder
    Dim mi As MailItem

    Set ib = _
       Application.Session.GetDefaultFolder(olFolderInbox)
    Set mi = ib.Items.GetLast

    RespondTo mi
End Sub

The incoming message needs to be inspected for information about the sender, and responded to, if appropriate:

Sub RespondTo(mi_in As MailItem)

    If IsAutoRespond(mi_in) Then
        MakeResponse mi_in
    End If

End Sub

An incoming message has a SenderName property. Oddly, it has a To property, but not a From property (why? am I missing something?). The SenderName is the ‘display’ name, that you typically see in the ‘From’ column of your Inbox, in preference to the actual email address. As far as I can see, this comes from the alias set up for the email account of the sender; it doesn’t correspond to any name field in the corresponding Contact (Full Name, E-mail, etc).

So we need to record the SenderName, as it appears in your Inbox, in a field of the corresponding Contact. We could add a user-defined field, but on the Details tab there’s a Nickname field, which I doubt is ever used, and so can be hi-jacked. Use of this field could be taken as enabling our auto-reply, but to be on the safe side, let’s add a user-defined field AutoRespond, set to True.

The IsAutoRespond function uses the Find function on the Contacts:

Set contact = contactsfolder.Items.Find(filter)

where

filter = _
     "[Nickname] = " + Chr(34) + mi.SenderName + Chr(34) + _
     " And [AutoRespond] = " + Chr(34) + "True" + Chr(34)

When a filter string is given as a literal, the double-quotes are doubled (e.g. “”True”"). I discovered that this isn’t necessary when using chr(34). If the Find fails, then the result variable is Nothing.

MakeResponse replies to the incoming message, using the next free date in the reply message’s body:

Sub MakeResponse(mi_in As MailItem)

    Dim mi_out As MailItem
    Dim nextfree As Date

    Set mi_out = mi_in.Reply

    With mi_out
        .Body = responsetext & NextFreeDay
    End With
    mi_out.Send
End Sub

The NextFreeDay function goes through the Calendar looking for All-Day-Event appointments (these could be fake ones, used just to block out days; I have mine as Free time, so that they don’t clash with ‘real’ appointments). The result starts as tomorrow’s date, and gets pushed into the future, as appointments are found. Weekends and other unavailable dates should be taken into account.

The initial version is pretty basic. It would seem possible to use Calendar information in more sophisticated ways, or indeed to analyse the incoming message content.

Email Auto-Reply with Availability

My wife’s a Business Intelligence (sic) consultant, writing reports on hideously complicated databases. As is the way of things, she does reports for different customers and projects. The difficulty lies in getting across the idea that when working for customer X, she’s not available to fire-fight problems for customer Y. So she responds to emails, saying “I’m sorry, my next availability to look at your problem is on <date>”, where <date> is found by looking in her diary for the next free day.

Assuming that you’re using Outlook as both email client and calendar, is it possible to generate such replies automatically? Presumably incoming emails (with certain properties, say Senders) can be detected using Rules, and a reply sent. But can we intercept the rule-processing, and insert information derived from the Calendar (or indeed elsewhere) into the reply message?

Having not done a lot with Outlook (compared with Excel and Access), I have no idea at this stage, but I’ll investigate, and report back. Obviously, if anyone out there has done this already, let me know!

Digression on Documentation: DITA

As is the way of things, I’ve been diverted from the delights of VBA, on to some other work. This is a migration of the system definition documentation for a major financial system, from traditional Word documents to an XML-based architecture called DITA. (Somewhat fancifully, the ‘D’ stands for Darwin, which is appropriate in this, his bicentenary year).

The production of technical documentation is undergoing something of a revolution at present. This is due to the maturing of a raft of technologies based on the XML markup language. Broadly speaking, these technologies provide a solution that sits between monolithic documents-as-files (such as Word documents), and relational databases, as complex aggregations of fine-grained information records.

With a Word document, the unit of content is the same as the unit of presentation: a file is edited, and the same file is printed. This applies equally to web pages (HTML), but with the page as the unit. In practice, it is difficult and time-consuming to identify, extract and recombine fragments of documents to produce new deliverables.

With a relational database, the information records can be conjoined, aggregated and filtered in very complex ways, using a query language (SQL). However, databases are not really suited to holding large free-text elements, like a section of a document. Also, there is no notion of hierarchical structuring in query output, in contrast to the hierarchy of chapters, sections and sub-sections that we are familiar with in documents.

The XML-based solutions aim to provide a middle course. Content is created and held in a form that is structured enough to identify, extract and recombine fragments of documents to produce new deliverables. At the same time, the content does not carry information about presentation (either the target format or the details of layout). This is provided by transformations of the content to produce deliverables in different formats, such as Word or PDF for printable documents, or hypertext (XHTML) for web presentation or online Help.

The challenge is to come up with an information model that defines and relates appropriate topics (i.e. basic chunks), in ways that allow querying, selection and combination in flexible ways. There’s a trade-off here between flexibility and chunk size. Too fine-grained and it’s impossible to manage; too coarse-grained and you’re back with monolithic documents. There’s a wider trend towards ‘medium-sized’ information chunks: think of blog posts, like this one, or Wiki pages.

More on this anon…

Domain Specific Languages – Part 3

In the previous posting, I was trying out a simple rule-based DSL: in effect, taking control of expression evaluation away from the formula mechanism. Perhaps more interesting is to have a proper object model behind the scenes, where the objects ‘collaborate’ to calculate properties of interest, in ways that would be difficult to do with formulas alone.

In this example, the object model is a network of activities, in the manner of PERT-style planning. An activity has a duration, and some number of predecessor activities, on which it depends (for inputs or  resources):

There is a single initial pseudo-activity, which is given an actual finish date, as a baseline for the analysis. A1 and A3 can then start on the following day.

An activity starts at start-of-day on its start date, and finishes at end-of-day on its finish date. So a 2-day activity might have start date 12/08/09 and finish date 13/08/09.

As time passes, activities get initiated and completed, and thus get actual start and finish dates.

The aim of the analysis is to work out the the earliest start and end dates for activities, back to the point where activities have actual dates. A subsequent version also calculates latest start/finish dates, and thus ’slack’, but that’s omitted here for simplicity.

All the work is done by a class (module) Activity. This has properties for duration and the various dates, and Collections for predecessor and successor Activities (only the predecessors are used in this version). If an Activity does not have a stored value for EarliestStart, it calculates it as the latest of the EarliestFinishes of its predecessors. The recursion is terminated by an ActualFinish. Here’s the procedure:

Private Function CalcEarliestStart() As Date
    If obj_actual_start > 0 Then
        CalcEarliestStart = obj_actual_start
    Else
        Dim pred As Activity
        For Each pred In obj_preds
            If pred.EarliestFinish > CalcEarliestStart Then
                CalcEarliestStart = pred.EarliestFinish
            End If
        Next
        CalcEarliestStart = CalcEarliestStart + 1
                             'start of next day
    End If
End Function

The activities (with durations and possibly actual dates) are set up on a worksheet. The outputs are generated to this worksheet, but could be elsewhere:

The Predecessor relationships are set up on a separate worksheet:

A general module Objects contains a Collection for the Activity objects, plus the procedures to run the analysis:

Public activities As Collection

Sub RunPert()
 Range("outputs").Clear
 LoadActivities
 ConfigureNetwork
 CalcPert
End Sub

LoadActivities creates the objects and puts them in the Collection; ConfigureNetwork sets the predecessor relationships; CalcPert queries the Earliest dates for each Activity.

Since each Activity caches its Earliest dates, once calculated, it does not matter in what order the activities appear on the worksheet. It’s easy to add or change activities, and enter Actual dates as they happen (the second version is a bit more sophisticated about this – e.g. updating the duration).

Obviously this isn’t an all-singing, all-dancing planning tool, but I think there’s enough here to suggest that the approach could be quite useful for this type of problem.