How To Automate Excel to Create a New Workbook from Delphi (231614)

MORE INFORMATION

This article parallels other articles that describe the same process using different languages. For more information, please see the following articles in the Microsoft Knowledge Base:

Building the Automation Sample

1. Start Delphi. A new project should be created by default.
2. Add a button to Form1.
3. Replace the contents of the code window for Unit1 with the following:

   unit Unit1;
   
   interface
   
   uses
     Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
     StdCtrls;
   
   type
     TForm1 = class(TForm)
       Button1: TButton;
       procedure Button1Click(Sender: TObject);
       procedure DisplayQuarterlySales(oWS : Variant);
     private
       { Private declarations }
     public
       { Public declarations }
     end;
   
   var
     Form1: TForm1;
   
   Const xlVAlignCenter = -4108;
   Const xlThin = 2;
   Const xlEdgeBottom = 9;
   Const xlDouble = -4119;
   Const xlThick = 4;
   Const xl3DColumn = -4100;
   Const xlColumns = 2;
   Const xlLocationAsObject = 2;
   
   implementation
   
   uses ComObj;
   
   {$R *.DFM}
   
   procedure TForm1.DisplayQuarterlySales(oWS : Variant);
   var
     oResizeRange, oChart, oSeries: Variant;
     iNumQtrs,iRet : Integer;
     sMsg : String;
   begin
     // Determine how many quarters to display data for
     for iNumQtrs := 4 downto 2 do
     begin
       sMsg := 'Enter sales data for ' + IntToStr(iNumQtrs) + ' quarter(s)?';
       if MessageDlg(sMsg,mtConfirmation,[mbYes,mbNo],0) = mrYes then
         break;
     end;
     sMsg := 'Displaying data for ' + IntToStr(iNumQtrs) + ' quarter(s).';
     MessageDlg(sMsg,mtInformation,[mbOK],0);
   
     // Starting at E1, fill headers for the number of columns selected
     oResizeRange := oWS.Range['E1:' + Chr(Ord('E') + iNumQtrs - 1) + '1'];
     oResizeRange.Formula := '="Q" & COLUMN()-4 & CHAR(10) & "Sales"';
   
   
     // Change the orientation and WrapText properties for the headers
     oResizeRange.Orientation := 38;
     oResizeRange.WrapText := True;
   
     // Fill the interior color of the headers
     oResizeRange.Interior.ColorIndex := 36;
   
     // Fill the columns with a formula and apply a number format
     oResizeRange := oWS.Range['E2:' + Chr(Ord('E') + iNumQtrs - 1) + '6'];
     oResizeRange.Formula := '=RAND()*100';
     oResizeRange.NumberFormat := '$0.00';
   
     // Apply borders to the Sales  data and headers
     oResizeRange := oWS.Range['E1:' + Chr(Ord('E') + iNumQtrs - 1) + '6'];
     oResizeRange.Borders.Weight := xlThin;
   
     // Add a totals formula for the sales data and apply a border
     oResizeRange := oWS.Range['E8:' + Chr(Ord('E') + iNumQtrs - 1) + '8'];
     oResizeRange.Formula := '=SUM(E2:E6)';
     oResizeRange.Borders.Item[xlEdgeBottom].LineStyle := xlDouble;
     oResizeRange.Borders.Item[xlEdgeBottom].Weight := xlThick;
   
     // Add a chart for the selected data
     oResizeRange := oWS.Range['E2:' + Chr(Ord('E') + iNumQtrs - 1) + '6'];
     oChart := oWS.Parent.Charts.Add;
     oChart.ChartWizard(oResizeRange,xl3DColumn,,xlColumns);
   
     oResizeRange := oWS.Range['A2:A6'];
     oChart.SeriesCollection.Item[1].XValues := oResizeRange;
     iRet := oChart.SeriesCollection.Count;
     for iRet := 1 to iNumQtrs do
     begin
       sMsg :=  '="Q' + IntToStr(iRet) + '"';
       oChart.SeriesCollection.Item[iRet].Name := sMsg;
     end;
     oChart.Location(xlLocationAsObject,oWS.Name);
   
     // Move the chart so as not to cover your data
     oWS.Shapes.Item(1).Top := oWS.Rows.Item[10].Top;
     oWS.Shapes.Item(1).Left := oWS.Columns.Item[2].Left
   end;
   
   procedure TForm1.Button1Click(Sender: TObject);
   var
     oXL, oWB, oSheet, oRng, VArray : Variant;
   begin
     // Start Excel and get Application Object
     oXL := CreateOleObject('Excel.Application');
     oXL.Visible := True;
   
     // Get a new workbook
     oWB := oXL.Workbooks.Add;
     oSheet := oWB.ActiveSheet;
   
     // Add table headers going cell by cell
     oSheet.Cells[1,1] := 'First Name';
     oSheet.Cells[1,2] := 'Last Name';
     oSheet.Cells[1,3] := 'Full Name';
     oSheet.Cells[1,4] := 'Salary';
   
     // Format A1:D1 as bold, vertical alignment = center
     oSheet.Range['A1:D1'].Font.Bold := True;
     oSheet.Range['A1:D1'].VerticalAlignment := xlVAlignCenter;
   
     // Create an array to set multiple values at once
     VArray := VarArrayCreate([0,4,0,1],varVariant);
     VArray[0,0] := 'John';
     VArray[0,1] := 'Smith';
     VArray[1,0] := 'Tom';
     VArray[1,1] := 'Brown';
     VArray[2,0] := 'Sue';
     VArray[2,1] := 'Thomas';
     VArray[3,0] := 'Jane';
     VArray[3,1] := 'Jones';
     VArray[4,0] := 'Adam';
     VArray[4,1] := 'Johnson';
   
   
     // Fill A2:B6 with an array of values
     oSheet.Range['A2:B6'] := VArray;
   
     // Fill C2:C6 with a relative formula (=A2 + ' ' + B2)
     oRng := oSheet.Range['C2:C6'];
     oRng.Formula := '=A2 & " " & B2';
   
     // Fill D2:D6 with a formula (=RAND()*100000) and apply format
     oRng := oSheet.Range['D2:D6'];
     oRng.Formula := '=RAND()*100000';
     oRng.NumberFormat := '$0.00';
   
     // Autofit columns A:D
     oRng := oSheet.Range['A1:D1'];
     oRng.EntireColumn.AutoFit;
   
   
   
     // Manipulate a variable number of columns for Quarterly Sales Data
     DisplayQuarterlySales(oSheet);
   
     // Make sure Excel is visible and give the user control
   
     // of Microsoft Excel's lifetime
     oXL.Visible := True;
     oXL.UserControl := True;
   
   end;
   
   end.
   						

4. Press the F9 key to run the project. Click the button and Microsoft Excel should start and display a sample worksheet.NOTE: Delphi is manufactured by Borland (Inprise Corporation), a vendor independent of Microsoft; we make no warranty, implied or otherwise, regarding this product's performance of reliability. Contact Inprise for any developer support regarding the Delphi language.