PhysiologyWeb Logo  Search
PhysiologyWeb Loading...

Delphi Code - Open and Modify an Existing Excel File and Save with a New Name (Save As)
Here is a simple Delphi procedure that opens and modifies an existing Microsoft Excel file, saves the file with a new name (save as), and finally closes the file. The modification is in the form of a few simple additions to cells in the worksheet of the 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.OpenAndModifyAnExistingExcelFileAndSaveAs(Sender: TObject);
var
ExcelFileName, ExcelFileNameNew: 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
ExcelFileNameNew := 'C:\PhysiologyWeb\delphi_code_examples\excel_file_new.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
//connect to Excel Worksheet using either the ExcelApplication or ExcelWorkbook handle
try
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] := 'Delphi is the best!'; //[row,column], in this case, text is added to Cell A1
ExcelWorksheet.Cells[2,1] := 125; //adds a number to Cell A2. Cell format determines how Excel will interpret this.
ExcelWorksheet.Cells[3,1] := '=5+5'; //adds a simple formula to Cell A3
ExcelWorksheet.Cells[4,1] := '=A3*10'; //adds a simple formula to Cell A4
ExcelWorksheet.Cells[5,1] := '=if(A3=10,TRUE,FALSE)'; //adds a simple formula to Cell A5
ExcelWorksheet.Cells[6,1] := '=if(A4=100,"It matches!","Wrong!")'; //adds a simple formula to Cell A6
ExcelWorksheet.Cells[7,1] := '=today()'; //adds today's date to Cell A7
ExcelWorksheet.Cells[8,1].ClearContents; //clears the contents of Cell A8. It does not clear formatting.

ExcelWorksheet.Cells[1,1].Select; //selects Cell A1

ExcelWorkbook.SaveAs(ExcelFileNameNew);
//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