Navigation Menu Search PhysiologyWeb
PhysiologyWeb Logo Search PhysiologyWeb
   
— share —
Share on Facebook    Share on X    Share on LinkedIn    Share on Pinterest    Share on Reddit    Email    Copy URL
procedure TForm1.ExcelFileDeleteWorksheets(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
ExcelWorkbook.Worksheets[ExcelWorkbook.Worksheets.Count].Name := 'Last Sheet'; //assumes any existing sheets do not have the same name

//delete the very first worksheet (i.e., tab)
ExcelWorkbook.WorkSheets[1].Delete;

//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 also deleted the first tab that existed in this file. It is gone for good!';
ExcelWorksheet.Cells[3,1].Select; //selects Cell A2
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: Wednesday, March 19, 2025
— share —
Share on Facebook    Share on X    Share on LinkedIn    Share on Pinterest    Share on Reddit    Email    Copy URL