Optimising Excel automation

The key to optimising automation is to call the automation server (ie Word or Excel) as little as possible. In Word, this especially means not calling the server inside a loop. This is normally achieved by building a string by concatenation in a loop, and then passing this string to Word when it is finally built, eg

tmp:= '';
with qExample do
 begin
  close;
  params[0].asinteger:= n;
  open;
  while not eof do
   begin
    tmp:= tmp + fieldbyname ('name').asstring + #13;
    next
  end
end;

wrdSel.typetext (tmp);

Optimisation should not be limited solely to outputting text. Looking over one of my programs, I discovered that I had output text to a table, and then set the shading for each cell in a column separately. It is, of course, better to shade a table column all in one go, and then set any cells which need to have differring shading. Bearing in mind what I wrote previously about accessing Word collections in Delphi, the code becomes

wrdTable:= WrdDoc.Tables.Add (wrdapp.selection.Range, row + 4, 12, 1, 2);
wrdTable.columns.item(1).select;
wrdSel.range.shading.texture:= 100;
// 'unshade' certain cells
wrdTable.cell (1, 1).range.shading.texture:= 0;
wrdTable.cell (2, 1).range.shading.texture:= 0;

And now onto Excel.... The major activity when automating Excel is passing values to the various cells; other activities such as formatting the worksheet exist, but are much less important. Remembering the prime directive in automation, "call the server as infrequently as possible", it seems as if there is little to be done to automate Excel. Well, not quite.

The first, most simple and most effective optimisation is to hide Excel until all the data has been transferred. Whilst it is cool to watch the data being transferred, the screen updating is also slow. So the standard code to launch Excel should be

XLApp:= CreateOleObject('Excel.Application');

XLApp.Visible:= false;

XLApp.Workbooks.Add;

Sheet:= XLApp.Workbooks[1].WorkSheets[1];

and at the end of the code should be the line

XLApp.Visible:= true;

How can the Word optimisation of concatenating the data before exporting it be translated to Excel? The answer is to declare an array in Delphi, populate this array in Delphi, and then transfer it in one go to Excel. I don't remember where I picked up this technique so unfortunately I can't credit anyone. [Edit: here is the link]

Here is the complete code from a demonstration program which I wrote for myself; the only component on the form is a 5X5 stringgrid renamed sg. The program populates this stringgrid and then performs the magic. The important stage is to chose a range in the worksheet which is the same size and dimensions as the array to be passed; this is the 'range:= ' line.

procedure TForm1.Button1Click(Sender: TObject);
var
 xls, sheet, Range, arrData: Variant;
 i, j: integer;

begin
 {create variant array where we'll copy our data}
 arrData:= VarArrayCreate([1, sg.RowCount, 1, sg.ColCount], varVariant);

 {fill array}
 for i:= 1 to sg.RowCount do
  for j:= 1 to sg.ColCount do
   arrData[i, j] := sg.Cells[j-1, i-1];

 {initialize an instance of Excel}
 xls:= CreateOLEObject('Excel.Application');
 xls.visible:= false;
 Sheet:= XLApp.Workbooks[1].WorkSheets[1];

 Range:= sheet.Range[sheet.Cells[1, 1], sheet.Cells[sg.rowcount, sg.colcount]];
 Range.Value:= arrData; {copy data from allocated variant array}
 xls.Visible:= True;
 range:= unassigned;
 sheet:= unassigned;
 xls:= unassigned;
 close
end;

Happy optimising!

Yet more Word automation

Today I was checking out possibilities of optimising my Word automation code. I know that theoretically it's possible to send to Word a list of strings and then get Word to turn those strings into a table, but I tried this once and didn't succeed. The first stage is to build a long string, whilst separating the various values with a separator character, which is obviously one which won't appear in the text itself. I used the caret (^) as a separator, and one tells Word to use this character with the DefaultTableSeparator method. The code for the first step is thus:

wrdApp.defaulttableseparator:= '^';

tmp:= '';

with qJobs do // get text from this database table

 begin

  close;

  parambyname ('a').AsInteger:= a;

  parambyname ('b').AsInteger:= b;

  open;

  while not eof do

   begin

    tmp:= tmp + fieldbyname ('name').asstring + '^';

    next

  end

end;

tmp[length(tmp)]:= ' '; // remove the final caret

wrdSel.typetext (tmp);

The exported text will be in the form "a^b^c^d^e^f^g".

The key to turning this text into a table is to selected the exported text first and then turn it into a table. My normal method of exporting text into Word doesn't leave the text selected at the end, so I had to figure out how to select it. The exported text is always going to be a paragraph by itself, and it's always going to be the current paragraph (but not necessarily the final paragraph at the time of export). To quote an article which I found today, "if you want to know [the current paragraph number] in order to operate on the currently selected paragraph, table or other object, you can simply use: selection.collectionname (1)."

which in Delphi translates to "wrdSel.paragraphs.item(1).range.select". The Delphi "gotcha" is that Word collections have to be referred to as "collections.item(x)", not "collections(x)".

The next stage is turning the selected text into a table; this is done via the 'ConvertToTable' method, which takes a large number of parameters. Fortunately, we only need the first three; the first parameter says how to separate the table items, which is via the default separator character. The Word constant for this is "wdSeparateByDefaultListSeparator", whose value is 3. The second parameter is the number of rows in the table; although this parameter might seem to be important, it is irrelevant as the table adds rows according to the need, and so I pass the number 1. The third parameter, which is much more important, is the number of columns. Here is the code for this part:

wrdSel.paragraphs.item(1).range.select;

wrdSel.converttotable (3, 1, 2);

Normally I add a table to a Word document using this code:

wrdTable:= WrdDoc.Tables.Add (wrdSel.Range, 1, 12, 1, 2);

In the case of ConvertToTable, however, the table has already been created, and so the above line is useless. The solution is to access the current table which has been added to the document, not assuming that this is the first or last table. The way to do this is the same way in which I accessed the current paragraph, viz

wrdTab:= wrdSel.tables.item(1); // the new table

Once I have a pointer to the current table, I can do anything I want with it, such as removing the borders, shading one column, setting text alignment, etc.

Here is the final and complete code:

wrdApp.defaulttableseparator:= '^';

wrdSel:= wrdApp.selection;

tmp:= '';

with qJobs do

 begin

  close;

  parambyname ('a').AsInteger:= a;

  parambyname ('b').AsInteger:= b;

  open;

  while not eof do

   begin

    tmp:= tmp + fieldbyname ('name').asstring + '^';

    next

  end

end;

tmp[length(tmp)]:= ' ';

wrdSel.ParagraphFormat.Alignment:= 2;

wrdSel.typetext (tmp);

// the below line always selects the current paragraph

wrdSel.paragraphs.item(1).range.select;

wrdSel.converttotable (3, 1, 2);

wrdTab:= wrdSel.tables.item(1); // the new table

wrdTab.borders.insidelinestyle:= false; // no borders

wrdTab.borders.outsidelinestyle:= false;

wrdSel.homekey (wdStory); // jump to beginning of document

I used this code in a different place in the program to create a table with 75 rows of 5 columns each. The code executed almost instantaneously, as opposed to the length of time it took before. This technique cannot be slower than building the table manually, but the advantage becomes clearer the more rows and columns that have to be added.

Incidentally, I have one table with twelve columns and an changeable number of rows, but there is text only in the first and last columns. Of the other ten columns, only one will contain an 'X', which represents the numerical value for the first column in a range between one and ten. I did not use the 'ConvertToTable' technique to build this table as it is sparse, and the CTT technique should only be used for dense tables.

Next up is a similar conceptual technique for exporting data to Excel.

Counting beats with Van der Graaf

I had a long drive yesterday to the north of Israel, and took with me a few disks to listen to on the way. Coming back, I played Van der Graaf Generator's "Godbluff" and "Still life" and for want of something better to do, I started counting beats.

People have always ragged VdGG with their weird time signatures, but I've never really noticed and always assumed that those people were just tarring VdGG with the time signature brush, whether it's true or not. If you want real funny time signatures, listen to National Health....

So: "The undercover man" starts in 3/4 for two verses and then moves to 4/4 for the rest of the song. "Arrow" is in 4/4 throughout. "Scorched earth" is a different kettle of fish, though. The opening statement is three bars of 4/4 followed by one of 3/4. The verse has lines of 4/4 and lines of 5/4. The closing riff sequence is mainly in 5/4. There's a restatement of the riff at one point in 6/4. There's one section which seems to have 5/4, 3/4, 4/4 in successive bars. I also counted some bars as 7/4. That's fun for all the family.

Over the years, I haven't listened much to "The sleepwalkers", so this was quite a metric shock. I haven't figured out yet in which time signature the opening verse is - this will have to wait for another time. After the middle "if I only had time", there's a long riff section of 18 beats per repetition, which is probably three bars of 4/4 and one of 6/4. When the final verse comes in, the main structure of the vocal lines is 4/4, 2/4, 4/4, 4/4, with the instrumental bits in 3/4. I wonder whether the song was originally written like the final verse but became "tarted up" during the arranging process, during which beats were lost forever.

I listen to most of "Still life" quite frequently, but there are still some interesting things to be heard. The opening two tracks, "Pilgrims" and "Still life" seem to be totally in 4/4. Yesterday, I heard "La Rossa" as a slow 12/8, and on that basis, the middle section became very interesting ("if we made love now..."): a bar of 3/4 followed by two of 4/4, repeated several times (that could be written as 9/8 followed by two bars of 12/8, to make the triplet beat clear).

I noticed a few weeks ago that "My room" has a similar metric structure to the final verse of "Sleepwalkers" - 4/4, 2/4, 4/4, 4/4. If we take the first line, "Searching for" is a bar of 4/4, "diamonds" is a half bar of 2/4, "in a sulphur mine" is two bars of 4/4.

"Childlike faith in childhood's end" has most of its verses in 13/4 (or 4/4, 4/4, 5/4)!

Buy me a metronome for Christmas.

Better late than never

Several years ago I noticed that the more that I program, the less music I make. Unfortunately at the moment it's much easier to write computer programs than it is to write songs; for one, I am never short of ideas whereas I never have any ideas for the other.

But while I am writing programs, I am also listening to music, and in the 'better late than never' department, I must note my increasing interest for Joni Mitchell's classic "Blue" album from 1971. I've been listening to this album for the past few years but have never payed too much attention to it. At first, the faster songs ("All I want", "Carey", "California") served as my introduction, but lately their appeal has begun to fade, and to be honest, I almost wish that they weren't on the album, for I have fallen in love with the slower songs.

"Little green", "Blue" and "River" have become mainstays of my listening in the past few days. I admit that mainly I listen to the music and not to the words, not having enough spare processing power for them. The title song alone is worth the price of admission, especially in the 'middle bit', where a line sung in 4/4 suddenly compresses to be sung in 3/4 ("acid, booze and ass").

Inter-program communication

I wrote the other day about inter-program communication using a user-defined message which is broadcast to all windows. This technique is sound because only the programs involved recognise the user-defined message. Today I was reading about the wm_copydata message, which allows one program to pass data to another program. Here, broadcasting the message to all windows is strictly forbidden, as any program which has a wm_copydata handler will try to handle the message, sometimes with disastrous results. Here, the technique has to be to identify in advance (via the findwindow function) the receiving program. My use was limited only to passing a trigger: in plain English, 'when you receive this message, you'll know that I've finished'. If I ever use this technique in the future, I could pass a parameter in the 'wparam' field which has an agreed meaning. Here's a link to a program written in Delphi which uses wm_copydata. This isn't quite as robust as the one shown in the first link, although the article uses 'findwindow' and thus glosses over certain problems.

More Word Automation

After figuring out how to add bold text to a Word document from a Delphi program, it was only a short step to figure out how to add underlined text. But the real cherry was finally figuring out how to add bulleted text, ie

• line one
• line two

This may appear to be trivial, but the code which Word creates when doing the above as a macro is fairly complicated. I found some code in a Japanese Delphi blog which basically copies the Word macro code and turns it in Delphi, but it didn't work for me. It turns out in the end that most of that code is actually unnecessary, and I managed to write a 'bullet' procedure with only a few lines of code. I am posting the code here in the hope that Google will bookmark it, so when some poor programmer tries to create bulleted text in Delphi, he will have an example which works. Here it is:

procedure wrdBullets (app: variant; const s: string);

{ This procedure MUST be passed the wrdApp object, as ListGalleries is a

property of this object, not of the selection object}

const

 wdBulletGallery = 1;

 wdListApplyToWholeList = 0;

 wdWord10ListBehavior = 2;

var

 template, sel: variant;

begin

 Template:= App.ListGalleries.Item(wdBulletGallery).ListTemplates.Item(1);

 Sel:= App.selection;

 Sel.Range.ListFormat.ApplyListTemplate

(Template, false, wdListApplyToWholeList, wdWord10ListBehavior);

 Sel.typetext (s);

 sel.Range.ListFormat.RemoveNumbers;

end;

As always, the problem with creating Word automation code (in any language, but especially Delphi, which is a non-Microsoft language with a different syntax) is knowing which methods belong to which objects.

I have created a Delphi unit which exports all the more complicated functionality, such as bold, underline, bullet and gotobookmark. This way, I only have to write the complicated code once, and then every program which I write can use this code simply by including a call to a procedure.

Encouraged by these achievements, I also included the unit a procedure which receives a range of Excel cells and puts around and in them a double border.

Exam program launcher

I work with my occupational psychologist friend every Friday morning. Today, whilst we were talking about a program that we are developing, I noticed that her computer's desktop was filled with icons from my programs: normally three icons per program (one manager, one exam and one results program). I suggested writing a 'program launcher' program, which would present the user with a choice of programs to be run, and then run the chosen program. This would cut down the number of desktop icons drastically.

In the end, we decided against such a program (or rather, left it as an optional exercise for the future) as it doesn't improve the working environment. But out of this idea came a better idea: write a program launcher for the various exams that clients sit. What, you might ask? If a program launcher for multiple programs was discarded, why write one for only a subset of those programs?

The difference is this: a client will sit down at one of the computers in the computer room. The secretary will bring up one of the exams, fill in the client's details (surname, forename, id number, etc) then let the client do the exam. When the exam finishes, the secretary will bring up another exam, type in the client's details again and then let the client do this exam. All the exams were written purposely as standalone programs which need no external files and so can be distributed and run at other sites with a minimum of effort. This is why the user's personal details have to be entered into every exam. Use of a launcher program would mean that the personal details need only be entered once, and then the user can get on with all the exams.

The exam program launcher which I designed works on the following basis:

1. The user's personal data are entered into the launcher
2. The launcher saves these data in the computer's registry
3. A list of exams which the user will sit are chosen from the list of all exams (this list comes from a database which holds the exam's name, its location and any command line parameters)
4. The first exam in the chosen list is removed from the list and run
5. When the exam finishes, it notifies the program launcher than it has finished
6. If there are more exams in the chosen list then the launcher loops to step 5
7. The launcher removes the data saved from the registry and exits

Whilst the above isn't exactly rocket science, one will not find many examples of how to do inter-program communication in Windows. In DOS, this probably would have been done with an interrupt; in fact, during the dying days of DOS, I became quite adept at writing TSR programs, initially in assembly language and then in Turbo Pascal, which used the $2F multiplex interrupt for this purpose. But now we are in Windows, and so we must use a message. But which message?

It turns out that Windows has the 'RegisterWindowMessage' function which creates a unique message number for the parameter passed to it. Several programs can call the same function with the same string; the first program that calls the function will create (register) the message number, and every subsequent call will retrieve the same number.

I modified some of the exams to do the following:

• In the 'GetDetails' dialog form, the program checks the registry to see whether it has values. If the exam has been called from the program launcher, then there will be values, whereas if the exam is run directly then there won't be any values.
  
• When the exam finishes, it makes a call to the RegisterWindowMessage to get the special message number and then broadcasts a message to all the running programs with that message number.

If the exam is run directly, nothing untoward happens, but if the exam is run from the launcher, then it will indeed notify the launcher when it completes. The launcher handles this completion message by executing the next exam in the exam list.

It's a bit difficult to explain this asynchronous, inter-program communication in a synchronous manner (ie writing this explanation) and to someone who isn't used to event-based programming, but like every clever idea, it is in fact quite simple (especially in retrospect). One of the neat things about this program is that the exams can also be run directly, with no side effects.