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 hides all other pre-existing worksheets. An Excel workbook must have at least one visible worksheet. 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.
procedure TForm1.HideExcelWorksheets(Sender: TObject); var W: Integer; ExcelFileName: String; ExcelApplication, ExcelWorkbook, ExcelWorksheet: Variant; begin //be sure ComObj and Variants units are included in the "uses" clause
W := 0; ExcelFileName := 'C:\PhysiologyWeb\delphi_code_examples\excel_file.xlsx'; //replace file name with the name of your file
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.
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
//hide all pre-existing worksheets For W := 1to ExcelWorkbook.Worksheets.Count-1do ExcelWorkbook.Worksheets[W].Visible := False; //Note: At least one worksheet must be visible.
//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle ExcelWorksheet := ExcelWorkbook.WorkSheets[ExcelWorkbook.Worksheets.Count]; //gets the last worksheet in this workbook //Note: Hidden worksheets can be assigned but cannot be operated on. //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 hid all other pre-existing worksheets.'; 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;