Art Delphi Automation History Home Politics Email me Default Colours Printable Colours
| Web sites | Delphi sites For catching Excel events, or general COM concepts, see also Non-Delphi sites |
| Books | Charlie Calvert's Delphi 4 Unleashed |
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.
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;
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.
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;
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;
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;
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';
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);
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';
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.)
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!"');
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.