5

I have encountered a weird problem along the lines of reasons why excel.exe remains loaded after running a delphi client automation program? question. The problem is one step deeper though.

I have got the code written in Delphi 7 and upgraded it to XE2. After doing so I had the same problem in XE2 and while suggested solution works in simple test app, as soon as I use these lines Excel.exe remains loaded after program exit:

fExcel: Variant; // Tried both as a field of a class and global variable

fExcel := CreateOleObject('Excel.Application');
try
  fExcel.Workbooks.Open(srcPathName);
  fExcel.DisplayAlerts := False;
  ...
  // Offending lines
  fExcel.ActiveWorkBook.ActiveSheet.Range[                                //
      fExcel.ActiveWorkBook.ActiveSheet.Cells[3, 2],                      //
      fExcel.ActiveWorkBook.ActiveSheet.Cells[3+i,1+XL_PT_Tip_FieldCount] //
  ].Formula := VarArr;                                                    //
  ...
  fExcel.ActiveWorkBook.SaveAs(tgtPathName, -4143, '', '', False, False);
  fExcel.ActiveWorkBook.Close;
finally
  fExcel.Application.Quit;
  fExcel.Quit;
  fExcel := Unassigned;
end;

However if I introduce temp variables cl,ch: Variant and replace above 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;

It magically starts working and Excel.exe gets properly disposed of.

Same happens if a sheet variable is used, adding temp variable cures it:

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 (with ref counting?). I can not explain why this works like that, but it does work exactly as described and drives me crazy a bit.

Is there a reason for such behavior and a solution other than adding temp variables each time?

As requested: a complete program:

unit Unit1;
interface
uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, Vcl.StdCtrls, ActiveX, ComObj;

type
  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
  ex, VarArr, cl, ch: Variant;
begin
  ex := CreateOleObject('Excel.Application');
  try
    // Loading template XLS
    ex.Workbooks.Open('C:\Documents and Settings\...\TemplateCommon.xls');
    ex.DisplayAlerts := False;

    //Selecting work sheet
    ex.ActiveWorkBook.Sheets['Sheet ABC'].Select;

    VarArr := VarArrayCreate([0, 9, 0, 12], varVariant);

    // This code works fine    
    {cl := ex.ActiveWorkBook.ActiveSheet.Cells[3, 2];
    ch := ex.ActiveWorkBook.ActiveSheet.Cells[3 + 9, 2 + 12];
    ex.ActiveWorkBook.ActiveSheet.Range[cl, ch].Formula := VarArr;}

    // This code leaves Excel running
    ex.ActiveWorkBook.ActiveSheet.Range[
      ex.ActiveWorkBook.ActiveSheet.Cells[3, 2],
      ex.ActiveWorkBook.ActiveSheet.Cells[3 + 9, 2 + 12]
    ].Formula := VarArr;

    ex.ActiveWorkBook.SaveAs('C:\Documents and Settings\...\out.xls', -4143, '', '', False, False);
    ex.ActiveWorkBook.Close;
  finally
    ex.Quit;
    ex := Unassigned;
  end;
end;

end.

Actual program is much more complicated, but even in this simple test-case the bug exists:

  • In case of temp vars Excel is disposed of immediately.
  • In case with nested vars, Excel is disposed of only when the program is closed, as if seems something holds a reference to it or something.
Community
  • 1
  • 1
Kromster
  • 7,181
  • 7
  • 63
  • 111
  • I expect that the compiler has to make hidden temps when you don't use locals. We can't tell anything about the scope of this code though. A complete program would have shown us that. Frankly, you've all heard me say that a gazillion times. Why are people still not managing to spend the extra 5 minutes needed to post complete programs? – David Heffernan Jan 20 '15 at 07:58
  • @DavidHeffernan: Thanks for your comment. I'm hesitant to post complete program because it will look like a request to "debug that for me". I'm posting small sample in case anyone has met this pattern and could share his knowledge. – Kromster Jan 20 '15 at 08:04
  • We don't want **your** complete program. Just **a** complete program. Cut down from the real program to be the smallest possible repro. Maybe it doesn't matter here that we have a complete program but something flipped in me. Sorry. – David Heffernan Jan 20 '15 at 08:12
  • Thanks for the edit. FWIW it's usually better as a console app since that can be a complete program in one single file. – David Heffernan Jan 20 '15 at 08:46
  • 1
    @DavidHeffernan: I understand your position, just hoped to get an answer with a shorter description, if it's known to anyone. Thanks for the guidance about OP format. – Kromster Jan 20 '15 at 10:58

1 Answers1

-1

Dispose every variable of excel in the reverse order of creation of variable then excel object goes dispose properly.

Kishore Agarwal
  • 69
  • 6