PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Rename and Color Excel Worksheet Tab
Here is a simple Delphi procedure that opens an existing Microsoft Excel file and adds a new worksheet (i.e., tab) to the workbook as the last tab. It then changes the name and color of the newly-created worksheet tab. Some simple text is added to the new worksheet. Finally, the file is saved using the same name (overwrites the original 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.
Naming rules for Microsoft Excel worksheet tabs
Minimum name length 1 character
Maximum name length 31 characters
Characters not allowed [ ] * ? / \ ' :
Duplicate worksheet names Not allowed










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

ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\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.Open(ExcelFileName);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.open
except
ExcelWorkbook := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelWorkbook) = False then
begin
try
//add a new worksheet after the last existing worksheet in this workbook
ExcelWorkbook.WorkSheets.Add(Null,ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count],1,-4167);
//reference
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheets.Add

//rename the new worksheet
ExcelWorkbook.Worksheets[ExcelWorkbook.Worksheets.Count].Name := 'New Sheet';
//assumes any existing sheets do not have the same name
//naming rules
//minimum name length: 1 character
//maximum name length: 31 characters
//Characters not allowed are [ ] * ? / \ ' :
//duplicate worksheet names are not allowed

//change the tab color for the new worksheet
ExcelWorkbook.Worksheets[ExcelWorkbook.Worksheets.Count].Tab.Color := clBlue;
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.tab.color

//ensure the first worksheet tab has no color
ExcelWorkbook.Worksheets[1].Tab.ColorIndex := -4142;
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.tab.colorindex
//https://docs.microsoft.com/en-us/office/vba/api/excel.xlcolorindex

//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
ExcelWorksheet := ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count]; //gets the last worksheet in this workbook
//reference
//https://docs.microsoft.com/en-us/office/vba/api/Excel.Worksheets.Item
except
ExcelWorksheet := Null;
//add error/exception handling code as desired
end;

If VarIsNull(ExcelWorksheet) = False then
begin
ExcelWorksheet.Select;
ExcelWorksheet.Cells[1,1] := 'We created this worksheet as the last tab.'; //[row,column], in this case, text is added to Cell A1
ExcelWorksheet.Cells[2,1] := 'We then changed the worksheet tab color to blue.';
ExcelWorksheet.Cells[3,1].Select; //selects Cell A3
end;

//now save the file
ExcelWorkbook.SaveAs(ExcelFileName);
//or
//ExcelApplication.WorkBooks[1].SaveAs(NewExcelFileName);
//Note: The file is saved using the same name. Therefore, the modified file overwrites the original one. Simply change the file name parameter to save using a different file name.
//reference
//https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.saveas
end;
finally
ExcelApplication.Workbooks.Close;
ExcelApplication.DisplayAlerts := True;
ExcelApplication.Quit;

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






Posted: Saturday, December 12, 2020
Last updated: Sunday, December 13, 2020