Art  Delphi  Automation  History  Home  Politics  Email me Default Colours  Printable Colours

Automating Microsoft Excel

Contents


Sources of information

Web sites

Delphi sites
Borland's papers
Chapter 17 of C. Calvert's D4 Unleashed
Graham Marshall's Delphi 3 and Excel

For catching Excel events, or general COM concepts, see also
Binh Ly's tutorials

Non-Delphi sites
MS Visual Basic Programmer's Guide
Microsoft Developer's Network
Stephen Bullen's web site

Books Charlie Calvert's Delphi 4 Unleashed

Back to top

How do I ... ?

Back to top

>>>>>How to start Excel<<<<<

The last two methods check to see if Excel is already running before starting a new instance.


Using Delphi 5's Excel components

The new Delphi 5 components make starting Excel very simple. Drop an ExcelApplication component on your form. If the AutoConnect property is true, Excel will start automatically when your program starts; if it's false, just call

  ExcelApplication1.Connect;

when you want to start Excel. To use a running instance of Excel, if there is one, set the ConnectKind property of TExcelApplication to ckRunningOrNew, or to ckRunningInstance if you don't want to start a new instance if Excel isn't running.

Once Excel has started, you can connect other components, such as TExcelWorkbook, using their ConnectTo methods:

  ExcelWorkbook1.ConnectTo(ExcelApplication1.ActiveWorkbook);
  ExcelWorksheet1.ConnectTo(ExcelApplication1.ActiveSheet as _Worksheet);
  ExcelWorksheet2.ConnectTo(ExcelApplication1.Worksheets.Item['Sheet2'] as _Worksheet);
Note that a workbook or worksheet must be open before you can connect to it! See How to open a workbook or How to create a workbook for code to do this.

I advise you not to try to start Excel using any component except the application component, however. At least on some setups, calling the Connect method (NB not the ConnectTo method!) of a Workbook or Worksheet component will cause an exception.


Opening Excel (early binding)

Before you can use this method, you must have imported the type library (Excel8.olb for Excel 97).

One way of starting Excel is to try the GetActiveObject call, to get a running instance of Excel, but put a call to CoApplication.Create in an except clause. But except clauses are slow, and can cause problems within the IDE for people who like Break On Exceptions set to True. The following code removes the need for a try...except clause, by avoiding using OleCheck on GetActiveObject in the case when Excel is not running.

  uses Windows, ComObj, ActiveX, Excel_TLB;
  var 
    Excel: _Application;    
    AppWasRunning: boolean; // tells you if you can close Excel when you've finished
    lcid: integer;
    Unknown: IUnknown; 
    Result: HResult; 
  begin 
    lcid := LOCALE_USER_DEFAULT;
    AppWasRunning := False; 
	
    {$IFDEF VER120}      // Delphi 4
    Result := GetActiveObject(CLASS_Application_, nil, Unknown);
    if (Result = MK_E_UNAVAILABLE) then 
      Excel := CoApplication_.Create 

    {$ELSE}              // Delphi 5
    Result := GetActiveObject(CLASS_ExcelApplication, nil, Unknown);
    if (Result = MK_E_UNAVAILABLE) then
      Excel := CoExcelApplication.Create
    {$ENDIF}  

    else begin 
      { make sure no other error occurred during GetActiveObject }
      OleCheck(Result); 
      OleCheck(Unknown.QueryInterface(_Application, Excel)); 
      AppWasRunning := True; 
    end; 
    Excel.Visible[lcid] := True;
    ... 

There is one problem that you should be aware of, however: starting Excel with GetActiveObject may result in Excel's main frame showing, but its client area remaining hidden. Although you can restore the client area by setting Excel to full screen view and back again, this is obviously unattractive behaviour. It seems to happen only when Excel is running invisibly at the time of the GetActiveObject call.

Excel may be running invisibly, even after you think you've freed it, if any of your Workbook or Worksheet variables are still 'live'. (Check this by pressing Ctrl-Alt-Del once and looking at the list of running tasks.) If you make sure that you free all Excel variables when you close the application, Excel will close down properly. Then starting the application again will not normally cause problems, at least if users of your software won't be running Excel invisibly by any other method.

If your users may be using other software that automates Excel invisibly, however, you may prefer to avoid all calls to GetActiveObject (or GetActiveOleObject), and simply call CoApplication.Create.


Without using the type library

Automation is so much easier and faster using type libraries (early binding) that you should avoid managing without if at all possible. But if you really can't, here's how to get started:

  var 
    Excel: Variant; 
  begin 
    try 
      Excel := GetActiveOleObject('Excel.Application');    
    except 
      Excel := CreateOleObject('Excel.Application');    
    end; 
    Excel.Visible := True; 
Back to 'HowDoI'

>>>>>How to close Excel<<<<<

Assuming an application variable, Excel, and an integer variable LCID that you've assigned the value GetUserDefaultLCID:

Early binding::

  { Uncomment the next line if you want Excel to quit without asking 
    whether to save the worksheet } 
  // Excel.DisplayAlerts[LCID] := False; 
  Excel.Quit; 
 
If you're using the D5 server component, you should disconnect it:
  Excel.Disconnect;       
If you're using an _Application interface variable, you should set it to nil instead:
  Excel := nil;       

Late binding:

  { Uncomment the next line if you want Excel to quit without asking 
    whether to save the worksheet } 
  // Excel.DisplayAlerts := False; 
  Excel.Quit; 
  Excel := Unassigned; 
Note, however, that Excel will hang around in memory, running invisibly, unless you've released all your workbook and worksheet variables. Disconnect any components, set any interface variables to nil, and set any variants to Unassigned to prevent this.
Back to 'HowDoI'

>>>>>How to create a workbook<<<<<

Assuming an application variable, Excel, and an integer variable LCID that you've assigned the value GetUserDefaultLCID:

Early binding:
  var
    WBk: _Workbook;
  ...
    WBk := Excel.Workbooks.Add(EmptyParam, LCID); 


Putting EmptyParam as the first parameter means that a new workbook with a number of blank sheets will be created. If you pass a filename as the first parameter, that file will be used as the template for the new workbook. Alternatively, you can pass in one of the following constants: xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, or xlWBATWorksheet. This will create a new workbook with a single sheet of the specified type.

Late binding:

In late binding, you don't have to specify optional parameters or LCIDs, so you can just do this:

    WBk := Excel.WorkBooks.Add;

If you're not using the type library, but want to use one of the constants mentioned above, you can define them in your code like this:

  const
    xlWBATChart = $FFFFEFF3;
    xlWBATExcel4IntlMacroSheet = $00000004;
    xlWBATExcel4MacroSheet = $00000003;
    xlWBATWorksheet = $FFFFEFB9;

Back to 'HowDoI'

>>>>>How to open a workbook<<<<<

Assuming an application variable, Excel, and an integer variable LCID that you've assigned the value GetUserDefaultLCID:

Early binding:
  var
    WBk: _Workbook;
    WS: _WorkSheet;
    Filename: OleVariant;
  ...
    Filename := 'C:\Test.xls';
    WBk := Excel.Workbooks.Open(Filename, EmptyParam, EmptyParam,
                                EmptyParam, EmptyParam, EmptyParam, 
                                EmptyParam, EmptyParam, EmptyParam, 
                                EmptyParam, EmptyParam, EmptyParam, 
                                EmptyParam, LCID);
    WS := WBk.Worksheets.Item['Sheet1'] as _Worksheet;  
    WS.Activate(LCID);


Late binding:

In late binding, you don't have to specify optional parameters, so you can just do this:

  var
    WBk, WS, SheetName: OleVariant;
  ...
    WBk := Excel.WorkBooks.Open('C:\Test.xls');
    WS := WBk.Worksheets.Item['SheetName'];
    WS.Activate;
Back to 'HowDoI'

>>>>>How to close a workbook<<<<<

Assuming a _Workbook variable, WBk, and an integer variable LCID that you've assigned the value GetUserDefaultLCID:

Early binding

  var
    SaveChanges: OleVariant;
  ...
    SaveChanges := True;
    WBk.Close(SaveChanges, EmptyParam, EmptyParam. LCID);

If you use EmptyParam as the SaveChanges parameter, the user will be asked whether to save the workbook. The second parameter allows you to specify a filename, and the third specifies whether the workbook should be routed to the next recipient.

Late binding

In late binding, it isn't necessary to specify the optional parameters or the LCID, so you can just put this:

  WBk.Close(SaveChanges := True);

or this:

  WBk.Close;
  
Back to 'HowDoI'

>>>>>How to enter data<<<<<

Assuming a _Worksheet variable, WS:

  WS := Excel.ActiveSheet as _Worksheet;

  WS.Range['A1', 'A1'].Value := 'The meaning of life, the universe, and everything, is';
  WS.Range['B1', 'B1'].Value := 42;
You can enter data into many cells at once:
  WS.Range['C3', J42'].Formula := '=RAND()';
You can access cells with row and column numbers or variables, like this:
  var
    Row, Col: integer;
  ...
    WS.Cells.Item[1, 1].Value := 'The very first cell';
    WS.Cells.Item[Row, Col].Value := 'Some other cell';
Back to 'HowDoI'

>>>>>How to copy data<<<<<

From one range to another

  var
    DestRange: OleVariant;
  begin
    DestRange := Excel.Range['C1', 'D4'];
    Excel.Range['A1', 'B4'].Copy(DestRange);
Make sure you're not still editing a cell when you try to copy the range, or you'll get a 'Call was rejected by callee' exception.

From one sheet to another

This example will copy the first column of one sheet to the second column of another:
  var
    DestSheet: _Worksheet;
    DestRange: OleVariant;
  begin
    DestSheet := Excel.WorkBooks['Test.xls'].Worksheets['Sheet1'] as _Worksheet;
    DestRange := Destsheet.Range['B1', 'B1'].EntireColumn;
    Excel.Range['A1', 'A1'].EntireColumn.Copy(DestRange);

Via the clipboard

Using the Copy method without a destination parameter will place the cells in the Windows clipboard:
  Excel.Range['A1', 'B4'].Copy(EmptyParam);
You can then paste the cells to another range, like this:
  var
    WS: _Worksheet;
  begin
    WS := Excel.Activesheet as _Worksheet;
    WS.Range['C1', 'D4'].Select;
    WS.Paste(EmptyParam, EmptyParam, lcid);
Back to 'HowDoI'

>>>>>How to format a range<<<<<

Assuming a _Worksheet variable, WS:

  var
    Format: OleVariant;
  ...
    WS := Excel.ActiveSheet as _Worksheet;
To format one cell in the General number style
    Format := 'General';
    WS.Range['A1', 'A1'].NumberFormat := Format;
To format a range in the 'Text' style, aligned right

Weirdly enough, to give a range a 'text' style you have to set its NumberFormat property to '@':

  with WS.Range['A1', 'M10'] do
  begin
    NumberFormat := '@';
    HorizontalAlignment := xlHAlignRight;
  end;
To format a range of cells with the 'March 4, 1999' date style
    Format := 'mmmm d, yyyy';
    WS.Range['B1', 'C10'].NumberFormat := Format;
To format an entire column in a customized currency style
    Format := '$#,##0.00_);[Red]($#,##0.00)';
    WkSheet.Range['C1', 'C1'].EntireColumn.NumberFormat := Format;
To set the text in a cell to 20pt Arial, bold, and fuchsia
    with Excel.ActiveCell.Font do
    begin
      Size := 20;
      FontStyle := 'Bold';
      Color := clFuchsia;
      Name := 'Arial';
    end;

To change the cell's colour

    Excel.ActiveCell.Interior.Color := clBtnFace; 
or
    Excel.Range['B2', 'C6'].Interior.Color := RGB(223, 123, 123); 
To make the first three characters in a cell bold
  var
    Start, Length: OleVariant;
  ...
    Start := 1;
    Length := 3;
    Excel.ActiveCell.Characters[Start, Length].Font.FontStyle := 'Bold';
    Start := 4;
    Length := 16;
    Excel.ActiveCell.Characters[Start, Length].Font.FontStyle := 'Regular';
Back to 'HowDoI'

>>>>>How to add a name to a workbook<<<<<

  var
    WB: _Workbook;           
    N: Excel_TLB.Name;  // or N := Excel97.Name; if you're using D5 
  begin
    WB := Excel.ActiveWorkbook;
    N := WB.Names.Add('AddedName', '=Sheet1!$A$1:$D$3', EmptyParam, EmptyParam,
                 EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam,
                 EmptyParam, EmptyParam);
The first parameter is the new name, the second is what the name refers to. If the third parameter is set to False, the name will be hidden and won't appear in the Define Name or Goto dialogs.

Two possible problems to note here. First, to declare a variable of type 'Name', you'll probably need to scope it explicitly, so:

    N: Excel_TLB.Name; 
Secondly, note that the $ signs in the RefersTo parameter are essential. Leaving them out will cause a varied assortment of unexpected results. (Thanks to Airy Magnien for pointing this out.)

Back to 'HowDoI'

>>>>>How to add a macro to a workbook<<<<<

You can modify Excel VBA code at run time, by writing directly to the code module in the Excel VBA editor, adding or deleting lines and events. The important object here is the CodeModule object (cunningly made hard to find in the Excel VBA help). This is declared in the VBIDE97.pas file. Here's an example of how to use it:

uses 
  VBIDE97; // or VBIDE_TLB for Delphi 4
var
  LineNo: integer;
  CM: CodeModule;
begin
  CM := WBk.VBProject.VBComponents.Item('ThisWorkbook').Codemodule;
  LineNo := CM.CreateEventProc('Activate', 'Workbook');
  CM.InsertLines(LineNo + 1, '  Range("A1").Value = "Workbook activated!"');
Back to 'HowDoI'

>>>>>Downloads<<<<<

You can download an example project for Excel automation from here.

Stefan Cruysberghs has written a component to help in exporting data from a TDataset to Excel. Download TscExcelExport here.

Mike Zambeck has written a small component and project that demonstrates importing csv files, appending, and formatting ranges. Download TExcelCSV here.

Back to 'HowDoI'