Delphi Code - Copy and Paste Rows and Columns in Microsoft Excel

procedure TForm1.CopyAndPasteRowsAndColumnsInMicrosoftExcel(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_copy_paste_rows_columns.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);
                   //reference
                   //https://docs.microsoft.com/en-us/office/vba/api/excel.workbooks.add
                   //https://docs.microsoft.com/en-us/office/vba/api/excel.xlsheettype
                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
                           //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;

                                //add some basic information to worksheet

                                //add text to A1 cell [row,column]
                                ExcelWorksheet.Cells[1,1] := 'Row 1 will be copied and pasted into Row 6. Rows 1-3 will be copied and pasted at Row 10.';
                                //change font color in A1
                                ExcelWorksheet.Cells[1,1].Font.Color := clBlue;
                                //change font size of A1
                                ExcelWorksheet.Cells[1,1].Font.Size := 12;
                                //set font in A1 to bold
                                ExcelWorksheet.Cells[1,1].Font.Bold := True;
                                //change the background color of A1
                                ExcelWorksheet.Cells[1,1].Interior.Color := clYellow;
                                //add text to Row 1 in Cell B2
                                ExcelWorksheet.Cells[1,2] := 'Merry Christmas!';
                                //add text to Row 1 in Cell C2
                                ExcelWorksheet.Cells[1,3] := 'Happy New Year!';
                                //add equation to Row 2 in Cell D2
                                ExcelWorksheet.Cells[1,4] := '=582.19-1005.89';
                                //change format of D2 to currency
                                ExcelWorksheet.Cells[1,4].NumberFormat := '$#,##0.00_);[red]($#,##0.00)';
                                //add text to A2
                                ExcelWorksheet.Cells[2,1] := 'Column A will be copied and pasted into Column G. Columns A-D will be copied and pasted at Column J.';
                                //change font color in A2
                                ExcelWorksheet.Cells[2,1].Font.Color := clNavy;
                                //change font size of A2
                                ExcelWorksheet.Cells[2,1].Font.Size := 12;
                                //set font in A2 to bold
                                ExcelWorksheet.Cells[2,1].Font.Bold := True;
                                //change the background color of A2
                                ExcelWorksheet.Cells[2,1].Interior.Color := clAqua;
                                //add equation to A3
                                ExcelWorksheet.Cells[3,1] := '=582.19+1005.89';
                                //change format of A3 to currency
                                ExcelWorksheet.Cells[3,1].NumberFormat := '$#,##0.00_);[red]($#,##0.00)';
                                //auto fit Columns A through D
                                ExcelWorksheet.Columns['A:D'].EntireColumn.AutoFit;

                                //copy entire Row 1
                                ExcelWorksheet.Rows[1].EntireRow.Copy;
                                //paste Row 1 into Row 6
                                ExcelWorksheet.Rows[6].EntireRow.PasteSpecial;
                                //if no parameters are passed to PasteSpecial, content and formatting will be pasted from the source

                                //copy entire Rows 1, 2, and 3
                                ExcelWorksheet.Rows['1:3'].EntireRow.Copy;
                                //paste Rows 1, 2, and 3 at Row 10
                                ExcelWorksheet.Rows[10].EntireRow.PasteSpecial;
                                //if no parameters are passed to PasteSpecial, content and formatting will be pasted from the source

                                //copy entire Column A
                                ExcelWorksheet.Columns[1].EntireColumn.Copy;
                                //paste Column A into Column G
                                ExcelWorksheet.Columns[7].EntireColumn.PasteSpecial;
                                //if no parameters are passed to PasteSpecial, content and formatting will be pasted from the source

                                //copy entire Columns A, B, C, D
                                ExcelWorksheet.Columns['A:D'].EntireColumn.Copy;
                                //paste Column A, B, C, and D at Column J
                                ExcelWorksheet.Columns['J:J'].EntireColumn.PasteSpecial;
                                //if no parameters are passed to PasteSpecial, content and formatting will be pasted from the source

                                //references
                                //https://learn.microsoft.com/en-us/office/vba/api/excel.range.pastespecial

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

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