Delphi Code - Open a New Excel File with a Specified Number of Worksheets

procedure TForm1.OpenANewExcelFileWithASpecifiedNumberOfWorksheets(Sender: TObject);
var
   W, NumberOfWorksheetsNeeded: Integer;
   ExcelFileName: String;
   ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant;
begin
     //be sure ComObj and Variants units are included in the "uses" clause

     W := 0;
     NumberOfWorksheetsNeeded := 8;
     ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\new_excel_file.xlsx'; //replace file name with the name of your file

     ExcelApplication := Null;
     ExcelWorkbook := Null;
     ExcelWorksheet := Null;

     try
        //create Excel OLE
        ExcelApplication := CreateOleObject('Excel.Application');
     except
           ExcelApplication := Null;
           //add error/exception handling code as desired
     end;

     If VarIsNull(ExcelApplication) = False then
        begin
             try
                ExcelApplication.Visible := True; //set to False if you do not want to see the activity in the background
                ExcelApplication.DisplayAlerts := False; //ensures message dialogs do not interrupt the flow of your automation process. May be helpful to set to True during testing and debugging.

                //Open Excel Workbook
                try
                   ExcelWorkbook := ExcelApplication.WorkBooks.Add(-4167);
                   //or
                   //ExcelWorkbook := ExcelApplication.WorkBooks.Add;
                   //reference
                   //https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
                except
                      ExcelWorkbook := Null;
                      //add error/exception handling code as desired
                end;

                If VarIsNull(ExcelWorkbook) = False then
                   begin
                        try
                           //create the desired number of worksheets in this workbook
                           //if the default number of worksheets created is less than the desired number
                           If NumberOfWorksheetsNeeded > ExcelWorkbook.Worksheets.Count then
                              begin
                                   While ExcelWorkbook.Worksheets.Count < NumberOfWorksheetsNeeded do ExcelWorkbook.WorkSheets.Add(Null,ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count],1,-4167);
                                   //or use the code below if you do not care about the order in which the sheets are named
                                   //ExcelWorkbook.WorkSheets.Add(Null,Null,(NumberOfWorksheets-ExcelWorkbook.Worksheets.Count),-4167);
                              end;

                           //if the default number of worksheets created is more than the desired number
                           If NumberOfWorksheetsNeeded < ExcelWorkbook.Worksheets.Count then
                              begin
                                   While ExcelWorkbook.Worksheets.Count > NumberOfWorksheetsNeeded do ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count].Delete;
                                   For W := 1 to ExcelWorkbook.Worksheets.Count do ExcelWorkbook.Worksheets[W].Name := 'Sheet'+IntToStr(W); //renames the sheets
                              end;

                           //connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
                           ExcelWorksheet := ExcelWorkbook.WorkSheets[1]; //[1] specifies the first worksheet
                        except
                              ExcelWorksheet := Null;
                              //add error/exception handling code as desired
                        end;

                        If VarIsNull(ExcelWorksheet) = False then
                           begin
                                ExcelWorksheet.Select;

                                ExcelWorksheet.Cells[1,1] := 'We just created a new Excel file with ' + IntToStr(NumberOfWorksheetsNeeded) + ' worksheets!'; //[row,column], in this case, text is added to Cell A1
                                ExcelWorksheet.Cells[2,1] := '=if('+IntToStr(ExcelWorkbook.Worksheets.Count)+'='+IntToStr(NumberOfWorksheetsNeeded)+',"And we also verified that we have the correct number of worksheets!","Unfortunately, it appears that we do not have the correct number of worksheets!")';
                                ExcelWorksheet.Cells[3,1].Select; //selects Cell A3

                                ExcelWorkbook.SaveAs(ExcelFileName);
                                //or
                                //ExcelApplication.WorkBooks[1].SaveAs(NewExcelFileName);
                                //Note: If a file with the new name already exists, it overwrites it. Write additional code to address as desired.
                                //reference
                                //https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
                           end;
                   end;
             finally
                    ExcelApplication.Workbooks.Close;
                    ExcelApplication.DisplayAlerts := True;
                    ExcelApplication.Quit;

                    ExcelWorksheet := Unassigned;
                    ExcelWorkbook := Unassigned;
                    ExcelApplication := Unassigned;
             end;
        end;
end;