Excel event handling

[jnash67]

I'm trying to figure out how to react to Excel events in an AutoIt script. Here's where I've gotten so far. The COM reference page uses IE as an example:

$EventObject=ObjEvent($oIE,"IEEvent_","DWebBrowserEvents")

You then have event handler functions beginning with the IEEvent specified in the ObjEvent call like:

Func IEEvent_BeforeNavigate($URL, $Flags, $TargetFrameName, $PostData, $Headers, $Cancel)

I want to do the same for Excel. According to MSDN (http://msdn.microsoft.com/en-us/library/aa768309%28VS.85%29.aspx), DWebBrowserEvents is an "event sink interface" that the application must implement to receive event notifications. For Excel the interface might be IConnectionPoint or IDispatch.

The following links show how it should work from C# but haven't been able to adapt it to AutoIt -

http://support.microsoft.com/kb/309301

http://blogs.msdn.com/b/gabhan_berry/archive/2008/02/13/c-excel-addin-steps-2-and-3-understanding-the-ribbon-and-event-trapping.aspx

There's also some role for a UUID or IID as follows:

//00024413-0000-0000-C000-000000000046
const IID IID_ApplicationEvents  = 
{0x00024413,0x0000,0x0000,{0xc0,0x00,0x00,0x00,0x00,0x00,0x00,0x46}};

Can someone please show an example of AutoIt responding to a couple of Excel events, especially the Workbook_BeforeClose event?

[seandisanti]

i tried and this is as close as i came...

One thing that may warrant some investigation; if i do "AppEvents" i get an error that the library is not registered, but if i do _AppEvents i get error 80040200 I can play with this some more again tomorrow, but calling it a night tonight i think

expandcollapse popup

#RequireAdmin
$oEX = ObjCreate("excel.Application")
$oEX.Visible = True
$oWB = $oEX.Workbooks.Add()

Global $g_eventerror = 0    ; to be checked to know if com error occurs. Must be reset after handling.

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler
$EventObject=ObjEvent($oEX,"ExEvent_","_AppEvents")
If $g_eventerror then
    $g_eventerror = 0
  Msgbox (0,"AutoItCOM test","Test passed: We got an error number: " & @error)
Else
  Msgbox (0,"AutoItCOM test","Test failed!")
Endif

Exit

; This is my custom defined error handler
Func MyErrFunc()

  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )

    Local $err = $oMyError.number
    If $err = 0 Then $err = -1

    $g_eventerror = $err  ; to check for after this function returns
Endfunc

If @error Then MsgBox(0,"error",@error)
While 1
    Sleep(10)
WEnd
Func ExEvent_WorkbookBeforeClose($Cancel = 1)
    MsgBox(0,"Nope","Not done here yet")
EndFunc

[jnash67]

Thanks. Please do play around some more and I will do the same.

i tried and this is as close as i came...

One thing that may warrant some investigation; if i do "AppEvents" i get an error that the library is not registered, but if i do _AppEvents i get error 80040200 I can play with this some more again tomorrow, but calling it a night tonight i think

expandcollapse popup

#RequireAdmin
$oEX = ObjCreate("excel.Application")
$oEX.Visible = True
$oWB = $oEX.Workbooks.Add()

Global $g_eventerror = 0    ; to be checked to know if com error occurs. Must be reset after handling.

$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")    ; Initialize a COM error handler
$EventObject=ObjEvent($oEX,"ExEvent_","_AppEvents")
If $g_eventerror then
    $g_eventerror = 0
  Msgbox (0,"AutoItCOM test","Test passed: We got an error number: " & @error)
Else
  Msgbox (0,"AutoItCOM test","Test failed!")
Endif

Exit

; This is my custom defined error handler
Func MyErrFunc()

  Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $oMyError.description    & @CRLF & _
             "err.windescription:"     & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: "         & @TAB & hex($oMyError.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $oMyError.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $oMyError.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $oMyError.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $oMyError.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $oMyError.helpcontext _
            )

    Local $err = $oMyError.number
    If $err = 0 Then $err = -1

    $g_eventerror = $err  ; to check for after this function returns
Endfunc

If @error Then MsgBox(0,"error",@error)
While 1
    Sleep(10)
WEnd
Func ExEvent_WorkbookBeforeClose($Cancel = 1)
    MsgBox(0,"Nope","Not done here yet")
EndFunc

[PsaltyDS]

Try the generic event (no event name appended):

$EventObject=ObjEvent($oEX,"ExEvent_")

; ...

Func ExEvent_($sEventName)
    MsgBox(64, "Excel Event","Event name = " & $sEventName)
EndFunc

Once you know you are getting events and the event names, you can look up the specific parameters.

[seandisanti]

Once you know you are getting events and the event names, you can look up the specific parameters.

that generates the same error the 80040200

[jnash67]

Try the generic event (no event name appended):

$EventObject=ObjEvent($oEX,"ExEvent_")

; ...

Func ExEvent_($sEventName)
    MsgBox(64, "Excel Event","Event name = " & $sEventName)
EndFunc

Once you know you are getting events and the event names, you can look up the specific parameters.

I tried this but the message box never shows.

[PsaltyDS]

Any different if you do the same generic function with $EventObject = ObjEvent($oEX,"ExEvent_", "_AppEvents") from cameronsdad?

[VeryDxZ]

Hey guys, what you've been discussing is exactly what I'm looking for!

Is there any progress? If you've successfully done it, please give some clue... Thanks!

[pulido]

First of all, sorry for necroing this post, but I'm facing a similar problem and I haven't been able to find a solution, nor a place with additional information about this.

My problem is basically the same: I want to listen to Excel events from my autoit script. I have used GetObj/CreateObj and ObjEvent, but in my call:

$oExcel = ObjGet("","Excel.Application")
$oExcel.workbooks.add
...
$ExcelSinkObject=ObjEvent($oExcel,"EEV_","AppEvents") ;
...
Func EEV_BeforeWorkbookSave($Wb,$SaveAsUI,$Cancel)
MsgBox(0,"test","save!");
EndFunc

I've tried everything you said as third parameter of ObjEvent:

- No third parameter -> 80040200h

- "AppEvents" -> "Not registered library" (8002801Dh)

- "AppEvents_"-> 80040002h (Non compatible Interface)

- "_AppEvents"-> 80040200h

- any random string -> 80040200h (Non compatible Interface)

After searching information and trying for hours, I'm starting to wonder if this is even possible. I'm not an expert here, but I fail to find any example of what I want to do in the internet.

Any help would be appreciated. Thanks in advance.

[Eemuli]

[EDIT] I updated AutoIt to version 3.3.8.0 and events started working through ObjEvent().

I found a way to interact with Excel events. This one doesnt use the ObjEvent() method though, but it works very well.

IDEA

Excel has Visual Basic script attached to workbook which can do various of things. I found out that it can catch events that occur in Excel.

So the idea is to use Excels VB script to communicate back to AutoIt when event occurs. The prosess is basically following:

1. Create Mailslot for AutoIt.

2. Use AutoIt to write VB script to Excel workbook. When event occurs in Excel, VB script sends info to mailslot.

3. Check mailslot and act according to event which occured.

CODE

I created nice wrappers for you which you can easily use to interact with Excel events.

Here are the main functions. Include this into your script.

Please note that you will have to have Mailslot.au3 UDF in your script directory. You can get it from here: ()

ExcelEvent.au3

Here is example how to use it.

ExcelEventExample.au3

SYNTAX

ExcelEvents.au3 include basically two usable functions

• _ExcelEvent($oExcel, $eventName, $functionName [, $checktime = 250]) and
• _ExcelUnEvent($eventName, $functionName).

With _ExcelEvent you can attach your own function to be executed every time some spesific event occurs.

With _ExcelUnEvent you can deattach your function from being executed when event occurs.

Edited January 1, 2012 by Eemuli

[trancexx]

Events work just fine. All I see in this thread is bad or wrong code, nothing else.

[Eemuli]

Events work just fine. All I see in this thread is bad or wrong code, nothing else.

Maybe you could help us with these Excel events. The problem seems to be that we dont know the correct interface.

Here is the code which you can use:

#include <Excel.au3>

Global $oMyError = ObjEvent("AutoIt.Error", "MyErrFunc")
$oExcel = ObjGet("","Excel.Application")
Global $oExcelEvent = ObjEvent($oExcel, "_eventHandler_", "_AppEvents")  ; What is the correct interface?

Func _eventHandler_($event)
ConsoleWrite("event: " & $event & @LF)
EndFunc

While 1
ConsoleWrite("loop" & @LF)
Sleep(1000)
WEnd

Func MyErrFunc()
ConsoleWrite("We intercepted a COM Error !" & @CRLF & @CRLF & _
   "err.description is: " & @TAB & $oMyError.description & @CRLF & _
   "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
   "err.number is: " & @TAB & Hex($oMyError.number, 8) & @CRLF & _
   "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
   "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
   "err.source is: " & @TAB & $oMyError.source & @CRLF & _
   "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
   "err.helpcontext is: " & @TAB & $oMyError.helpcontext & @CRLF)
Local $err = $oMyError.number
If $err = 0 Then $err = -1
EndFunc   ;==>MyErrFunc

[trancexx]

Something like this should work (based on code from this thread):

Global $oExcel = ...
Global $oExcelEvent = ObjEvent($oExcel, "_eventHandler_") ; , "AppEvents")

;... Whatever here



Func _eventHandler_WorkbookBeforeClose($Cancel)
    MsgBox(0, "Nope", "Not done here yet")
EndFunc   

Func _eventHandler_WorkbookBeforeSave($Wb, $SaveAsUI, $Cancel)
    MsgBox(0, "test", "save!")
EndFunc

[Eemuli]

Something like this should work (based on code from this thread):

Global $oExcel = ...
Global $oExcelEvent = ObjEvent($oExcel, "_eventHandler_") ; , "AppEvents")

;... Whatever here



Func _eventHandler_WorkbookBeforeClose($Cancel)
    MsgBox(0, "Nope", "Not done here yet")
EndFunc  

Func _eventHandler_WorkbookBeforeSave($Wb, $SaveAsUI, $Cancel)
    MsgBox(0, "test", "save!")
EndFunc

Yes. It should, but it doesnt. Did the code work for you?

[trancexx]

Yes. It should, but it doesnt. Did the code work for you?

I don't have Office.

The version of AutoIt you use? (Please give me smart answer.)

[Eemuli]

The version of AutoIt you use? (Please give me smart answer.)

I was using version v3.3.6.0 and found out that there is newer release. So I updated to v3.3.8.0.

After update I started to get events and ObjEvent() didnt trigger error anymore. (Damn, I should have checked the updates in the first place )

After some checking I also noticed that some events should be written without the "Workbook" in the function name.

Func _eventHandler_SheetChange($Sh, $Target)
    ConsoleWrite("Sheet Change !" & @LF)
EndFunc

Func _eventHandler_WorkbookNewSheet()
    ConsoleWrite("New Sheet !" & @LF)
EndFunc

Also some events like SheetActivate only occurs randomly when you change the sheet. Hmmmmm...

I guess using SheetDeactivate will have to do it.

But anyway, Thank you for getting me back on the track !

[trancexx]

After some checking I also noticed that some events should be written without the "Workbook" in the function name.

Func _eventHandler_SheetChange($Sh, $Target)
    ConsoleWrite("Sheet Change !" & @LF)
EndFunc

Func _eventHandler_WorkbookNewSheet()
    ConsoleWrite("New Sheet !" & @LF)
EndFunc

If there are no examples available on the net then the easiest and the smartest way of writing your code would include examination of the interface of the even object. First you would have to find where the definition for that interface is. You get that using ObjName function with second parameter set to number 4. Then open that file with some super cool tool that would list all the secrets hidden inside.

It could be coincidence, but I have written such tool myself - go figure. It's in the examples forum having it's own thread named TLB Viewer or something like that. Then you will know all the right names for the functions, parameters,... everything.

[Eemuli]

If there are no examples available on the net then the easiest and the smartest way of writing your code would include examination of the interface of the even object. First you would have to find where the definition for that interface is. You get that using ObjName function with second parameter set to number 4. Then open that file with some super cool tool that would list all the secrets hidden inside.

It could be coincidence, but I have written such tool myself - go figure. It's in the examples forum having it's own thread named TLB Viewer or something like that. Then you will know all the right names for the functions, parameters,... everything.

Less experimenting and more knowledge This is very useful tool.

Here is the link for everyone =>