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

Excel - common problems

• Exception when starting Excel
• Excel always creates a separate instance
• Excel shows its outline but not its contents
  

Starting Excel - common problems

I get an EOleSysError exception when I try to start Excel!

You probably have 'Break on exceptions' set to true in the Delphi IDE. You can change this (using the Tools|Debugging|Language Exceptions menu in D4), or you can use code that avoids throwing an exception if Excel is not open - see Opening Excel (early binding).

My program always creates a separate instance of Excel. How can I use the running instance?

Use GetActive Object or GetActiveOleObject - see How to start Excel for examples.

When Excel starts, I see its frame, but no contents!

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.

LCIDs explained

Sometimes you'll find that a parameter is required that isn't mentioned anywhere in the application's documentation. This happens when you need to specify system information: the chief example of this is the lcid, which is a locale identifier, and which pops up all over the place in Excel. The good news is that there is a constant in the Windows unit which provides you with the correct value, LOCALE_USER_DEFAULT:

var
  lcid: integer;
begin
  ...
  lcid := LOCALE_USER_DEFAULT;
  Excel.Visible[lcid] := True; 

There's also a function, GetUserDefaultLCID, but using that value can cause an error, complaining about an 'old format or invalid type library' on some setups. Using LOCALE_USER_DEFAULT for the LCID value should fix that.

Style borders don't work

When I try to set a style's border lines in code, the wrong lines come out, in the wrong colour!

For Excel 97 (at least), the values defined in Excel_TLB.pas for the XlBordersIndex constants don't work when you're setting style border lines. Use these values instead:

xlEdgeLeft := 1;
xlEdgeRight := 2;
xlEdgeTop := 3;
xlEdgeBottom := 4;
xlDiagonalDown := 9;
xlDiagonalUp := 10;

You must also make sure you set the colour of the lines after you set the linestyle/weight, or it won't work.

Thanks to Mario Rothacher for this solution!

The BeforeClose Cancel parameter doesn't work

I've tried setting the Cancel parameter to True, 0, 1, in the Workbook component's BeforeClose event, and in the Application component's BeforeCloseWorkbook event - nothing works!

Unfortunately, the D5 component wrappers don't treat var parameters properly, and nothing you do to the Cancel parameter will stop Excel from closing a workbook. To prevent the user from closing a workbook, you need to use either an Excel macro, or Binh Ly's connection points technique to handle the BeforeClose event instead of the components. (Or save the workbook and reopen it, of course.) Binh Ly's EventSinkImp can be downloaded (with helpful documentation) from his site.