Excel COM automation, assigning event

[r_1gm]

Hi,

Code: Pascal  [Select][+][-]

1. program open_excel;
2.  
3. {$IFDEF FPC}
4.  {$MODE Delphi}
5. {$ELSE}
6.  {$APPTYPE CONSOLE}
7. {$ENDIF}        
8.  
9.  
10. uses
11.   SysUtils, Variants, ComObj;
12.  
13.  
14. procedure BeforeSave(SaveAsUI : OLEVariant; var Cancel: OLEVariant);
15. begin
16. end;
17.  
18. const
19.   ServerName = 'Excel.Application';
20. var
21.   XLApp     : OLEVariant;
22.   Path      : string;
23.   Password  : string;
24.   Workbook: OLEVariant;
25. begin
26.  
27.   try
28.     XLApp := CreateOleObject(ServerName);
29.  
30.     {Open existing document}
31.     Path:= 'D:/Book.xlsx';
32.  
33.     Workbook:= XLApp.Workbooks.Open(Path);
34.  
35.     Workbook.BeforeSave:=BeforeSave;
36.  
37.   finally
38.  
39.   end;                    
40. end.            
41.  

anybody know how to assign event to excel com object ?

i got error when compiling
open_excel.lpr(35,36) Error: Type is not automatable: "BeforeSave(var OleVariant;OleVariant);"

[rvk]

I don't think that's going to work like that (you can't just assign an event when using automation like that, I think).

If you create a tlb from EXCEL.EXE you see how it can be done.
Install the package LazActiveX in the IDE and after rebuilding create a TLB for EXCEL.EXE.
(Tools > Import Type Library > choose EXCEL.EXE and create)

You see it works with EventSinkInvoke (in Delphi it makes an InvokeEvent).
From there you can call your own OnWorkbookBeforeSave.

Some of it is explained on this page (for Delphi but should be the same for Lazarus):
https://www.gtro.com/delphi/comevents_e.php

From excel_1_9_tlb.pas created by the type library:

Code: Pascal  [Select][+][-]

1. procedure TEvsApplication.EventSinkInvoke(Sender: TObject; DispID: Integer;
2.   const IID: TGUID; LocaleID: Integer; Flags: Word; Params: tagDISPPARAMS;
3.   VarResult, ExcepInfo, ArgErr: Pointer);
4. begin
5.   case DispID of
6.     1610612736: if assigned(OnQueryInterface) then
7.           OnQueryInterface(Self, OleVariant(Params.rgvarg[1]), OleVariant(Params.rgvarg[0]));
8. //...
9.     1570: if assigned(OnWorkbookBeforeClose) then
10.           OnWorkbookBeforeClose(Self, OleVariant(Params.rgvarg[1]), Params.rgvarg[0].pbool^);
11.     1571: if assigned(OnWorkbookBeforeSave) then
12.           OnWorkbookBeforeSave(Self, OleVariant(Params.rgvarg[2]), OleVariant(Params.rgvarg[1]), Params.rgvarg[0].pbool^);
13.     1572: if assigned(OnWorkbookBeforePrint) then
14.           OnWorkbookBeforePrint(Self, OleVariant(Params.rgvarg[1]), Params.rgvarg[0].pbool^);
15. //...

I also see there is a simular topic here: https://forum.lazarus.freepascal.org/index.php?topic=27667.0

[Thaddy]

Correct. Using event is better done using early binding.

[marcov]

There was a post a few weeks back that eventsinks weren't working for openoffice. I haven't had time to dive into that :

https://forum.lazarus.freepascal.org/index.php/topic,68806.msg532528.html#msg532528

[r_1gm]

OMG,

thank you all

I will try to learn it slowly, the topic pretty advance beyond my imagination