PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Open a New Excel File with a Specified Number of Worksheets
Here is a simple Delphi procedure that opens a new Microsoft Excel file with a specified number of worksheets (8 in this example). The code confirms that the intended number of worksheets were created and, finally, saves the newly-created file. The most relevant parts of the code are highlighted. Comments are included to explain the code. If you copy and paste the code into your program, be sure to change the form and procedure names to match your setup. For additional Excel automation solutions, see Microsoft Excel Automation with Delphi.
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;






Posted: Saturday, December 5, 2020