Delphi controls MS Office applications

Problem/Question/Abstract:

Delphi controls MS Office applications

Answer:

How can you remote control MS Office applications from your Delphi application? The Answer is to use a TOLEContainer.

It requires some interface knowledge to use the right object(s) and their properties. Some samples are added to Delphi demos, but all of them are targeted at MSWord. I have posted examples for Internet Explorer elsewhere and here is a sample for MSExcel:


// procedure is activated when OleOject activates user interface
// procedure copies TStringGrid content to an (OleObject) Excel sheet

procedure TForm1.OleContainer1Activate(Sender: TObject);
var
  ExcelSheet: Variant;
  Count,
    Curent: Variant;
  i,
    j: Integer;
begin
  // first we read how many sheets are open in a specified Excel document
  Count := OleContainer1.OleObject.Application.Sheets.Count;

  // then we read the number of a sheet to witch user wants to add StringGrid content
  Curent := StrToInt(OKBottomDlg.Edit2.Text);

  if Curent <> 0 then
  begin
    if Curent <= Count then
      // if the sheet with index Curent exist then copy content
    begin
      // first we activate the desiered sheet object
      OleContainer1.OleObject.Application.Sheets[Count].Activate;
      // pass the object to a variant variable
      ExcelSheet := OleContainer1.OleObject.Application.ActiveSheet;

      // now we can do what ever we like with it
      ExcelSheet.name := OKBottomDlg.Edit3.Text + IntToStr(Count);
      for i := 0 to StringGrid1.RowCount do
      begin
        for j := 0 to StringGrid1.ColCount do
        begin
          ExcelSheet.Cells(i, j) := StringGrid1.Cells[j, i]
        end
      end;
      // here we copy the content
    end
    else // else if the sheet we are trying to access doesn't exsist
    begin
      // we add new sheets untill the requested
      // user's index is reached ( curent variable )
      for i := Count + 1 to Curent do
      begin
        OleContainer1.OleObject.Application.Sheets.Add
      end;
      // again we do as above
      OleContainer1.OleObject.Application.Sheets[Curent].Activate;
      ExcelSheet := OleContainer1.OleObject.Application.ActiveSheet;
      ExcelSheet.name := OKBottomDlg.Edit3.Text + IntToStr(Count);
      for i := 0 to StringGrid1.RowCount do
      begin
        for j := 0 to StringGrid1.ColCount do
        begin
          ExcelSheet.Cells(i, j) := StringGrid1.Cells[j, i]
        end
      end;
    end
  end;
end;