Opening Excel from a thread and displaying a file

Three weeks ago, I wrote about programming a thread that would activate Word (in the background, of course) and cause it to display a file that was preprogrammed as an HTML page. I wrote then "the next step is to write similar code that will open Excel in a thread". Well, I wrote the Excel code; this was easy to do once I had the Word example.

Today I came across an example where the simple 'open Excel in a thread' code is not sufficient: it often happens that I want to format certain columns as integers or as dates (although as I documented a year ago, it's better not to format date columns) after the data had been passed to Excel. I would do this with the following two lines:

 xlApp.Columns['B:F'].Select;
 xlApp.Selection.NumberFormat:= XLIntFormat;

I could not do this with the simple thread code as the xlApp variable is not visible to the calling program. The simple solution would be to add an optional parameter to the code that creates the thread; this parameter would be passed down the chain and used after the data had been inserted into Excel. A complication would be that there might be two ranges of columns that need to be formatted. 

Here's a further complication: normally I freeze the top line of an Excel file with the following code:

 sheet.Range['A2'].Select;
 xlApp.ActiveWindow.FreezePanes:= true;

In a certain case, I wanted to freeze the top two lines, meaning that the range would have to be A3 and not A2. Could I do this with another optional parameter? It is my understanding (probably wrong) that there can only be one optional parameter per procedure. This was exacerbated when I thought that I would also have to pass another string parameter denoting which columns should be dates (in the end, I didn't need this). So I decided to use overloaded procedure definitions, a first for me.

I now have the following:

Procedure ThreadExcel (const AFileName: string); overload;

Procedure ThreadExcel (const AFileName, intformat: string); overload;

Procedure ThreadExcel (const AFileName, intformat: string; freeze: integer); 
                       overload;
...

Procedure ThreadExcel (const AFileName: string);
begin
 TExcelThread.Create (AFileName, '', 2);
end;

Procedure ThreadExcel (const AFileName, intformat: string);
begin
 TExcelThread.Create (AFileName, intformat, 2);
end;

Procedure ThreadExcel (const AFileName, intformat: string; freeze: integer);
begin
 TExcelThread.Create (AFileName, intformat, freeze);
end;

I suppose that I also need a variation where there are no columns to be formatted, but a different 'freeze number' is required but I'll cross that bridge when I come to it (actually this could be done by passing afilename, a blank string and the freeze number). The possibility that there might be different types of format passed and/or multiple column selections is handled in the following code:

 Procedure DoFormat (const fmt: string; aformat: string);
 var
  i: integer;
  cols: string;

 begin
  repeat
   i:= pos (',', aformat);
   if i = 0 then
    begin
     cols:= aformat;
     aformat:= '';
    end
   else
    begin
     cols:= copy (aformat, 1, i - 1);
     aformat:= copy (aformat, i + 1, length (aformat));
    end;
   xlApp.Columns[cols].Select;
   xlApp.Selection.NumberFormat:= fmt;
  until aformat = '';
 end;

...
 if intformat <> '' then DoFormat (XLIntFormat, intformat);

Intformat could be 'A:B,E:F' in order to format two column ranges. Passing the format string as a parameter enables formats other than XLIntFormat ('#,##0') to be used. This procedure uses what might be termed 'old style' programming; modern day coders would probably use a stringlist in the following manner (this is unchecked code, so it may be wrong):

var
 i: integer;
 alist: tstringlist;
 
begin
 alist:= tstringlist.create;
 alist.delimiter:= ',';
 alist.delimitedtext:= aformat;
 for i:= 0 to alist.count - 1 do
  begin
   xlApp.Columns[alist[i]].Select;
   xlApp.Selection.NumberFormat:= fmt;   
  end;
 alist.free;
end; 
 

I wonder whether I need to add a comma at the end of the list.

Amitriptyline reduces the amount of deep sleep

After a week of excellent numbers for deep sleep, the data has reverted to a few minutes' deep sleep each night. I've noticed that sometimes my headphones aren't on my ears but have moved slightly; I have no way of discovering whether this is important. I've also noticed that frequently I wake up at around 11:30 pm with reflux arising from GERD, which I handle with omeprazole (a wonder drug). The reflux may arise from my evening medication, two of which can cause reflux. So yesterday I took the medication at 6 pm, a few hours earlier than my usual time.

This morning I read that amitriptyline, a medication that I take in a very low dose to prevent migraines might be causing sleep problems, as well as reflux. According to this site, Amitriptyline also reduces the amount of deep, slow-wave sleep (SWS), also referred to as ‘deep sleep’. During SWS our bodies are in housekeeping mode: our muscles repair themselves and new tissue is grown.

Like many of my medical problems, it all seems like a problem in balance,  where one medication improves something but causes something else. I'll try taking amitriptyline once every other day; I doubt that this will cause the migraines to reappear, but it might increase the amount of deep sleep. I wonder how long it takes the body to metabolise the drug: if I don't take it tonight, will last night's dose still remain in my body?

I have an appointment with a sleep doctor next Thursday; I intended to bring up the subject of increasing the amount of deep sleep even before I read the information about amitriptyline. Maybe she'll reduce the dosage (I see that there are pills with 10 mg - I take 25 mg pills, where 250 mg is the dosage for depression).

Tables in Word documents opened in a thread

Earlier in the month, I wrote about opening Word in a background thread. Today I had reason to revisit the code, as the Occupational Psychologist wanted a few bells and whistles. The first request was to add a flag to cause the document to be displayed in landscape mode - simple. 

The second request was harder: should the document display a table, then that table should have the 'Repeat table header on subsequent pages' flag set. I know how to do this in a live document (I need it for my doctoral thesis) so it was quite easy to create a macro that would set the flag. Unfortunately, it wasn't clear which object within Word executes (Selection.Rows.HeadingFormat = wdToggle); is 'selection' a table? Is it something else? I had the idea to iterate over any tables that might be found within the Word document but not only was this difficult, it also didn't work.

I decided to leave this request for later, but serendipity played its part: one Word document that the management program created was now displayed horizontally, but more importantly, the table header repeated on the second and third pages! How did this happen, I asked myself, as I hadn't figured out how to do this yet. I looked at the HTML code that the program creates and that Word displays: along with the <table> tag, I had also used the <thead> tag. Somewhere along the line, I had defined a style in which the font in the header section is one colour, and the font in the <tbody> section is a different colour. It appears that this tag also causes Word to display the table with repeating header sections!  This saved me a great deal of head scratching.

Sleeping even more deeply

Of course, after writing my previous blog entry on this topic, there were three days with no deep sleep whatsoever. When I saw this about a week ago, I considered the situation and realised that I had loaded four pieces onto the mp3 player. Only two were 'aimed' at increasing the number of delta waves, with the other two intended for increasing 'happiness' (I'm not sure which wave that is or even whether there is a 'happiness' wave). So I deleted the two 'offending' pieces from the player and slept several nights hearing only the two delta wave pieces (that sound like humpback whales - it's not music).

I looked at the data today: since 05/11/21, the numbers have been 45, 31, 2, 20, 0, 55 and 46 minutes respectively. I think that the 2 minutes night was the day that I deleted the pieces (last Monday); there is no explanation yet for Wednesday's poor showing (0 minutes). That night also had a high number of apneic events.

I could have reasonably expected that Thursday's figures would be poor as I went to bed late and was up in the middle of the night with a blocked nose. The usage time agrees with this - only 402 minutes as opposed to 495 minutes the day before - but strangely there were no apneic events at all, along with 55 minutes deep sleep. On Friday night/Saturday morning I 'slept in' - 549 minutes, the longest since 1/10 - but again no apneic events and 46 minutes deep sleep.

So it looks like I've hit on a winning formula to increase the amount of deep sleep that I get.

The Department of Bright Ideas

At work, I introduced the concept of Engineering Changes (an existing module in Priority) several years ago in order to aid communication between sales (who sometimes sell products that have not yet been designed) and engineering (who have to design those products). Over the past few years, this chain has been extended to production, who have to manufacture those products. The traditional way of informing production was to print a copy of the product's blueprint and send it to production.

Someone - not me - suggested putting a terminal on the shop floor so that the workers could scan a bar code on the product, where this bar code contains the EC number. Magically the blueprint would appear on the computer's screen. This is fairly easy to do within Priority, as the bar code would lead directly to the EC record, where the path to the blueprint is stored. Then someone asked whether this could be done outside of Priority: my initial thought was that this would not be possible, as the required files are stored in a labyrinthic directory structure, where the target files are several directories deep. 

Then the penny dropped: I could write a program that receives a barcode for input then performs a recursive search starting with the top level directory in order to find the required file (displaying the file at the end is trivial). This would not be fast but at least it would work. Writing this recursive search was slightly tricky, as most of the examples that I found of recursive directory search simply create a list of all matching files as opposed to looking for one specific file. Another hurdle was that within one terminal directory there could be several files for the same EC - the most recent version is the target (coding this turned out to be much simpler than I originally thought).

So I wrote the program, going through several revisions, tested it at home with a much simpler directory structure and a barcode reader, then tested it at work. The program fulfils its requirements, although it's slow - because of the search.

Last night I was walking the dog when I had another bright idea: instead of constantly performing a recursive search for one target file, I could write a program that performs a complete recursive search, saving its results in a file, then update the current program to search the file (that is loaded into the program's memory via a stringlist). This of course is very fast. Should the target not be found in the stringlist, the original recursive search algorithm gets called; this would happen if a new EC has been created since the directory structure was last mapped.

Writing the mapping program was very easy, being based on the original program, but without the user interface and without the complications of looking for a specific file. I had trepidations about the amount of time that this program would required to create a complete map, but had I thought more deeply about it, I would have realised that it wouldn't require much more time than the current program required to find one file. True, I am 'pruning the search tree' the moment the target file is found, meaning that the original program doesn't have to search the entire directory tree, but I get the feeling that all the target files that are searched appear towards the end of the recursive search, so pruning doesn't save that much time.

But 'the proof is in the pudding': after writing the mapping program (and testing it at home), I ran it at work. The program required two or three minutes to build the directory tree! I had been thinking that it would take half an hour or more. Once I had the directory tree map/index, it was a simple matter to include this in the original program that now works very fast. It's not instantaneous, but I think that the slight delay is due to executing the PDF viewer.

Moral of the story: walking the dog provides one with time for unconstrained thought. As always, these bright ideas seem so simple in retrospect that makes me wonder why I don't think of them earlier. The reason is that we are curtailed, 'blinkered', restrained, in our thinking, and one has to get out of the work environment (by walking the dog) in order to free oneself of the restraints.

DBA update

I last wrote on this topic exactly one month ago, after having interviewed the CEO and after transcribing that interview. I quickly followed this up with another three interviews, each person with his own opinion of the enhancement's success. On the basis of how long it took me to transcribe the first interview, I took my supervisor's advice and sent the other three interviews to a company that specialises in court transcriptions.

After being told that the transcriptions would take about a week (they're not urgent) and paying (415 NIS, which is equivalent at the moment to about $130, the dollar being extremely weak against the shekel), I am still waiting. I have received various excuses and I'm getting to the point of wondering whether I've been defrauded.

Despite not having the transcriptions, I know basically what was said (and I can always listen to the interviews again, should I want to) and so was able to write my first round of conclusions: not only about the enhancement itself, but also (and more importantly) about the pilot study methodology.

I also read a few interesting papers two weeks ago; one is theoretical and the other builds on the theoretical basis to carry out research. The topic is beneficial non-compliance brought about by the use of workarounds. The author of the first paper (who is a contributor to the second paper) proposes that at times, non-compliance of company standards can actually be beneficial. Yes, I know that this sounds like an oxymoron but it is also an interesting idea in itself, and also something that I can use myself. In the case where the use of a workaround mean that the work gets done better, the idea and/or methods of the workaround should be incorporated into the fabric of the ERP system so that the standard procedure (that presumably is lacking in some respect or is too difficult to comply with) can be updated to something that works.

I feel like the Borg: you will be assimilated!

So I added at least a page on beneficial non-compliance (including a table) and wrote about ten pages on the pilot study before sending off this version to my supervisor. I know that the pilot study chapter is not complete, but I'm not sure what I should add. Hopefully now I can maintain momentum with the thesis.

La Rossa

I'm still very high on my discovery of the 5/4 bars in VdGG's "La Rossa". The elongated bars make perfect sense when looking at the prosodic elements of the song as these bars highlight the 'if we made love now' lines. 

I wondered whether these flourishes are maintained in performance; I don't have handy a recording of this song from its time period, but at the moment the three piece band are touring in Scandinavia, and as luck would have it, a version of "La Rossa" was posted a few days ago. 

I have to admit that I don't like live VdGG very much; Hammill especially seems to ruin the songs that were so carefully wrought. 'Fortunately' his contributions to LR are almost inaudible, with Hugh and Guy putting out a tremendous sound. 

I listened closely to the middle section, and as far as I can tell, they played the rhythmic structure as it appears on record. I found it easier to identify the different bar lengths, and maybe I'll be able to transcribe this portion of the song, after all.

Opening Word from a thread and displaying a file

I wrote eight and a half years ago (!) the following:  As I have noted, I have spent no small amount of time updating and expanding the use of email in the Occupational Psychologist's management program. Email is now sent via GMail and works very nicely ... except for one not so small problem: when one is sending an email with an attached file, the program apparently stops working for a minute or so. The solution to this problem is to send the email via a separate thread but until now I have lacked the programming knowledge to implement this.

Today I can rephrase this statement as follows:  As I have noted, output from the Occupational Psychologist's management program is frequently sent to Microsoft Word (and even more frequently to Microsoft Excel). There is one not so small problem with this: when one is running a report whose output is displayed by Word, the program apparently stops working for a minute or so. The solution to this problem is to open Word in a separate thread but until now I have lacked the programming knowledge to implement this.

I have tried this in the past but not succeeded. As it was so long ago, I have no idea what I did then, but now it was fairly simple to write the necessary code that worked the first time that I tried it! I think that I missed using CoInitialise/CoUnInitialise in the Execute procedure. The next step is to write similar code that will open Excel in a thread: there are many more places that call Excel to display data.

Here is the code:

unit Manage08at;

interface

uses Windows, SysUtils, Classes, MyOffice, ComObj, ActiveX, Variants;

Procedure ThreadWord (const AFileName: string);

implementation

type
 TWordThread = class (TThread)
  private
   FFilename: string;
   procedure Execute; override;
  public
    constructor Create (const AFileName: string); reintroduce;
  end;

constructor TWordThread.Create (const AFileName: string);
begin
 inherited Create (False);
 FreeOnTerminate:= True;
 FFilename:= afilename;
end;

procedure TWordThread.Execute;
var
 wrdApp, wrdDoc, wrdSel: variant;

begin
 CoInitialize (nil);
 wrdApp:= GetWordObject;
 wrdDoc:= wrdApp.Documents.Add
             (wrdApp.Options.DefaultFilePath[wdUserTemplatesPath] + '\q400.dot');
 wrdDoc.Select;
 wrdSel:= wrdApp.Selection;
 wrdSel.InsertFile (ffilename);
 deletefile (ffilename);

 wrdSel.WholeStory;
 wrdSel.Font.Name:= 'Arial';
 wrdSel.Font.Size:= 11;
 wrdSel.homekey (wdStory);
 wrdapp.visible:= true;
 wrdSel:= unassigned;
 wrdDoc:= unassigned;
 wrdApp:= unassigned;
 CoUnInitialize;
end;

Procedure ThreadWord (const AFileName: string);
begin
 TWordThread.Create (AFileName);
end;

end.
  

The only thing that is personal development in this code is the MyOffice unit that includes a procedure called GetWordObject that opens an instance of Word.

Counting beats with Van der Graaf Generator (3)

Twelve years ago, I wrote: "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 was listening to the 2021 remix of the song (the only differences that I can hear is a guitar line in the introduction that isn't audible in the version familiar to me from 1976; along with one in the saxophone solo at the end where there are a few new phrases as well as some guitar 'chunks' at the very end) when suddenly I noticed yet another metrical peculiarity of this song, especially the middle section that I referenced above. This either displays metric modulation (three beats in the space of two) or something similar, but that isn't anything new for me.

What caught my attention was ironically the 'if we made love now' phrase at about 4:17 in the song: after having triplets and fours scattered around, that specific line (and its corresponding repeat at 5:22/3) has a five beat bar. The word 'now' is the first beat of five before the next chord change. The same thing happens at the end of the phrase 'it will change all there is yet to be': 'it will change' is one triplet, 'all there is' another triplet, and 'be' is the first beat of a five beat bar. Wow. 

The resources available to me now are more extensive than they were 12 years ago, and I'm going to try and bring this song to the interest one of the YouTubers who relish odd rhythmic constructions.

By chance, today is Peter Hammill's birthday.

Sleeping deeply

I've been continuing my attempts to increase the amount of deep sleep that I get each night by means of listening to 'binaural beats'. 

I wrote a few days ago that "Last night [Friday 30/10] was much better in terms of sleep quality; I also awoke an hour earlier than I intended but still feeling refreshed". That's interesting as apparently I had only 5 minutes of deep sleep that night. I haven't felt any change over the past few weeks, but then I'm a 'morning man' and generally wake with a sharp mind. I often have good ideas whilst walking the dog at 5:45 am.

Since my last blog on the subject, I've been changing the conditions somewhat. I had switched to the 'over the ear' headphones from the swimming mp3 player, but after these kept on falling out, I decided to switch to a headset pair that I found in my spare parts drawer. As opposed to the first two sets of earplugs, these sit outside of the ear; I find that I get better sound quality this way than earplugs that have to be inserted at the correct angle. This headset is quite rigid, meaning that sleeping on my side is not too comfortable. 

I also switched mp3 players: I had written before that the swimming mp3 player is difficult to control. Either it got turned off one night or ran out of battery, but it wasn't working one morning. Despite charging it, I haven't been able to turn it on again. The night after this, I used my mobile phone, but the earphone plug didn't make a good connection. Belatedly I realised that I don't need to keep my 'good' mp3 player reserved for when I travel on the train - as I barely travel on the train these days. The player has built-in memory as well as an SD card, so I cleaned out the built-in memory then transferred my three pieces of binaural beats to the player. I set the player to play from its memory and reduced the volume somewhat. Since then this has been working well, although I must remember to charge it now and then (I woke up in the middle of last night to the sound of silence; I had to find the charging cable and connect it in darkness to re-enable the player).

I also mentioned last time that I had ordered the simplest pair of headphones that I could find. Despite being quoted a delivery date of a few weeks, these arrived two days ago and I was able to use them for the first time last night. Of course, I don't have any data for them yet, but subjectively they were lighter and caused less pain than their predecessors.

And as for readings:

Day	minutes
Wednesday, 27/10	19
Thursday, 28/10	33
Friday, 29/10	0
Saturday, 30/10	5
Sunday, 31/10	21
Monday, 01/11	16
Tuesday, 02/11	29

I hope that the improvements of the past few days continue. Tuesday also registered a fairly high number of apneic events (10); I hope that this is a one-off. The problem with reading data from a few days ago is that I don't remember whether there were any unusual events on a given day.