reasons why excel.exe remains loaded after running a delphi client automation program?

Question

I wrote a Delphi program that extracts and consolidates data from several different spreadsheets of a single .XLS file, to a text file for later processing. It is a Delphi 7 console program.

An excerpt of the most relevant pieces of code will show you that, apparently, my program is pretty well behaved or at least as much as it needs to 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 exits, the XLS remains locked. Looking at the Task Manager, I see that Excel.exe process that was started when the client program ran, is still running, eventhoug the client program has exited and succesfully unloaded.

Do you happen to know what are the usual suspects for this behaviour? have any idea where to look for always unloading excel upon client execution?

Answer 1

You need to release the ExcelApp variant. It still holds a reference count of 1, and therefore Excel isn't completely closed.

Add this to your code (the marked line):

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

Here is some 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's 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 have encountered much the same problem in XE2 and my solution was to replace such code samples:

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

with:

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;

Same happens in this case, where 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 temp variables (cl,ch: Variant) does the trick. It seems like the nested Excel variable access does something odd. I can not explain why this works like that, but it does work..

Answer 3

I faced the same issue trying to close "zombie" Excel processes (the ones that stay running if I launch them from my app and then forced terminate the app). I tried all suggested actions with no luck. Finally I created a combined killer procedure that robustly does the trick using WinApi if 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;