Protecting cells in Excel files

Yesterday the OP and I were discussing time sheets: some of her psychologists prefer to fill an Excel worksheet with their hours worked. Looking at this (and bearing in mind recent work of mine in Priority), I said that I could write a program which would input the data from the worksheet into the appropriate table within the OP's management program. Of course, I noted, we would need to have a standard file format, so then I found myself agreeing to write a program which would output such a file.

The first stages in writing the 'calendar' program were very simple; there was a certain amount of 'fun' involving saving the file in xlsx format, but this was swiftly overcome. I realised that I would have to protect the format of the file - I don't want people changing the structure. In order to do this, I would have to protect the cells in two columns (date and day name) whilst leaving the third column (hours worked) editable. I've never done this with an Excel file, let alone with automation, so first I had to learn how to protect the cells, then how to do this within the framework of a Delphi program.

The necessary code appears below:

// allow users to edit only the hours
 sheet.Protection.AllowEditRanges.Add (Title:= 'range1',
                                       range:= sheet.columns['C:C']);
 sheet.protect;
 sheet.Range['C2'].Select;

 // save as xlsx
 XLApp.Workbooks[1].saveas (filename:= copy (s, 1, length (s) - 3) 
                            + 'xlsx',
                            FileFormat:= xlOpenXMLWorkbook);

The final line is required as the Excel file is created by loading a csv file, thus a regular 'save' would save the file as csv, not xlsx.

The next stage in the process is to convert the xlsx file (after completion) to a tab delimited file by means of the increasingly valuable program which I wrote with Delphi 10. This turns out to be much harder than expected - a topic for another blog entry.