Importing a csv file into a multi-sheet workbook with automation

There hasn't been a blog about programming for some time. This isn't because I haven't been programming but rather because I haven't come across any new techniques which are worth describing here. Today, of course, is different.

I was tasked with exporting data from the Occupational Psychologist's management program to Excel; I have done this many times previously. But the twist was that two sets of data were to be exported, preferably to separate sheets within one Excel workbook. My first solution was to use 100% automation - create the workbook, add a sheet, add the data, add another sheet, add the data, then finalise. Technically, this is fine but it's slow.

I then looked at my favourite solution for speeding up Excel automation: outputting the data into a csv file then importing this file into Excel. Whilst my standard code handles this without difficulty, I couldn't see how I could import data into the second sheet. The solution came after an interim version in which I imported both sets of data into the same sheet.

All I had to do was create a new sheet, cut the required data from one sheet then paste it into the new sheet. Voila - this way I could have my cake and eat it. I performed the task manually once whilst recording it into a macro so that later I could duplicate the macro code with automation.

The following code starts after the data has been stored in a tstringlist called 'csv'. During the export, I noted how many lines were needed for the first data set ('start') and how many for the second set ('stop').

 s:= mydirname + inttostr (gettickcount) + '.csv';
 with csv do
  begin
   savetofile (s);
   free
  end;

  xlApp:= CreateOleObject ('Excel.Application');
  xlApp.visible:= false;
  xlApp.workbooks.open (s);  // this imports the csv file into the spreadsheet

  xlApp.Range['A' + inttostr (start) + ':F' + inttostr (stop)].Select;  // this builds a string like A12:F46
  xlApp.Selection.Cut;                   // cut the data
  sheet:= XLApp.Sheets.Add;              // add a new sheet to the workbook  
  sheet.select;                          // select the new sheet
  xlApp.activesheet.paste;               // paste the data
 
  xlApp.sheets[2].select;                // go back to the first sheet 
  xlApp.activesheet.Range['A2'].Select;  // remove the selected area after the cut

  xlApp.visible:= true;
  sheet:= unassigned;
  XLApp:= unassigned;

All I had to do was lose the preconception that data from one query had to be imported into one sheet and data from the second into the second sheet. Once I had the interim solution of both data sets in the same sheet, the solution became clear.