Creating an Excel sheet from a Delphi application?

Question

So I place the Excel application OLE icon on my Delphi Form1, and go like this:
uses
{all the others and}, OleServer, Excel97;
.
procedure Form1.write_to_excel;
begin
ExcelApplication1.Visible [0]:= TRUE;
ExcelApplication1.Workbooks.Add (Null, 0);
RangeE:= ExcelApplication1.Range['A1', 'A1'];
RangeE.Value:= 'blah-blah';
.
{many more entries and formatting of cells....}
end;

This works well,  BUT - after I repeat several times opening such Excel application, and closing it again (using Excel Close icon), Excel starts to hang at some point: It opens the toolbar, but not the sheet, and the computer hangs. 
Workaround: Close Excel, close my Delphi application, open manually a blank Excel application, close it again. After that, all works again.... for a while.
What am I missing? Do I need to "destroy/exit/close" Excel in my Delphi code separately? If yes, how?

Mike U · Accepted Answer

I don't like using the OLE Excel application because it is flakey. I do it the old school way and that always gives me good results. You may try to use some of my example below with the OLE icon, let me know if that works. The old school way that works with any dll (so you don't have to have Borland/Embarcaredo write the OLE wrapper for you) is to either:

1.) Import the type library by selecting "Project", "Import Type Library", then select the name of the dll, in your case "Microsoft Excel x.x Object Library (Version x.x)" and then select "Create Unit". Then add the name of the created unit to you form's uses clause, in your case "Excel_TLB". This gives you the advantage of code completion after typing a "." or "(" and also uses Excel's enumerations, but the disadvantage is you need to complete every parameter. The "Open" procedure might have 23 optional parameters, but you have to include all 23.

2.) Know the name of the registered dll's class, in your case "Excel.Application". The advantage is you don't have to include optional parameters. You also don't have to have the dll registered on your development computer. The disadvantage is you don't get code completion. You also have to know what the properties and methods are. You can record a macro in Excel and then look at what was recorded and cut and paste and with a little tweaking for semi-colons and other punctuation get working code.

If you go with option #1:

var
  clsExcelApp : Excel_TLB.ExcelApplication;
begin
  clsExcelApp := Excel_TLB.CoExcelApplication.Create;
  clsExcelApp.Visible := True;

If you go with option #2:

var
  clsExcelApp : Variant;
begin
  clsExcelApp := CreateOleObject('Excel.Application');
  clsExcelApp.Visible := True;

This will create an instance of Excel and start to use it. Either way, to destroy the instance it is the same:

  clsExcelApp.Quit;
  clsExcelApp := Nil;

Anonymous · Answer

Delphi Excel

? · Answer

Next time as the computer starts to hang try the following diagnostic...

Press Ctrl-Alt-Delete and look throught the current processes. See if you have lots of Excel applications still running and taking resources. If so you can close your Delphi app and manually terminate the Excell apps then restart the Delphi app. You then have to make sure that you properly terminate the Excel OLE when you close Delphi.

EDIT:

>>>Do I need to "destroy/exit/close" Excel in my Delphi code separately?<<<

Yes that what I'm saying....

In VB is would use the Close methods of the Excel objects followed by setting the variables to "Nothing" followed by a gc.Collect (Garbage Can Collection. Do this before you close your app that way you won't loose the handles to the threads of the Excel OLE

Trending News

Creating an Excel sheet from a Delphi application?

3 Answers