Tuesday, April 30, 2013

Automating Outlook - adding/updating a contact

I have been given a thankless task - exporting contact details from Priority (ERP program) to a public folder in Outlook. I believe that this task is fundamentally wrong but I have to recognise that Priority has a blind spot regarding emails (not email addresses); Outlook covers this blind spot.

The following snippet of a program shows how to open a file containing contact details and then to create new contacts in Outlook. For every contact in Priority there are four lines in the following file - this was done in order to prevent problems when Hebrew, English and digits are mixed in the same line.
procedure TForm1.BitBtn1Click(Sender: TObject); const olFolderInbox = 1; var olApp, NameSpace, myFolder, Items, Contact: OleVariant; datafile: textfile; line: string; begin olApp:= CreateOleObject('Outlook.Application'); NameSpace:= olApp.GetNameSpace('MAPI'); myFolder:= NmSpace.GetDefaultFolder (olFolderInbox); items:= myFolder.items; assignfile (datafile, 'x:\system\GLOB_CONTACTSFILE.txt'); reset (datafile); readln (datafile, line); while not eof (datafile) do begin line:= trim (line); Contact:= items.add; Contact.Firstname:= line; readln (datafile, line); Contact.Lastname:= line; readln (datafile, line); Contact.Email1Address:= line; readln (datafile, line); Contact.MobileTelephoneNumber:= line; contact.save; readln (datafile, line) end; Contact:= unassigned; Items:= unassigned; myFolder:= unassigned; NameSpace:= unassigned; olApp:= Unassigned; close end;
This is fairly simple, after one gets one's head around the Outlook Application model. Basically the code gets to the default contacts folder then starts reading the file. For every four lines in the file, a new Outlook contact is defined, given values and saved.

Now the complications begin. The above code stores the new contacts in the default contacts folder whereas the task definition states that the contacts should be stored in a public folder. For the purposes of this blog, I will assume that the folder's name is Architects. Outlook stores its folders in a recursive structure; in order to retrieve a given folder, one must first obtain a list of the top-level folders and then perform a recursive search within those folders. I had to play around a bit in order to write suitable code, but in the end I managed to define successfully a GetFolder function as follows
function GetFolder (aFolder: OleVariant): olevariant; var i: Integer; begin for i:= 1 to aFolder.Count do if afolder.item[i].name = 'Architects' then begin result:= afolder.item[i]; break end else result:= GetFolder (aFolder.Item[i].Folders); end; ... NameSpace:= olApp.GetNameSpace('MAPI'); myFolder:= GetFolder (namespace.folders);
The required folder name should really be defined as a global constant, but for my purposes, the above is sufficient.

The second problem was about updating contacts who already exist in this public folder. Let's assume that someone opened a contact in Priority and entered the contact's name and telephone number but not the email address. If this information is transferred immediately to Outlook, then the Outlook contact too will be lacking an email address. At some later stage, the email address is added to the contact in Priority; how will the contact in Outlook be updated (assuming that Priority knows to output the contact's data again)?

I searched high and low on the Internet but was unable to find anything about updating existing information. First, the existing record has to be found and only then can it be updated. As can be seen from the above code, I am transferring the contact's forename, surname, mobile telephone number and email address. None of these fields are 100% suitable for quick searching, so I changed the file structure and my program to add the contact's id number in Priority. This is guaranteed to be unique and so serves as a primary key both in Priority and in Outlook (to whom the concept of a primary key is foreign [SQL pun on purpose]). I decided to store this value in the department field of the contact.

Searching for a given record (or for several records) is surprisingly easy in Outlook. First, it's best to create another level of redirection then search for the records with the 'find' method
myFolder:= GetFolder (namespace.folders); items:= myFolder.items; contact:= items.find ('[department] = ' + line);
What happens when one has obtained the required record? It turns out that in order to update the record, one simply needs to change values within the record and then save it. In other words, unlike SQL which has an INSERT method for new records and UPDATE method for existing records, Outlook uses the same SAVE method. So the final code (at least for the time being) is
procedure TForm1.BitBtn1Click(Sender: TObject); var olApp, NameSpace, myFolder, Items, Contact: OleVariant; datafile: textfile; line: string; function GetFolder (aFolder: OleVariant): olevariant; var i: Integer; begin for i:= 1 to aFolder.Count do if afolder.item[i].name = 'Architects' then begin result:= afolder.item[i]; break end else result:= GetFolder (aFolder.Item[i].Folders); end; begin olApp:= CreateOleObject('Outlook.Application'); NameSpace:= olApp.GetNameSpace('MAPI'); myFolder:= GetFolder (namespace.folders); items:= myFolder.items; assignfile (datafile, 'x:\system\GLOB_CONTACTSFILE.txt'); reset (datafile); readln (datafile, line); while not eof (datafile) do begin line:= trim (line); contact:= items.find ('[department] = ' + line); if VarIsClear (Contact) then // ie the contact was not found begin Contact:= items.add; Contact.Department:= line; end; readln (datafile, line); Contact.Firstname:= line; readln (datafile, line); Contact.Lastname:= line; readln (datafile, line); Contact.Email1Address:= line; readln (datafile, line); Contact.MobileTelephoneNumber:= line; contact.save; readln (datafile, line) end; Contact:= unassigned; Items:= unassigned; myFolder:= unassigned; NameSpace:= unassigned; olApp:= Unassigned; close end;

Thursday, April 25, 2013

New TV series

For the past two weeks, I've been following two new (to me) television series broadcast on BBC Entertainment.

The first is 'Rev': a rather irreverent series following a country vicar who is transferred to an inner city parish. I should point out that so far (after three episodes), no mention has been made as to why the vicar moved from remote Suffolk to East London. The titular vicar is played by Tom Hollander, who also helps produce the show; I last saw him playing Guy Burgess in 'Cambridge Spies', but he hasn't been out of work since. His wife is played by Olivia Colman, a more familiar face on television, but unfortunately she doesn't get much screen time. I also recognised Ellen Thomas, who to me will forever be Liz, the school secretary in 'Teachers'.

There are three other characters who each get more screen time than either of those two ladies: the vicar's assistant, the vicar's "boss" (an arch Deacon) and Colin, a congregant. These are the characters who invest the programme with its sly humour.

This is not one of those inane comedy shows with which the BBC seems to fill its schedules; there are very rarely any belly laughs. This show shares with Gavin and Stacey the formula by which the lead character/s are the straight men, whereas the comedy is provided by the supporting cast. Whilst this show is witty, it lacks the lunacy and off the wall nature of G&S. 

The nature of the show's subject matter - Christian community life - it somewhat foreign to an Israeli audience, but my wife didn't have too much difficult in understanding what was going on.

The second series also inhabits a world which doesn't really exist in Israel: Silk is about a barrister who wishes to become a Queen's Counsel (aka 'silk'). In Israel, any lawyer can appear in court, whereas in Britain only  barristers can do this. Lawyers in Israel generally work in partnerships whereas barristers are forbidden to form partnerships but work in 'chambers'; frequently two barristers from the same chambers may appear against each other. My familiarity with barristers stems not from Wikipedia and not from this show, but from John Mortimore's autobiography, which I read years ago.

This series features two familiar faces: the star is Maxine Peake, who I first saw in that iconoclastic Mancunian comedy, "Shameless" (where she played the part of Veronica, the Gallagher's next door neighbour), supported by Rupert Penry-Jones, aka Adam from "Spooks". A third barrister is played by Nina Sosanya (ex-"Teachers"), whose part in the story seems very vague. As far as I can figure out, she is not favoured by the chamber's head clerk and so gets very little work to do.

This is a high level British drama and as such is very welcome.

I have discovered that I suffer regret after having watched certain television shows but keeping no memory of them. In recognition of this fact, I have been recording to DVD both these series (as well as G&S), even though I may never watch them again. It's worth preventing regret if it only costs me a few shekels.

Tuesday, April 23, 2013

Red Rabbit

I found myself reading Tom Clancy novels again on the Kindle. This time around, I've been looking at books of his which I haven't read previously; the first to be completed was 'Red Rabbit', and at the moment I'm barely past the beginning of 'Rainbow Six'.

I've seen several negative reviews of RR scattered around the Web and I think that they are not totally justified. True, the book would have improved immensely had it been edited properly (probably 30% could be lost without damaging the story) and true, I could have done without so many internal ruminations, but what is left is not that bad.

I doubt that anyone would claim that Clancy is a prose stylist, but when looking at the story, it's not too far removed from Le Carre's "Smiley's people" which is as much about defection as it is about anything else.

I think that the book would have been strengthened had the entire 'assassinate the Pope' story been dropped. True, this provides the impetus for the beginning and the story for the end, but to me it was anti-climatic. Had the Russian defector found a different reason to put his life on the line and had the story ended with his debriefing - or even the uncovering of some of the spies he mentions - then I think that the book would have been improved.

I hate to think whether the story about the British eye surgeons leaving in mid-operation in order to have their lunch (with wine) bares any connection to reality. 

I started wondering about the taxi driver who takes the Ryans to their railway station every morning; he might have mentioned his clients' travel habits (and the fact that Jack Ryan was away for a few days) to a friend who might have mentioned it to a friend ... and thence to the Soviets. Nothing was made of this - which means that the driver's inner dialogue could have been removed - but it does lead to lax security.

Sunday, April 21, 2013

Who knows where the time goes

Today we mark the 35th anniversary of the death of Sandy Denny.

I still remember returning home from a hard day in the laboratory (I was in my final term at University, where all my time was spent carrying out a research project): I was driving up Hampstead Main Street when I saw a copy of Melody Maker in a rack outside of Hampstead underground station. "Sandy Denny dead at 31" was the headline.

At the end of the 60s and the beginning of the 70s, Sandy had a string of four excellent albums ('Unhalfbricking', 'Fotheringay', 'The North Star Grassman', 'Sandy') which placed her at the top of the British musical scene. Although it sounds amusing now, she won the Melody Maker's 'best female singer' award two years running, 1970 and 1971; she was even invited to sing on Led Zeppelin's fourth album, further cementing the links between that hard rock band and her folk rock band. One would have thought that the door to super-stardom was open.

I should point out at this stage that when I write "Sandy Denny", I refer to the public side of the singer, and not to her private life. It was only in 1998, twenty years after her death, that the public became aware of the fact that there was some difference between her public and private persona. Although it wasn't clear at the time, with hindsight we can see that she didn't pass through that door as she began to fall prey to her sense of self doubt.

From this pinnacle (1973 onwards), her star began to wane: she released a string of somewhat less than excellent albums ('Old fashioned waltz', 'Rising for the moon', 'Rendezvous') and I was seriously beginning to wonder what had happened to her talent.

In retrospect (and again, ignoring personal data), there were three major figures in her musical life and one wonders how different her musical output would have been had these figures played different parts in the 70s. 

  • What would have happened had Joe Boyd not left Britain in early 1971? Would Fotheringay have completed their second album and gone on to fame?
  • What would have happened had Richard Thompson stayed by her side and not converted to Islam, disappearing to a Norwich retreat in 1972?
  • What would have happened had Sandy not hitched her star to Trevor Lucas?
We will never know.

In premature memory of her death, I played 'Unhalfbricking' on Saturday and remembered how her voice intrigued and influenced the 13 year old me in 1969. Her influence on my sense of harmony was very strong, and only in recent years has that influence faded.

Finally, a thesis title

After spending months trying to find a suitable collection of words which will describe the essence of my doctoral research, I finally hit on what seems to be the perfect title:

An investigation into the prevalence of end user computing (EUC) alongside ERP environments in Israeli SMEs.

(I admit that I don't like the word 'alongside' - I don't want to use the word 'in' twice, so I have to find a synonym).

There are two terms which need explaining here:
  • End user computing (EUC): this is defined as the optional use of computers by professionals and managers (ie knowledge workers) for work related purposes (Zinatelli, Cragg & Cavaye, 1996), or control by users over their computing needs (Raymond & Bergeron, 1992).
  • SMEs - small to medium enterprises: this is a rather nebulous term which is defined differently in different parts of the world. Most studies define SMEs as having fewer than 250 employees, whereas some set the bar at 100 employees.
Whilst I have frequently come across the term SMEs in my literature searches, I hadn't encountered EUC until a week ago; the above definitions  fit almost perfectly the idea that I had in my mind. In an ERP environment, the ERP program itself offers the user many screens and reports. I don't think that it is relevant whether the reports were written by the ERP company itself, thus existing in all applications, by a third party consultant or by an employee of the SME, such as myself. The point is that when faced by a problem, the end user has two options: she can either turn to the ERP expert (who will either find an existing solution or create a new solution) or she can solve the problem herself using tools which are external to ERP. 

It is this second option that I intend to investigate.

Although primarily I want to investigate the prevalence of EUC (how many people use external tools), I am interested in why people use external tools. Thus the first part is quantitative and thus fairly simple, whereas the second part is qualitative and more nebulous.

I want to see whether the following parameters make any difference to the prevalence of EUC
  1. user age
  2. user experience with ERP
  3. user education
  4. department to which the user belongs
  5. the company's area of operation (manufacturing, services, finance, etc)
  6. the percentage of non-SKU (stock keeping units) products in the company's database
  7. where the company is situated in the ERP life cycle (implementing, newish, veteran)
Edit from a week later: a better title which avoids using 'in' twice would be An investigation into the prevalence of end user computing (EUC) in Israeli SMEs which have implemented ERP.

Saturday, April 20, 2013

Emerging from the morning mist

As today is Saturday, it is my habit to take Mocha the dog for a long, off the leash, walk in the hills behind our house. We started out at about 6:45 in the morning, and as winter seems to have returned to our parts, there wasn't very much light at this hour. On the path leading up to the graveyard, I could see two figures looming out of the mist: one seemed to be the size of a dog, so I assumed that the second figure was the dog's owner.

As I came closer, I became puzzled about these two figures. For a start, they weren't moving at all, and the larger figure didn't seem to be a person - in fact, it didn't seem to have an upper body at all. Was I hallucinating? When I came much closer, I could see that in fact I was looking at a donkey and its foal. Mocha had been interested in some smells arising from the vegetation so she had been behind me, but shortly she came up and started trotting towards the donkeys. They in turn started moving away from us. 

Judging by the route that they took, they probably came from an encampment of Bedouin which is sited a few kilometers from the kibbutz. 

I couldn't get very close for a picture (every time I tried, they retreated), so below is the best that I could do.

Thursday, April 18, 2013

Dynamic parametric queries

As I wrote the other day, there is a module in the Occupational Psychologist's management program which lists all the receipts that they have issued. At the moment, there are just over 4,000 rows in the table (and nine columns) - it takes a while for all that data to be returned from the database and displayed on the screen. Although I have sorted out the problem of transferring that data to Excel, there remains the thorny question do I need to display all 4,000 rows every time I display the report? What if I only want receipts which have been issued since 01/03/2013 or cash receipts over 1,000 NIS?

What is needed is some form of preliminary screen with edit boxes which correspond to fields in the report. If the user places a value in an edit box, then the report (or more accurately, the query) has to take this value into account, whereas if the edit box is empty, then it should be ignored.

After some cogitation, I decided to use place a page control component on my form, with two different tab sheets. The first sheet would hold my preliminary, 'parameters', components whereas the second would hold the 'results' grid. I worked up a simple example with two edit boxes whose titles are 'from date' and 'to date'. The parameters sheet also has a 'show' button which builds the query, opens it and displays the results sheet.

My original query was in the form 'select <nine fields> from <some tables>'. The query has no parameters which is why 4,000 rows were being returned. I very naively wrote something like the following pseduocode which is executed when the 'show' button is pressed.
check whether 'fromdate' is empty or holds a valid date if not, exit check whether 'tilldate' is empty or holds a valid date if not exit whereflag:= false; copy the original query to string 'newquery' if 'fromdate' is not empty then begin if whereflag is false then begin whereflag:= true append ' where' to newquery end else append ' and' to newquery append ' receipts.curdate ' to newquery if 'tilldate' is not empty then begin append ' between :p1 and :p2' to newquery parambyname ('p1').asdate:= strtodate (fromdate) parambyname ('p2').asdate:= strtodate (tilldate) end else begin append ' >= :p1' to newquery parambyname ('p1').asdate:= strtodate (fromdate) end end else if 'tilldate' is not empty then begin if whereflag is false then begin whereflag:= true append ' where' to newquery end else append ' and' to newquery append ' receipts.curdate >= :p2' to newquery parambyname ('p2').asdate:= strtodate (tilldate) end
This mess did actually work but it was clear that it was far too wordy. As I wanted two paired values (dates and amounts) and two non-paired values (receipt number and payment type), it was clear that the above was not amenable to expansion. My first optimisation was to remove the 'whereflag' variable: it was needed so that I could append 'where' to the standard query for the first new statement, whereas every following statement would use 'and'. I realised that if I added the meaningless statement 'where 1 = 1' to the standard query, then every dynamic statement could start with 'and'.

The second optimisation came when I realised that being too clever and that using 'between' was counter productive: it would be easier to build the dynamic query if I had a separate clause for each edit box. In other words, instead of writing
and receipts.curdate between :p1 and :p2
I could write
and receipts.curdate >= :p1 and receipts.curdate <= :p2
This lead to the third optimisation which was checking all the edit boxes in one loop to see which were empty and which had values. This required setting the 'tag' value of each edit box. Once all these changes had been incorporated, the final code is as follows
procedure TReceiptsList.ShowBtnClick(Sender: TObject); const maxflags = 6; var fdt, tdt: tdatetime; i, tag: integer; flags: array [1..maxflags] of boolean; s: string; begin if (edFromDate.text <> '' ) and not TryStrToDate (edFromDate.text, fdt) then begin errflag:= true; EdFromDate.setfocus end else if (edTillDate.text <> '' ) and not TryStrToDate (edTillDate.text, tdt) then begin errflag:= true; EdTillDate.setfocus end; if errflag then begin laErrText.caption:= date_errtext; laErrText.Color:= clWhite; end else begin for i:= 0 to componentcount - 1 do if components[i] is TLabeledEdit then begin tag:= TLabeledEdit (components[i]).Tag; flags[tag]:= TLabeledEdit (components[i]).text <> '' end; flags[6]:= rg.ItemIndex > 0; s:= ''; for i:= 0 to querytext.Count - 1 do s:= s + querytext[i]; for i:= 1 to maxflags do if flags[i] then case i of 1: s:= s + ' and receipts.kabnum = :p1'; 2: s:= s + ' and receipts.curdate >= :p2'; 3: s:= s + ' and receipts.curdate <= :p3'; 4: s:= s + ' and receipts.price >= :p4'; 5: s:= s + ' and receipts.price <= :p5'; 6: s:= s + ' and receipts.cash = :p6'; end; with qReceiptsList do try disablecontrols; close; sdsReceiptsList.commandtext:= s; for i:= 1 to maxflags do if flags[i] then case i of 1: sdsReceiptsList.parambyname ('p1').asstring:= edKabnum.Text; 2: sdsReceiptsList.parambyname ('p2').asdate:= fdt; 3: sdsReceiptsList.parambyname ('p3').asdate:= tdt; 4: sdsReceiptsList.parambyname ('p4').asinteger:= strtoint (edFromRCP.text); 5: sdsReceiptsList.parambyname ('p5').asinteger:= strtoint (edTillRCP.text); 6: sdsReceiptsList.parambyname ('p6').asinteger:= rg.itemindex; end; open; finally enablecontrols; dbgrid1.columns[prevcol].title.font.color:= clRed; end; pc.tabindex:= 1; end; end;
I think that the loop which adds the conditions to the query should be merged with the loop which set the query parameters. It also occurs to me whilst editing this entry that instead of converting string values to integers (for the receipt amounts), I could pass them as strings and write parambyname ('p5').asstring:= edFromRCP.text, thus saving an unnecessary conversion. Improvements never end.

I should mention that this arrangement of preliminary parameters screen and final results screen works very well. The user can switch back and forth between the screens and issue new queries regarding receipts which return results at the speed of lightning (unless the user ignores all the parameters and requests everything!). There is at least one other screen in the program which will greatly benefit from this technique, but first I have to show it to the OP.

Blogger note: I am now using the <pre> HTML command to preserve code formatting. This saves me a great deal of time and means that I can use angle brackets without fear of them being replaced.

Sunday, April 14, 2013

Pictures from a balcony (7)

This is a picture taken from our kitchen window. The kumquats are ripening and turning yellow; soon the birds will be coming and eating them. On the left is the balcony with the bird station barely visible at the end. What is visible is what appears to be a transparent bird; this is actually a plastic bird with a solar LED inside which lights up at night. There used to be two, but one night there was a heavy wind which threw one of the birds onto the ground.

Thursday, April 11, 2013

Speeding up Excel

There is a module in the Occupational Psychologist's management program which lists all the receipts that they have issued. At the moment, there are just over 4,000 lines in the table (and nine columns) - it takes a while for all that data to be returned from the database and displayed on the screen. The other day, the OP asked me to add the possibility of outputting the data to Excel.

Whilst I demurred at the need for such a report, it's not my money paying the programmer's cheque, so I quickly wrote the necessary commands using standard Excel automation code. The  first few program runs took so long that I added a progress bar to the form, which enabled me to see that the program was indeed running. I should note that updating a progress bar 4,000 times also takes time, so I changed the code to update the bar once every 32 records.

I started thinking about how I could improve this. My first idea, interestingly, was to put the Excel code into a separate thread, having learnt how to do this with the emails. Whilst this worked, I found that the program was unresponsive, even though the heavy code was in a separate thread. I read a few articles about this on the Internet, which suggested that it was not possible to put Excel in a separate thread. I also had my doubts about the output.

Casting around for new ideas, I discovered code which output files in Excel's native file format. What could be better than this, I wondered. After playing around a bit, I got the code to work on my computer and shortly I was outputting xls files! At first, I continued with the progress bar, but it was clear that this new version was performing much better, so I removed the bar. 

So pleased was I that I considered replacing all the Excel code in the management program with this new library, but a cooler head prevailed. Let me check first that this code works on the OP's computer: even though I checked it with Excel 2003 and Excel 2007, she has Excel 2013 and who knows what surprises await there. Indeed, when I ran the program on her computer, I received an error message stating that the file had been blocked. I tried setting the trust values in her program, but the hard-to-understand Microsoft Hebrew and the fact that I was running her computer by slow remote control defeated me.

I then checked a few facts and discovered that I had been creating what is called BIFF-5 code, which Excel 2013 disdained to read. BIFF-5 was the primary format for Excel 5 (many years extinct); more modern versions prefer BIFF-8 code whereas Excel 2013 prefers BIFF-12 (although it can read BIFF-8). I searched but was unable to find an implementation of BIFF-8 (let alone BIFF-12) in Delphi. I was rapidly losing hope until I remembered something that I had written in a blog entry of mine from three and a half years ago (amazing how much we can forget):

Today I was reading another of Joel Spolsky's blogs, this time on Office formats. A comment at the end hinted at a new solution: Use a simpler format for writing files. If you merely have to produce Office documents programmatically, there’s almost always a better format than the Office binary formats that you can use which Word and Excel will open happily, without missing a beat.

In the same way that I spent the weekend outputting HTML in order to import it into Word, I could output a file in CSV format (as opposed to BIFF-5) and import that into Excel. I wouldn't need any fancy libraries and could code this very easily. So I did. I also checked that it worked properly with Excel 2013.

I thought it best to take the scientific approach and measure how long it took to activate Excel and pass it the data (either by automation or by file import, including the time needed to create the file). I was not surprised to find that the original automation method required on average 44,420 ticks (about 44 seconds) to handle the 4,000 lines (I thought it took longer). The BIFF-5 code on my computer took a mere 5,300 ticks - 8 times faster! 

So how fast could the csv code be? I couldn't believe my eyes when my measurements showed a mere 530 ticks! I checked that the resulting spreadsheet was correct - it wasn't, I had outputted the file with the 'xls' extension instead of 'csv' so Excel had put everything into one column. That mistake was quickly corrected; when I ran the program again, 563 ticks were required for the correct output. I ran the program four more times with an average execution time of 500 ticks. In other words, this csv code ran 111 times faster than the original code and nearly 11 times faster than the supposedly fast BIFF-5 code!!

Most the Excel code which I write simply outputs values into spreadsheets; after the data has got into the spreadsheet, I set column formats via automation, something which can't be transferred in a csv file. In other words, 99% of the time is spent simply transferring values into the spreadsheet. There are other programs which create fancy graphs so obviously in those cases there is more automation and less data transfer; I imagine that I wouldn't see a hundred fold speed improvement there.

Wednesday, April 10, 2013

A new CPAP machine

I hinted a few weeks ago that I was commencing the series of events which would lead to me getting a new CPAP machine. For some reason, the health fund insists that one sees a ENT doctor in order to get an appointment with a sleep doctor; this seems to be needless bureaucracy. Once that first and simple hurdle had been cleared, I was pleased to discover that the sleep doctor makes weekly visits to my local town, which means that I didn't have to go to Jerusalem each time. My appointment with the sleep doctor was similarly brief; I told her that I suffer from sleep apnea and was diagnosed five years ago. She signed a paper then sent me to a neighbouring office where a young woman was waiting with several CPAP machines. Her job was to match patients with machines and monitor the results.

So I left the clinic with a new (to me) CPAP machine and more importantly, a new mask. Even though I had told the technician what the settings on my old machine were, she set the machine to determine those settings automatically. The machine starts at a certain pressure: if I told her that 7 cmH2O was the determined setting, then the machine starts at 10 cmH2O, then slowly reduces the pressure until a certain rate of apnea are measured. The machine then raises the pressure and the rate of apnea should be reduced. This game continues until the machine establishes what the most effective pressure is. Not surprisingly, this was 7 cmH2O.

After a week of varying pressure, I returned to the clinic, where the technician reset the machine so that there would be a constant pressure of 7 cmH2O. I used the machine for another week then returned to the clinic in order to analyse the results. It seems that I had on average 15 apnea per hour during that week; considering that previously I had about 10 apnea per hour, it was clear that this machine was not helping me.

The technician didn't have a better machine with her, but after a few days, another technician (in reality, a salesman) paid me a visit at home and set me up with a different machine which has better diagnostics. After a week with the new machine, the salesman extracted the data and we discovered that I had on average 6 central sleep apnea events per hour and 0.5 obstructive sleep apnea events per hour. I am pleased that the number of apnea per hour decreased substantially but am somewhat surprised that I have CSA and not OSA: CPAP can overcome the latter but not the former.

A small misunderstanding left me for two nights with my old machine and old mask; I get the feeling that I didn't sleep too well during those nights. Then the salesman came and set me up with my new machine. There have been advances in diagnostics and connections in the five years since I got my first machine: that one had an old style serial port socket (I think it's RJ-45: similar to a telephone socket) for which the technician had a lashed-up cable with RJ-45 on one end and a nine pin 'new style' serial plug on the other. I think that the first machine which I tried this year gave its diagnostics via coded read-outs which the technician typed into a program in order to decode them. Certainly she didn't connect the machine to her computer.

The new machine has some form of connector (I don't remember which) but more importantly comes built in with an extractable memory card. Every morning when I turn the machine off, I see a little red light come on while the machine writes its data to the card. According to the salesman, data is written in the form of an Excel spreadsheet. I haven't had a chance to verify this yet as I don't have a card reader. I thought that I would be able to put the card in the mp3 player which I bought a few months ago but this card is much larger than the mp3 player's card.

So I went looking for a card reader. At first I tried Israeli sites, but a google query pointed me to an American site. I bought a reader for the princely sum of $2.50 with free postage to Israel! I hope that it works! I can hardly lose at this price.

I hadn't thought about this before, but that phrase data is written in the form of an Excel spreadsheet intrigues me, seeing as I've spent no small amount of time in the past few days doing exactly that. The gory details will come probably in my next blog.

Pictures from a balcony (6)

More pictures which were previously thought to be lost. My daughter bought a pedigree Boston Terrier puppy about two weeks ago; she brings him over on Friday nights when she comes to visit. Until now, our dog and the puppy have not got on too well: Mocha (our dog) feels that the puppy is invading her territory and also taking our affection, so she frequently growls at the little dog. It doesn't help that the puppy - being a puppy - is full of energy and is constantly running around, examining his foreign environment. As we say, he's like the Duracell bunny - and I wish that my daughter would remove his battery before he visits.

During the Passover holiday, my daughter went away for a few days so she brought her puppy to us so that we could look after him. Both dogs spent most of the time on the balcony where they seemed to have formed some kind of truce. I would take both of them for walks, and it was interested to see how the puppy would follow the adult dog, trying to imitate and learn what the elder dog was doing. These walks didn't faze Mocha very much but the puppy got tired (which was partially the point of the walks).

Those few days were also very hot: we were (and still are, occasionally) experiencing 'sharav' (aka khamsin) weather, which is when a very hot and dry wind blows. These days can be extremely uncomfortable and I tend to be inside (with the air conditioner activated) when a sharav blows. Whilst Mocha seems to spend most of her time laying flat out on the balcony, this was a new experience for the puppy.

Tuesday, April 09, 2013

Mobile phone problem solved

It's just occurred to me what the problem was with my mobile phone. In USB mode, there are three options: 
  • PC Suite - use Nokia PC Suite on your PC
  • Printing and media - use your device with a PictBridge compatible printer or with a compatible PC
  • Data storage - connect to a PC that does not have Nokia software, and use your device as data storage
Until this episode, I have always connected my phone to the computer at work, where I installed Nokia PC Suite several years ago. Thus the USB setting has always been PC Suite. During the holiday, I tried connecting my phone to my home computer, where there is no PC Suite. Unsurprisingly (in retrospect), the phone complained that the setting was wrong. Unfortunately the message was in terse Hebrew and didn't seem to make much sense. 

I must have played with the settings slightly, for one day I came to work (to fetch something) and tried connecting the phone to the computer. As the USB mode was not set to PC Suite, again I got the terse error message.

The penny has just dropped: I reset the USB mode to PC Suite and connected to my work computer with no problem. Should I ever need to connect the phone to other computers (such as my mobile, which I take with me on holidays, then either I will install PC Suite on the mobile or else I'll change the USB mode of the phone. I think that I'll choose the former option as it is less "panic free" - the phone will connect painlessly and I won't need to remember to change anything.

It's a shame that the girl in the service centre yesterday didn't have the same insight.

Passover hall pictures

I mentioned a few days ago that I seemed to be having problems connecting my mobile phone to computers in order to download pictures that I had taken with the phone. I went yesterday to one of my mobile provider's service stations; I was told that there was nothing wrong with the phone, but that some definition which previously had been automatic now had to be set manually each time I connected the USB cable. I have just looked in the phone's manual and discovered how I can reset the automatic definition.

With that out of the way, I was able to connect the phone to the computer and download pictures. Today's pictures were taken two weeks ago on the afternoon and evening of the Passover festival meal. If you guessed that the hall looks like the sort of place where weddings take place, then you would be correct, as for most of the year, this hall (in fact, a huge tent) hosts wedding parties.

As I mentioned before, the walls are decorated with hand stitched panels depicting scenes from the Passover story.

The next picture show the hall in the afternoon when we were rehearsing the songs: the tables have been laid but there are no people. The final picture shows the celebration in full swing. The decorated walls can be seen at the back of the picture.

Sunday, April 07, 2013

A new technique in Word Automation

In the first few months of the year, I spent time creating reports for the Occupational Psychologist in HTML: the reports were intended to be sent by email and this seemed to be the best way of doing this (I probably wrote ad nauseam about doing so). As a result, I managed to restore my HTML skills to the same level that they were about ten years ago. There hasn't really been any need for manual HTML code since, in the same way that there hasn't been any need for manual x86 assembler code. Whilst the latter seems to have gone the way of the dinosaurs, the former can still be useful (indeed, every now and then I manually edit HTML code on this blog; for example, there seems to be no other way of adding a table).

In a totally unconnected fashion, the OP has updated a few of her computers to use Office 2013. I don't know what this version is supposed to bring to the party, but her son was able to obtain licences for a paltry sum ($9?) and so she decided to update. As a result, it seems that certain parts of my Word Automation code don't work as well as they used to; the problem seems mainly to be with tables. In the last few days, there seem to have been all kinds of glitches with tables, making them almost unreadable, and so we needed a new solution.

And so I leveraged my HTML skills. Over the weekend, I spent several hours converting automation code into HTML code; the process is fairly easy but every now and then there are problems. Word allows one to view a table as a two dimensional array of data which is very familiar to programmers (although I find that I hardly ever use arrays any more) and allows one to access cells on a random basis. HTML, by contrast, works in a line by line fashion.

As it happens, the first Word code which I converted to HTML worked in a vertical manner: the Word code would fill in the first column for twenty rows, then the second column for twenty rows, etc. In order to keep my sanity, I declared a two dimensional array within my program, wrote the necessary data on a by column basis, then output it to HTML on a by row basis.

Apart from this extreme example, I also had to deal a few times with sparse arrays: in a row of twelve cells, maybe three have values and the rest are blank. This was easy to deal with using random access code but slightly more difficult with HTML.

I should note that the reports are generated quicker: it is easier to create a text file containing HTML code and then insert that file into a Word document than it is to automate Word.

Here are some examples:

wrdTable:= WrdDoc.Tables.Add (wrdsel.Range, 1, 8, 1, 2);
wrdSel.paragraphformat.alignment:= wdAlignParagraphRight;
wrdSel.ParagraphFormat.LineSpacing:= 10;
wrdsel.paragraphformat.characterunitleftindent:= 0;
wrdsel.paragraphformat.characterunitrightindent:= 0;
wrdsel.paragraphformat.lineunitbefore:= 0;
wrdsel.paragraphformat.lineunitafter:= 0;
wrdTable.cell (1, 1).range.text:= 'Scale';
wrdTable.cell (1, 2).range.text:= 'Mark';
wrdTable.cell (1, 3).range.text:= 'Percentile';
wrdTable.cell (1, 4).range.text:= 'Niner*';
wrdTable.cell (1, 5).range.text:= 'Value';
wrdTable.cell (1, 6).range.text:= 'Average';
wrdTable.cell (1, 7).range.text:= 'Std Dev';
wrdTable.cell (1, 8).range.text:= 'Num Items';
with html do
  add (tabheader + divgrey);
  add (AnsiToUtf8 ('!th!Scale!/th!!th!Mark!/th!!th!Percentile!/th!' +
                               '!th!Niner*!/th!!th!Value!/th!!th!Average!/th!' +
                               '!th!Std Dev!/th!!th!Num Items!/th!!/tr!!/div!'));
As always, I can't actually post the real HTML code here as the Blogger editor interprets my quoted HTML as real HTML (even if using the <pre> tag). The exclamation marks in the above snippet need to be replaced with angle brackets.

There is less fine control with HTML: tables are rendered in some default style. Most of the time, this is not a problem, but here and there I have noticed some functionality which was in the Word program which I cannot reproduce with HTML.

The code is certainly less verbose although I have yet to decide whether this is an advantage. Normally, verbose code is easier to debug and theoretically one can easily get lost in all the HTML tags. One advantage of using HTML is that during development, I can always display what I have coded so far; it is easy to see where tags are missing or code has not been aligned correctly. With Word, the program starts and then crashes whenever the automation is wrong.

I should point out that some reports were a mixture of Word and Excel automation: text would be written, followed by a table, followed by a graph (create by Excel automation, then copied into the Word document). In these cases, I have not touched the Excel automation; most of the Word automation too has been left untouched, and only the tables have been created with HTML (meaning multiple insertions of HTML code).

I await to hear from the OP how the HTML automation is received.

[edit from a few days later: I am chagrined to discovered that I stumbled upon this technique of exporting HTML then reading it into Word several years ago. At the time, the HTML technique seemed to speed things up only by about 10% so I quietly dropped the idea then promptly forgot about it. Today I reread all my blogs about Office Automation and came across this again. Of course, today I have a much more serious problem than speed]

Saturday, April 06, 2013

Back to blogging

I see that it's very easy to get into the habit of not writing blog entries....

The Passover festival has passed over - it was one of those weeks when there were two days festival, two days work (except that the factories and offices were closed, so more holiday) then the weekend then another two days festival. The last day of work was on Sunday 24 March and the next day of work was Tuesday 2 April. It was back to work with a vengeance: each day (Tues,Wed, Thur)  had an important - and long - meeting.

So what did I do during this time and why didn't I write? It all boils down to laziness, I suppose; that's not a quality which I normally have, but sometimes lethargy gets the better of me. There were also some very hot days, which I will quote in my defence. I took some pictures of the garden with my mobile phone and some of the hall in which the Passover celebrations took place, only to discover that there seems to be a problem with the phone. I can see the pictures but the phone is not recognised by my computers and so I've been unable to transfer them. Tomorrow I will finally take the phone to be repaired but as the problem seems to be with the phone's memory card, it seems that those pictures will be lost.

I did spend some time - not as much as I would have liked - studying. I very much miss the weekly meetings in Ramat Gan; these very much helped to keep the material in the brain's temporary memory, and of course having a fixed timetable helps those who slack. Now, every time that I pick up the file, I have to spend a certain amount of time just getting into the correct mental space so that I can continue. I have found some interesting papers which impinge on my subject. I read one doctoral thesis which examined the advantages of Microsoft .NET as an implementation platform - a niche subject if ever there was one. The candidate obviously didn't attend Heriott Watt as the methodology was almost non-existent and the writing was poor (most obviously uses "it's" when meaning "its").

There was work for the Occupational Psychologist but I'll discuss that in a separate entry.

I cooked a great deal: having so many festive meals (and guests) meant that I had to plan meals and shopping in advance. I ran the whole gamut of my menu, even bringing back forgotten dishes such as pineapple chicken. One recipe which I heard about only after the festival might get cooked next year: matza mousaka (fried mince meat placed between slices of matza, then cooked in the oven). Minced meat is a very versatile ingredient; so much can be done with it but unfortunately my family don't like it very much (if at all) which means that the mousaka is likely to remain in the planning stages.