Why does excel.exe remain loaded after starting Delphi client automation program?

Question

I wrote a Delphi program that extracts and combines data from several different spreadsheets of a single .XLS file into a text file for further processing. This is the console console Delphi 7 .

An excerpt from the most relevant code snippets will show you that, apparently, my program behaves very well, or at least as much as it should be.

uses ... ActiveX, ComObj ... ; procedure Fatal(s:string); ... Halt(1); var ExcelApp:Variant; (* global var *) begin (* main program block *) coInitialize(nil); ExcelApp:=CreateOleObject('Excel.Application'); try ExcelApp.Visible:=False; ExcelApp.WorkBooks.Open(ExcelFileName); ... XLSSheet := ExcelApp.Worksheets[ExcelSheetName]; ... try XLSRange := XLSSheet.Range[ExcelRangeName]; except Fatal('Range "'+ExcelRangeName+'" not found'); end; if VarIsNull(XLSRange) then Fatal('Range '+ExcelRangeName+' not found'); for row:=XLSRange.Row to XLSRange.Rows[XLSRange.Rows.Count].Row do for col:=XLSRange.Column to XLSRange.Columns[XLSRange.Columns.Count].Column do CellValue:=XLSSheet.Cells[Row,Col].Value; ... if CellValue<>'' then ... ... ExcelApp.Workbooks.Close; ... finally ExcelApp.Quit; coUninitialize; end; end. 

Sometimes, when the program terminates, the XLS remains blocked. Looking at the task manager, I see that the Excel.exe process that was started when the client program was started is still running, the eventhoug client program exited and successfully unloaded.

Do you happen to know what the usual suspects of this behavior are? is there any idea where to always look for excel unloading when client is running?

Answer 1

You need to release the ExcelApp option. It still contains the number of links equal to 1, and therefore Excel is not completely closed.

Add this to your code (marked line):

 finally ExcelApp.Quit; ExcelApp := Unassigned; // Add this line coUninitialize; end; 

Here is a simple code to reproduce the problem and test the solution:

 // Add two buttons to a form, and declare a private form field. // Add OnClick handlers to the two buttons, and use the code provided. // Run the app, and click Button1. Wait until Excel is shown, and then click // Button2 to close it. See the comments in the Button2Click event handler. type TForm1=class(TForm) Button1: TButton; Button2: TButton; procedure Button1Click(Sender: TObject); procedure Button2Click(Sender: TObject); private ExcelApp: Variant; end; implementation uses ComObj; procedure TForm1.Button1Click(Sender: TObject); begin ExcelApp := CreateOleObject('Excel.Application'); ExcelApp.Visible := True; end; procedure TForm1.Button2Click(Sender: TObject); begin ExcelApp.Visible := False; ExcelApp.Quit; // Leave the next line commented, run the app, and click the button. // After exiting your app NORMALLY, check Task Manager processes, and you'll // see an instance of Excel.exe still running, even though it not // in the Applications tab. // // Do an "end process" in Task Manager to remove the orphaned instance // of Excel.exe left from the above. Uncomment the next line of code // and repeat the process, again closing your app normally after clicking // Button2. You'll note that Excel.exe is no longer in // Task Manager Processes after closing your app. // ExcelApp := Unassigned; end; end. 

Answer 2

I ran into the same problem in XE2, and my solution was to replace code samples like this:

 fExcel.ActiveWorkBook.ActiveSheet.Range[ fExcel.ActiveWorkBook.ActiveSheet.Cells[3, 2], fExcel.ActiveWorkBook.ActiveSheet.Cells[3+i,1+XL_PT_Tip_FieldCount] ].Formula := VarArr; 

from:

 cl := fExcel.ActiveWorkBook.ActiveSheet.Cells[3, 2]; ch := fExcel.ActiveWorkBook.ActiveSheet.Cells[3+i,1+XL_PT_Tip_FieldCount]; fExcel.ActiveWorkBook.ActiveSheet.Range[cl, ch].Formula := VarArr; 

The same thing happens in this case when a sheet variable is used:

 sheetDynamicHb := fExcel.ActiveWorkBook.Sheets['Dynamics Hb']; cl := sheetDynamicHb.Cells[52, 2]; ch := sheetDynamicHb.Cells[52+i, 2+3]; sheetDynamicHb.Range[cl, ch].Formula := VarArr; 

Somehow introducing temporary variables ( cl,ch: Variant ) does the trick. It seems that accessing a nested Excel variable does something strange. I can’t explain why this works this way, but it works.

Answer 3

I ran into the same problem trying to close the "zombie" Excel processes (those that remain running if I start them from my application and then force the application to terminate). I tried all the proposed activities with no luck. Finally, I created a combined killer procedure that reliably performs a trick using WinApi if the usual COM methods do not help.

 procedure KillExcel(var App: Variant); var ProcID: DWORD; hProc: THandle; hW: HWND; begin hW := App.Application.Hwnd; // close with usual methods App.DisplayAlerts := False; App.Workbooks.Close; App.Quit; App := Unassigned; // close with WinApi if not IsWindow(hW) then Exit; // already closed? GetWindowThreadProcessId(hW, ProcID); hProc := OpenProcess(PROCESS_TERMINATE, False, ProcID); TerminateProcess(hProc, 0); end;