Using Delphi to Control MS Office via COM

• Introduction
• Excel
• See also:
  • Delphi Programming Main Index
  • OLE and COM
  • Delphi and DLLs
  • Excel object hierarchy

Introduction:

• Since Office 97, Microsoft has built into its Office programs COM interfaces which enable a programmer using VB or Delphi to control virtually every aspect of the Office programs much like using macros or Visual Basic for Applications does within the Office programs.
• To utilise this in Delphi, follow the steps:
  • you must first ensure the type libraries (a binary file containing the description of the exposed interfaces, properties & methods of each program and usually has a file extension of tlb or olb or is embedded within a dll) is converted into an Object Pascal unit file which is then stored in the "Imports" subfolder in Delphi, this is easily accomplished via Delphi menu item: Project:Import Type Library, then select the appropriate file and click OK and it will be processed.
    • library files to consider importing are:
      • Excel_TLB.pas
      • Word_TLB.pas
      • Office_TLB.pas (and automatically VBIDE_TLB.pas)
      • MSHTML_TLB.pas (from Win\System\MSHTML.dll)
      • ShdDocVw_TLB.pas (automatically when import IE4 or higher web browser control)
    • nb. Delphi often appends an underscore to objects & methods in the libraries to avoid confusion with Delphi VCL objects/methods.
  • in your project unit file:
    • enter the type library pascal filenames in the interfaces Uses clause
    • declare global application variable(s) in the interface (eg. under private)
      • eg. XLApp: Excel_TLB.Application_;
      • eg. WordApp: Word_TLB.Application_;
    • create a Uses clause under the implementation section and add ComObj and if you wish to use my COM file, enter it too: gaCOM
• There are 2 ways of accessing COM for this purpose:
  • variants:
    • usually the easiest, most terse but is slower in performance and cannot use type-checking to see if you are passing the correct parameters
    • when calling a variant method, Delphi must call the following:
      • IDispatch.GetIDsOfNames to get the Dispatch ID of the method from the application
      • IDispatch.Invoke to pass the ID
      • (see ..\Source\RTL\Sys\COMObj.pas to see how Delphi does this)
  • COM interfaces:
    • more difficult, most verbose but faster performance as less clock cycles and can use type-checking
    • uses array syntax, often requiring a language ID (lcid) but you can always pass [0] as this will give the default language
    • dispinterfaces have the Dispatch ID recorded in the type library file saving 2 of the 3 trips variants need to make
    • some things seem to return error using this method so for some aspects may need to use variants as well
      • eg. AChart := WorkSheets.Add(EmptyParam, EmptyParam, 1, xlChart,0) as Chart returns error $800A03EC thus need to use:
        • AChart := XLApp.WorkBooks.item[1].Sheets.Add(EmptyParam, EmptyParam, 1, xlChart, 0) as Chart

Excel:

•  

• 	Variant	Interface
  Opening Excel and creating a new workbook	var sht: Variant; XLApp := CreateOLEObject('Excel.Application'); XLApp.Visible := true; XLApp.Workbooks.Add(xlWBatWorkSheet); XLApp.Workbooks[1].WorkSheets[1].Name := 'My Worksheet'; sht := XLApp.Workbooks[1].WorkSheets['My Worksheet']; sht.Cells[1,1] := 'First Cell'; sht.Cells.Item[1,10] := '=SUM(A2:A9)';	var wsht: _Worksheet; wbks: WorkBooks; wshts: Sheets; wbk: WorkBook; XLApp := Excel_TLB.CoApplication_.Create; XLApp.Visible[0] := True; wbks := XLApp.WorkBooks as WorkBooks; wbks.Add(XLWBatWorksheet,0); wbk := wbks.Item[1]; wshts := wbk.WorkSheets; wsht := wshts.Get_Item(1) as _WorkSheet; wsht.Name := 'My Worksheet'; wsht.Cells.Item[1,1] := 'First cell'; wsht.Cells.Item[1,10] := '=SUM(A2:A9)';
  Examples of use:	var range, Col: Variant; range := XLApp.Workbooks[1].WorkSheets['My Worksheet'].Range['A2:A9']; range.Formula := 'RAND()'; range.Columns.Interior.ColorIndex := 3; range.Borders.LineStyle := xlContinuous; Col := XLApp.Workbooks[1].WorkSheets['My Worksheet'].Columns; Col.Columns[1].ColumnWidth := 5; Col.Columns[1].Font.Bold := True; Col.Columns[1].Font.Color := clBlue;
  Adding a chart:	var ARange, shts: variant; XLApp.WorkBooks[1].Sheets.Add(,,1,xlChart); shts := XLApp.Sheets; ARange := shts.item['My Worksheet'].Range['A1:A9']; shts.Item['Chart1'].SeriesCollection.Item[1].Values := ARange; //nb. instead of ARange could supply values via = VarArrayOf([val1,val2,..]) shts.Item['Chart1'].ChartType := xl3DPie; shts.Item['Chart1'].SeriesCollection.Item[1].HasDataLabels := True; // now lets copy chart to clipboard shts.Item['Chart1'].Select; XLApp.Selection.Copy; //now lets copy range of cells to clipboard: shts.Item['My Worksheet'].Activate; shts.Item['My Worksheet'].Range['A1:A9'].Select; shts.Item['My Worksheet'].UsedRange.Copy;	var aRange: Excel_TLB.Range; aWsht: Worksheet; aChrt: Chart; Index: OLEVariant; aSeries: Series; aSeriesColl: SeriesCollection; aWsht := WorkSheets.Item['My Worksheet'] as Worksheet; aRange := aWsht.Range['A1', 'A9']; aChrt := XLApps.WorkBooks.Item[1].Sheets.Add(EmptyParam,EmptyParam,1,xlChart,0) as Chart; Index := 1; aSeries := aChrt.SeriesCollection(Index, 0) as Series; aSeries.Values := aRange; aChrt.ChartType := xl3DPie; aSeries.HasDataLabels := True; //copy chart: nb. aChrt.Copy(EmptyParam,EmptyParam,0) copies chart to excel NOT to clipboard, thus need to use variants (V) to copy to clipboard: aChrt.Activate; aChrt.Select(EmptyParam,0); V := aChrt.Application_; V.Selection.Copy; //can copy range OK though: ?? SetFocus aWsht.Activate(0); aRange := aWsht.Range['A1', 'A9']; aRange.Select; aRange := aWsht.UsedRange(0); aRange.Copy(EmptyParam);
  Closing application:	if not VarIsEmpty(XLApp) then XLApp.Quit;	if not VarIsEmpty(XLApp) then XLApp.Quit;