recognize an onclose event with OleVariant?

Question

I´m using a OleAutomation with Excel := CreateOleObject('Excel.Application'); to use the functions Excel.Replace and Excel.SaveAs. These functions are essential in my problem and I cannot leave them out. On the other hand I have to use an OleVariant type, which has no OnCloseEvent. Now my problem begins:

At first a small overview:

**procedure OpenExcel begins**

Excel := CreateOleObject('Excel.Application'); 
//Excel opens a template and replaces placeholders 

**procedure OpenExcel end**;

Now Excel is open with all placeholders replaced. After this procedure the client can alter his sheet, correct mistakes, .. . Ongoing he wants to close Excel or save the Workbook.

And here starts my problem: I want to catch the moment, when the client closes Excel. If his document is saved, all data (some strings like path or color and integers like id´s placed in an object) will comitted to another programm named ELO (document archive) before the OleObject will be destroyed. But OleObjects can´t recognize a close or whatever.

Is there a workaround to solve this problem? Perhaps linking an object to the OleObject or the process itself to determine when Excel is closed by the client?

I looked up if there is a way provided by the \Ocx\Servers\excel200.pas but I didn't found anything. My co-worker advised I should look into the office folder and search for the excel .tlb-file, but it doesn't exsist.

IF THE PROBLEM ABOVE CANNOT BE SOLVED

If this isn´t possible do anyone know how I can use Excel.Replace and Excel.SaveAs with an object, which has a OnClose Event?

My current code for replacement is the following:

//for all sheets in the workbook
for iSheet := 1 to Excel.Worksheets.Count do
begin

  //activate the sheet
  Excel.Worksheets[iSheet].Activate;

  // this will be..
  sWhat := %First String%;  
  //..replaced by..
  sReplacement := %Second String%;

  // warnings off (override message or if excel didn't find a sWhat)
  Excel.Application.DisplayAlerts := False;

  Excel.Cells.Replace(
    What          := sWhat,
    Replacement   := sReplacement,
    LookAt        := xlWhole,
    SearchOrder   := xlByRows,
    MatchCase     := False,
    SearchFormat  := False,
    ReplaceFormat := False
  );

  //warnings on
  Excel.Application.DisplayAlerts := True;
end;

The SaveAs method is similiar. It just sends a command which excel itself can handle like the Excel.Cells.Replace.

I hope the description of my problem is clear enough, my english isn't as good as I want it to be..

Thanks in advance!

You failed to mention what delphi version you are using and which office version... — whosrdaddy, Nov 08 '12 at 10:26
Oh pardon. I use Delphi5 and Office2007 but it should also work for Office2003, that´s also a reason for the OleVariant. ( Tags edited :) ) — KHahn, Nov 08 '12 at 10:28
http://www.delphigroups.info/2/3/991910.html even the objects TExcelApplication, TExcelWorkbook etc. from Microsoft in excel2000.pas have **no eventhandler** for OnClose. — KHahn, Nov 08 '12 at 14:39
Implement 'IAdviseSink' on a class and ['Advise'](http://msdn.microsoft.com/en-us/library/ms686573) your workbook. Voting to close as a duplicate of http://stackoverflow.com/questions/2880090/how-to-catch-a-moment-when-the-external-editor-of-tolecontainer-has-been-closed just instead of oleobjectinterface of ole container, retreive the ioleobject for your workbook. — Sertac Akyuz, Nov 08 '12 at 15:00
That's over my head. I'm just programming with delphi since 3 month and I never touched interfaces. Nevertheless I will try to understand your given example and ask my instructor tomorrow, if anyone here in my company worked with interfaces :) — KHahn, Nov 08 '12 at 15:12

score 0 · Accepted Answer · answered Nov 13 '12 at 13:00

I just wanted to tell you that I didn´t solve this problem with late-binding but instead with early-binding.

For everyone with the same problem here is a possible solution:

uses excel97, excel2000 //order is important
..
var
  ExcelApp : TExcelApplication;
  ExcelWb: _WorkBook;
  ExcelSt: _WorkSheet;
  iLCID: Integer;

Open Excel, load a file, activate a sheet and connect to an OnWorkbookBeforeClose method

  // open excel
  iLCID := GetUserDefaultLCID;

  // create and connect excelapplication
  ExcelApp := TExcelApplication.Create(self);
  ExcelApp.Connect;

  // load file
  ExcelWb := ExcelApp.Workbooks.Open(
    ExtractFilePath(ParamStr(0))+'test.xlsx',
    emptyParam, emptyParam, emptyParam, emptyParam,
    emptyParam, emptyParam, emptyParam, emptyParam,
    emptyParam, emptyParam, emptyParam, emptyParam,
    iLCID
  );

  // Visible - off
  ExcelApp.Application.Visible[0] := false;

  // Starts excel on the first sheet
  ExcelSt := ExcelWb.Sheets[1] as _WorkSheet;
  ExcelSt.Activate(iLCID);

  // Visible - on
  ExcelApp.Application.Visible[0] := true;

  // Connect to the closemethod
  ExcelApp.OnWorkbookBeforeClose := WorkbookBeforeClose;

Replace something

var
  sWhat, sReplacement : String;
  iSheets : Integer;
begin

  // basic data
  sWhat := **STRING 1**;
  sReplacement := **STRING 2**;

  // Warnings off (Overridewarning)
  ExcelApp.Application.DisplayAlerts[iLCID] := False;

  // Replace for all sheets in a workbook
  for iSheets := 1 to ExcelApp.Application.Sheets.Get_Count do
  begin
    ExcelSt := ExcelWb.Sheets[iSheets] as _WorkSheet;
    ExcelSt.Activate(iLCID);

    ExcelApp.Application.ActiveWorkbook.Application.Cells.Replace(
      {What} sWhat,
      {Replacement} sReplacement,
      {LookAt} xlWhole,
      {SearchOrder} xlByRows,
      {MatchCase} False,
      {MatchByte} False
      );

   end;

  // warnings on
  ExcelApp.Application.DisplayAlerts[iLCID] := True;

Save your work

var
  oFileName, oFileFormat : OleVariant;
begin

  {
  Value = Constant in excel (Description)
  ------------------------------------------------------------------------------
  50   = xlExcel12 (Excel Binary Workbook 2007+ with or without macros, xlsb)
  51   = xlOpenXMLWorkbook (without macros Excel 2007+, xlsx)
  52   = xlOpenXMLWorkbookMacroEnabled (with or without macros Excel 2007+, xlsm)
  56   = xlExcel8 (97-2003 Format in Excel 2007-2010, xls)
         !!! in 2003 = xlNormal !!! 
  ------------------------------------------------------------------------------
  }

  oFileName   := 'Filename';
  oFileFormat := 50;
  iLCID       := GetUserDefaultLCID;

  ExcelApp.Application.ActiveWorkbook.SaveAs(
    {Filename} oFileName,
    {FileFormat} oFileFormat,
    {Password} emptyParam,
    {WriteResPassword} emptyParam,
    {ReadOnlyRecommended} false,
    {CreateBackup} false,
    {AccessMode} 0,
    {ConflictResolution} false,
    {AddToMru} false,
    {TextCodepage} false,
    {TextVisualLayout} false,
    {LCID} iLCID
    );

BeforeClose method

 procedure WorkbookBeforeClose(Sender: TObject;
   var Wb, Cancel: OleVariant);
 var
   oTrue :OleVariant;
 begin

   ExcelApp.Application.Visible[0] := false;

   //Here comes your stuff when the user wants to quit excel

   oTrue := true;
   Cancel := oTrue;

   ExcelApp.Quit;
 end;