Facts your mother never told you about Word automation

As I have probably written ad nauseam, I have written many programs for the Occupational Psychologist (OP) whom I support, and many modules within these programs write their output via Word and Excel. Ever since the OP bought new computers running Windows 7 and Office 2010, my programs have caused problems when outputting data to Word ('invalid pointer operation' is the usual error message. These problems don't occur on my computer, since I am working with Windows XP and Office 2003, nor on the old computers that the OP has.

I finally got motivated enough to hunt for the cause, and then the solution, to this problem. As usual in computer programming, finding the cause (aka debugging) took several hours whereas installing the fix took five minutes. The first step on the long road was changing the way that my programs would execute Word. I normally write this:

wrdApp:= CreateOleObject ('Word.Application');
wrdDoc:= wrdApp.Documents.Add ('q400.dot');

The first statement creates an instance of Word in the background, whereas the second statement creates a new document within Word, using the template called 'q400'. For the unsophisticated user, the first statement is equivalent to starting the Word program, and the second is equivalent to pressing 'New' and then choosing to create the new document based on the q400 template. The template is stored in Word's default template directory (DFD), so there is no need to specify the path to the template.

Incidentally, this DFD is normally inaccessible to ordinary users, which can be problematic at times. On the OP's computers, this directory is defined to be a specific directory which is accessible to all computers on her network, meaning that there is no need for multiple templates.

I started using what might possibly be a higher level of Word automation, writing code as follows:

WrdApp:= TWordApplication.Create(nil);
try
 WrdApp.ConnectKind:= ckNewInstance;
 WrdApp.Connect;
 Templ:= 'q400.dot';
 WrdDoc:= TWordDocument.Create (nil);
 try
   WrdDoc.ConnectTo
   (WrdApp.Documents.Add (Templ, emptyParam, emptyParam, emptyParam));
   wrdSel:= WrdApp.Selection;
 except
  on E: Exception do showmessage ('Error on WrdDoc.create ' + #13 + E.Message);
 end;
except
 on E: Exception do showmessage ('Error on WrdApp.create ' + #13 + E.Message);
end;

One annoying aspect of using TWordApplication and TWordDocument is that the arguments to their functions have to be of type 'OleVariant' and not simple integers or strings; this is why the 'templ' variable is assigned the value 'q400' and then passed as an argument to the 'add' procedure. My initial code didn't have the exception handling, but I'm pleased that I added it, as I could tell that there was an error whenever I tried to create the document. After scratching my head for a while, I realised that Word wasn't able to find the template that I wanted, even though it existed in the the default template directory.

It became clear after a while that I would have to pass the complete path to the template in order to open it. Obviously I couldn't write the naked path the source code, as the template's location on my computer is different to its location on the OP's computer. A quick Internet search revealed that one has to discover what the DFD is, then pass this value to the 'open' function. Fortunately, there is a way to do this, although it can only be done after the Word instance has been created (as opposed to getting the value from the registry, for example).

dir:= wrdApp.Options.DefaultFilePath[wdUserTemplatesPath];
Templ:= dir + '\q400.dot';

Once I had added this code, I no longer got the dreaded 'Invalid pointer operation' message! Backtracking, I realised that I didn't need to use the TWordApplication/TWordDocument type code and could continue to use my previous 'CreateOleObject' code, as long as the template's location was defined explicitly.

This is one definite change between the automation code needed for Office 2010 and for previous versions. It's a shame that I found no reference to it anywhere (most automation code tends not to use templates).

The Unthanks/2

I can tell when a piece of music has got under my skin: I dream about it. I woke up this morning at about 2am after dreaming almost constantly that I was a judge on the Israeli version of 'American Idol' and had to decide who would go through to the next stage of the contest: Rachel Unthank or Becky Unthank.

Of course, it didn't help that I had been listening to 'The Bairns' on my journey to and from MBA studies yesterday evening, nor did it help that when I got home, my wife was watching the Israeli version of 'American Idol', in which the contestants were performing as duos and the judges had to decide who would go through to the next stage of the contest. The repetition here is intentional.

There are nights when I seem to dream the same thing over and over (each night is a different dream, but within each night, the dream gets repeated), which can be very exhausting.

I've got to the stage where I can tell the girls' voices apart: Becky's is more breathy, if that is a legal adjective. She sings the first verse of 'Blackbird', whereas Rachel sings the second verse. The differences between their vocal tones are becoming more apparent every time I listen. I have to admit that I find that breathy quality somewhat arousing.

My fellow blogspot blogger, "Plastic Bag", has also written a few blogs about 'The Bairns'.

Crocs

As I have written before, I have various problems with my feet. The copper socks help to an extent, but when the weather gets warmer, my feet feel enclosed and pressured by shoes and socks, and so I normally wear sandals. Unfortunately, the open sandals exacerbate my cracked heel condition so after a couple of days wearing sandals, I have to revert to socks. This year I decided to break the loop.

Ideally, what I need is a pair of shoes which are very light, have a closed heel and an open toe. Whilst I doubt that there is such a mythical beast, shoes produced by Crocs might hold the answer. A quick look through their catalogue led me to the 'Crocs tideline leather shoe' and another look showed me where the nearest Crocs store is. I purchased one pair with light coloured leather and one pair in black; at the moment I am wearing the light coloured shoes and I am enjoying every moment. They are very comfortable, prevent the heels from cracking and of course let my feet sweat without causing problems.

The shoes are also very stylish and go well with all my long trousers - and shorts as well. This is definitely a good buy, as opposed to the clogs which I bought from Teva Naot a month ago.

The Unthanks

I'd heard the name Unthank a few times in the past few months, but apart from noting its strangeness, it had passed me by. I read about many obscure folk acts but the chance of me actually hearing them is so small that I frequently don't pay attention. My interest was definitely piqued when I read that they had recorded a cover of King Crimson's "Starless" (this seems to be the flavour of the month). I wondered how they would handle - if at all - the 13/8 instrumental portion, let alone the freak out section later on.

The song is currently available on YouTube; one listen was enough to convince me that I was listening to something valuable.

With thanks to sources who shall remain anonymous, I was able to listen (and listen and listen) to an earlier record of theirs, 'The Bairns' (don't pay the amazon.com price - British Amazon have it for four and a half pounds, and I'm ordering my own copy tomorrow). I've barely listened to anything else since becoming acquainted with this disc a week ago. I can understand why the Unthanks can polarise people: some of the songs are somewhat hard to take, with sparse accompaniments and harmonies in parallel fifths ('I wish' is particularly harrowing with the wife shouting from the bedroom, "turn that noise off!"). But to me, it's captivating!

This morning I was looking for some background information on Rachel and Becky Unthank, when I stumbled on this interview with Adrian McNally, who was their manager and is now their pianist and Rachel's husband. There's a paragraph which I want to quote here:

The vision I had of the band, from before we even started working together, was such a eureka moment, that every decision and direction since has been easy. All we have to do is look at the blueprint and ask if it fits. That pivotal, central idea was to afford Rachel and Becky a soundscape that they could sing completely independently of. I wanted them to be able to sing just as they would unaccompanied, as traditional singers do, without having to worry about staying in time with other players. Rather than seeing the music as accompaniment, I wanted to create a reflective, musical alter-discourse to their songs. Two narratives running side by side. Eerily detached accompaniments, at once abstract and unobtrusive. The result is hopefully something that sounds at once traditional and progressive, honest and abstract, simple and complex.

It's good to know that someone has a vision of the band, although not always is that vision realised. I am very happy to know that they have indeed realised and achieved this vision, although I should point out that not all of the songs have a reflective, musical alter-discourse. Some of them are quite traditional (couldn't avoid that pun!) in their use of vocal and musical harmony, but then again, some of the songs come from somewhere else.

Like probably everyone else, my first reaction to 'The Bairns' was 'what on earth is this music? What are these voices?', although I was a convert by the end of the disc. My reaction reminds me of the first time that I listened to Eliza Carthy's "Red Rice"; that took a while to assimilate but the effort was worth it. I have no doubt that the instrumentation - or lack of it - has no small part to play. Jazzy piano, violin, cello (and on 'Starless', trumpet) playing chamber arrangements are the sort of thing which I prefer to guitar, bass and drums.

Reharmonising

Since I bought my Washburn guitar a few months ago, I've been playing it exclusively, at the detriment of my Ovation acoustic. Tonight is the Passover Seder, which means that as usual I will be playing a certain song. I seriously considered playing the song on the Washburn along with a little chorus and reverb from my amplifier, but then I realised that the logistics would be too complicated (we'd need an electrical extension cable and an extra microphone) so I decided to play the Ovation as usual.

It was strange for maybe five minutes to play this guitar. The fretboard and strings felt different, not to mention the contoured body (which is more of a hindrance than a help). But after a few minutes, it was back to normal. I should replace the strings on the guitar because they're sounding a bit dull but that can wait. I went over 'the song' a few times to get the fingers working again. This song has a rather jazzy harmonic sequence with an uneven number of chords per bar (sometimes one, sometimes two and sometimes three), making it extremely dense. I originally learnt the song when someone gave me a chord sequence, although I've altered it slightly over the years. Once I heard the song on the radio, which suggested a subtle change as well.

Last night, the singer and I had a short rehearsal. True, we know the song backwards and have been performing it for nearly twenty years but we always like to warm up. Normally she sings in C minor, but this year we're performing the song in A minor, meaning no capo - the open strings can ring out! Thinking of a different introduction to the song, I played the standard samba chord sequence; suddenly I noticed that she could sing the opening line to that sequence instead of the usual one so quickly I reharmonised the first half of the song. Let's hope that I remember what to do.

Leaving her house, I realised that those opening chords are very similar to the opening chords of 'Stairway to heaven'; if it works well, I'm going to play a little musical joke by playing the opening of STH then going into our song. I wonder whether anyone will notice.

Marketing exam results

The results finally arrived today for the Marketing exam, which was held in the first week of March. Five weeks is actually quite quick - normally we are told that we will have results after eight weeks. This time, it didn't take so long  because there are fewer people worldwide who sit the exams in March. Similarly, there are very few people who take two courses in the December semester, so there are fewer exam papers to be marked. Thus we get the marks earlier.

I am disappointed to write that I only achieved a mark of 70 - I had thought that I would have done better. Of course, this is a fine mark and I'm pleased, etc, but my mark in project management had 'spoiled' me and I'm no longer content with a 'mere' 70.

On the other hand, I'll be very pleased if I can get the same mark in Human Resource Management, my current course. It's not a subject which interests me very much, although the material isn't particularly difficult. I suppose this is the ultimate mark of quality - doing well in an exam held in a subject which doesn't interest me.

What I saw on television last night

I don't normally write about what I see on television for two reasons: I'm sure I have nothing original to write, and I don't watch much television anyway (some nights I don't watch at all whereas at other times I watch between an hour and two - it very much depends what is being shown. Apart from three weekly series which I follow, I only watch the news).

Last night on the Channel 2 news was an article about people drawing pictures on the walls of a building which was condemned and destroyed shortly after the drawings were finished. I wasn't following the story very closely because my attention had been drawn to the strange choice of incidental music - "Starless", by King Crimson. The beautiful, post-apocalyptic instrumental opening was there, along with bits of John Wetton singing the first verse. There was also part of Fripp's guitar solo. This is not the first time I've heard KC as incidental music on the news, but it always strikes me as very bizarre.

Following the news was a programme called 'Avudim' - not the American series 'Lost', but rather a new Israeli programme which tries to put people in touch with lost family members, such as unknown fathers and siblings given up for adoption. This is not the sort of thing which I would generally watch but we were watching television in the bedroom whilst our daughter commandeered the television in the living room for one of the inane comedies that she watches.

Last night's program was about a 26 year old woman whose father had left her when she was only her child; her mother remarried, and both the mother and stepfather turned away from the daughter. So basically, this woman never had a nuclear family (it makes me wonder what kind of a mother she is to her two small daughters). She expressed a wish to know who her father was and perhaps to meet him.

Eventually, the father was tracked down to a secluded village in the Kursk region of Russia (scene of a very important battle in the Second World War); the father remembered the daughter well and his version of how he left her was somewhat different to the mother's version. Eventually, the daughter flew to Russia, took the ten hour train ride from Moscow to Kursk and the necessary car ride in order to meet her father.

I suppose that this could be construed a very moving story but I found it voyeuristic in the extreme. I very much side with the woman's husband, who kept on asking why she needed to do this. The woman herself, after having met the father, admitted that she probably wouldn't be keeping in touch; the cultural differences between a rural Russian village (in which time seems to have stood still) and a modern Israeli town are too great.

Advanced SQL for me - NULLIF

It all began so innocently. The Occupational Psychologist (OP) sent me an email yesterday asking that a certain list box (containing customers; one chooses a customer and then sees a report based on that customer) contain only active customers. When I pointed out that at present, there is no way of determining what an active customer is, she wrote back suggesting that I add an 'active' field to the customers table. So I added an 'active' field to the customers table, added this field to the 'Edit Customers' dialog, and modified a few queries so that they loaded only active customers into certain list boxes.

I woke up in the middle of the night with all kinds of ideas about active/inactive customers. When making the additions yesterday evening, I decided to define a new customer as being inactive. Whenever a new docket is opened, its customer will be marked as active. This saves the users from manually defining active customers. Could I do something similar in order to define an inactive customer, where such a customer would be one who has no connected dockets whatsoever, or all the connected dockets are closed (each docket has a status, so I would be looking for dockets of a certain status)?

No problem. The first thing to do would be to add a field 'Closed' to the 'statuses' table; only one status can have this value. Then I could write a query which runs at program startup, listing all customers and the number of open dockets connected to them; all the customers with a count of 0 should be marked as inactive and all the others active.

Then I started considering how I could implement the necessary changes in the 'statuses' table: only one status can be considered 'future' and only one status 'closed'. This would require checking in a dialog which until now has not carried out such checks.

Following on from this, I started envisioning a non-modal dialog which runs on program startup and carries out all kinds of checks (at the moment, I only have the above check to run, but who knows what will happen once I implement this and we get inspired). The dialog should give feedback on what it's doing but shouldn't close automatically so that the user can see this feedback. On the other hand, the dialog should close automatically after a few minutes in order to prevent screen clutter, which means a timer has to be involved....

The query to discover which customers are currently active originally was

select customers.id, count (*)
from customers, dockets, statuses
where customers.id = dockets.customer
and dockets.status = statuses.id
and statuses.closed = 0

The only problem with the above is that customers who don't have any connected dockets would not appear. Then I thought that I should use a left join -

select customers.id, count (*)
from statuses, customers left join dockets
on customers.id = dockets.customer
where dockets.status = statuses.id
and statuses.closed = 0
group by customers.id

but this is liable to return a count of 1 for every customer, because every customer will be in the left join. Not only that, but if a customer doesn't have a docket, then the join with the statuses table will be invalid.

A better query would be

select customers.id, count (*)
from customers left join dockets
on customers.id = dockets.customer
group by customers.id

but this counts all the dockets, not just those which have been closed. The following seems to be the correct query

select customers.id, count (dockets.id)
from customers left join dockets
on customers.id = dockets.customer
left join statuses
on dockets.status = statuses.id
where statuses.closed = 0
group by customers.id

Unfortunately, this query returns 180 rows whereas the customers table has 182 rows!  It transpires that the two missing rows belong to customers who have one docket each, and both those dockets are closed. As far as I am concerned, a customer who only has closed dockets is equivalent to a customer who has no dockets.

After no small amount of faffing about, I discovered the NULLIF function which can be used inside the count function. This function takes two parameters: if the parameters are equal, then it returns null, else it returns the value of the first parameter. Using this function means that the final query is

select customers.id, count (nullif (dockets.status,
(select id from statuses where closed = 1)))
from customers left join dockets
on customers.id = dockets.customer
group by customers.id

The subquery is necessary because theoretically the program doesn't know what the closed status is.

Now I've worked out the SQL, I can implement it within the program.

dbProlog

Someone left a comment here the other day asking me to write about Prolog, Delphi and databases. I may have misunderstood his intentions, as I started thinking about how I could implement a subset of Prolog in Delphi using a database! After some cogitation, I wrote the foundations for what I call dbProlog, where 'db' is a contraction of either 'database' or 'damaged brain', depending on one's orientation. This is not some form of belated April Fool's Day joke, but on the other hand, this 'work' is not to be taken too seriously. I see it as a means of exercising my brain when there's nothing else to do.

Following is a table of Prolog statements and their db equivalents.

Prolog	db
male (abraham).	insert into male1 (one) values ('abraham');
male (isaac).	insert into male1 (one) values ('isaac');
female (sarah).	insert into female1 (one) values ('sarah');
parent (abraham, isaac).	insert into parent2 (one, two) values ('abraham', 'isaac');
parent (sarah, isaac).	insert into parent2 (one, two) values ('sarah', 'isaac');
?male (X).	select one from male1;
father (X,Y):- parent (X, Y), male (X).	???
?father (X, isaac).	select male1.one from male1, parent2 where male1.one = parent2.one and parent2.two = 'isaac';

I wrote a very simple, ad hoc parser which converts a Prolog statement into a predicate and its arguments; these are then transformed into the necessary SQL statement. This parser should be replaced by a proper recursive descent parser which tokenises and checks the grammar, but at the moment I can't be bothered.

The major outstanding conceptual problem at the moment is that I can't think of a way of representing rules within an SQL database.  

By now, anyone who is still with me is probably scratching their head wondering where the 'male1', 'female1' and 'parent2' tables came from. These are created on the fly the first time the interpreter comes across the predicates. But how does the interpreter know when this is the first time? Inserting into a non-existent table is not a very good idea. I solved this by having the database contain one permanent table called 'tablenames' consisting of one field, 'name' (this table actually has more than one field, but that's an unnecessary complication at the moment). 

Each time a predicate is used, its arity (the number of arguments) is calculated and a simple SQL statement is issued: select count (*) from tablenames where name = 'male1'; (obviously 'male1' is replaced by the actual predicate and its arity). If this query returns a number greater than 0, then the predicate is known and its table exists. If the query returns 0, then the table has to be created - create table male1 (one varchar (50), primary key (one));. If the arity is two, then the statement becomes create table parent2 (one varchar (50), two varchar (50), primary key (one, two));. Within the interpreter there is a ten member array of strings whose values are 'one', 'two', 'three'...'ten'. This array is used to convert indexes into field names.

Whenever a table is created, an insert has to be performed on the 'tablenames' table to signify that the table has been created. Because SQL tables are permanent, all the predicate tables have to be cleared, either on program startup or completion. I thought it better to do this on startup, so the tables are available for inspection with standard SQL tools after the interpreter closes. This is done with two queries: one steps through 'tablenames', returning the name of each table, and the other then drops the table. At the end of this procedure, 'tablenames' is emptied.

Green MBA

There was an article in the online version of The Guardian today about a 'green MBA' which is run by a university in Spain. According to the various comments attached to the article, it turns out that this 'university' is some sort of scam, which is why I'm not bothering to post a link to the article itself. Even so, the article started me thinking about how 'green' my MBA course is and how rapacious it is (all business schools teach their pupils to be rapacious in order to maintain continual growth, no?).

I have to conclude that the course is exceedingly grey and non-rapacious. Maybe one learns to rape and pillage in some of the optional courses, such as 'mergers', but I won't be taking that course.

In fact, the degree seems to be very 'parveh', as we would say in Hebrew: neither meat nor milk. The basics of several disciplines are taught but we are not taught what to do with them. The closest I have got so far to a rapacious course would be Marketing, in which we were taught how to analyse a market and slice it (I'm sure there's a better term but the word escapes me at the moment). We were not taught how to plan an advertising campaign nor how to rip off consumers. In Organisational Behaviour, we learnt the theory of changing company culture and structure but not how to carry it out. We learnt about balance sheets in Accountancy, but not how to improve profitable product lines at the cost of less profitable ones.

Here's a comment to that article which sums up my position:

Business is powerful and it only makes sense to try to change the system via the channel with the most leverage. While some "green" MBA programs are bandwagon-jumpers, there are others, like my alma mater, that are dedicated to changing the way business is done by integrating triple bottom line (people, planet and profit) into every course, from finance to marketing to management. Check out greenmba.com and others.

Firebird fixed!

After the untimely demise of the NT server, I had to move the databases and the firebird server to a different computer. I had tried to do this before in December but was unable to connect from other computers on the network.

This time around, I noticed that I was getting a strange error message about temporary files; it turns out that the temporary file directory had been set to a directory which doesn't exist. But more importantly, I still couldn't get any computer to connect to the databases.

When the db server was on the nt computer, the db drive was mapped as 't:', and the connection strings/locations were like "t:\db\manager.fdb". I mapped the db server on the new computer also as "t:" (after removing the previous definition, of course), but naturally this didn't work. I then tried a different solution: replacing the 't:' with the fully qualified network name, ie "\\kivserver\firebird\db\manager.fdb". This too did not work, although the error message was subtlely different. This is where I was in December.

I did a fair amount of googling last night in order to see whether someone had had the same experience as I. Most of the sites which I checked lead to dead ends (in the sense that they didn't help me, not that the sites don't exist!) but eventually I found something which helped. I tried it out on one of the networked computers, and yes! I could connect. 

The secret is not to use the fully qualified network name but to use the hosting computer's ip address and then the local reference (where the database sits on the server). Thus the successful connection string is "10.0.0.202:e:\firebird\db\manager.fdb". Lo and behold, the computers can now connect to the databases. I have just finished doing a complete sweep of three computers defining ten database connection strings, and once again everything is working properly.

On a slightly related note, I see that my previous post about the now defunct NT server managed to attract in one days more visits than my blog attracts in two weeks. Unfortunately I have not been able to determine what the cause of this sudden attraction nor what the search text was. True, Google Analytics does provide search strings, but none of them had the correct number of hits. Keep on hitting me! Although I imagine that those who came here to read about NT4 were probably bemused by the other subjects about which I write.

Farewell NT4

In the summer of 1998, I bought an NT4 server for my company and installed an ethernet network. Whilst we had been computerised for many years, we worked with terminals connected to an Alpha server via a serial connection. This connection was for the ERP-like programs with which we worked at the time; office applications were virtually non-existent and certainly non-network. Thus introducing the server and the network made a huge change in our way of work.

Even though the server had only 256MB memory and a Pentium II processor, it worked beautifully (there were maybe twenty users). The server also hosted an Exchange server which too worked without problems.

The server was officially retired in the beginning of 2007 when my company and another merged. At first, the server lay idle in a corner of the server room but one day I took it home and installed it in the network of the occupational psychologist. My main interest was in the external tape drive - I hoped to use the drive in order to back up all the files - but unfortunately the drive had stopped working. 

The server worked admirably as a file server and as a database server (Firebird) until a few months ago when the first Windows 7 machines started to arrive. Win7 had difficulty reading the files on the NT4 server and even more difficulty in creating files. With regret, we installed another computer (originally Win7 but swiftly downgraded to XP) which served as a file host; the NT4 kept on working as the Firebird server.

You've heard the story about the servers which stopped working every night? When the support team came to find out what had happened, they found that the janitor had unplugged the electricity cable in order to plug in his vacuum cleaner. 

This story may be apocryphal but something similar happened this morning. The noise of the computer annoyed one of the psychologists who was sitting in the same room, so she unplugged the server!  Instant death of the databases! We tried resuscitating the server but it kept on showing 'MBR fault' - obviously the master boot record of the server had been damaged. I imagine that someone, somewhere will know how to fix this, but we need those databases up and running now. 

I've got the databases running on a temporary computer, but I will have to transfer them to the XP server. I tried this a few months ago, and whilst I was able to run the databases, I couldn't connect to them from the Win7 machines. Now I have no option but to try again.

Farewell NT4 server: you served us well for 13 years and now is the time to say goodbye.