Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Re^2: Excel events handling

by olivierp (Hermit)
on Dec 23, 2004 at 14:21 UTC ( #417092=note: print w/replies, xml ) Need Help??

in reply to Re: Excel events handling
in thread Excel events handling

I did find it, and updated it somewhat.
This has been tested against Excel 2002 sp3, on XP
Note that this script will leave dangling Excel processes in memory if given the chance, and will not end if you manually close Excel while it is running.
As usual with these things, ActiveState's OLE browser comes in handy, as well as the documentation for Excel's Object model. In particular for identifying the arguments your event handler(s) can potentially receive.
use strict; use sigtrap qw(die INT); use Win32::OLE qw(in EVENTS); use Win32::OLE::Variant; $| = 1; # Without this, you end up registering the same event multiple times.. +. # However, check the docs, as there is a performance penalty... Win32::OLE->Option(_Unique => 1); # Excel 10 "Dispatch" interfaces for Win32::OLE->WithEvents # Application: AppEvents # Sheets: DocEvents # Charts: ChartEvents # Workbooks: WorkbookEvents # OLE: OLEObjectEvents sub kill_excel { my $self = shift; $self->Quit; } END { my $Count = Win32::OLE->EnumAllObjects(sub { my $Object = shift; my $Class = Win32::OLE->QueryObjectType($Object); printf "# Object=%s Class=%s\n", $Object, $Class; }); print $Count,$/; } my $xl_app = Win32::OLE->new('Excel.Application', \&kill_excel); $xl_app->{Visible} = 1; my %wkb; my %sheets; sub get_ole_type { return Win32::OLE->QueryObjectType(shift); } sub app_event_handler { my ($obj, $event, @args) = @_; #print "app_event_handler: ", get_ole_type($obj), " ($obj->{Name}) : + Event: $event",$/; my $new_item = $args[0]; if ($event eq'NewWorkbook'|| $event eq 'WorkbookActivate' ){ print "app_event_handler: ", get_ole_type($obj), " ($obj->{Name}) +: Event: <$event>",$/; print $new_item->{BuiltinDocumentProperties}->{"Creation Date"}; print $new_item, ":",join("<->", keys %wkb), $/; unless ($wkb{$new_item}){ my $w = $new_item; print "Registering event handler for Workbook: $w->{Name}",$/; Win32::OLE->WithEvents($w, \&wb_event_handler, 'WorkbookEvents') +; $wkb{$w} = $new_item; for my $s (in $w->Worksheets()) { $sheets{$s} = $s unless $sheets{$s}; print "Registering event handler for sheet $s->{Name} in book $s-> +{Parent}->{Name}",$/; Win32::OLE->WithEvents($s, \&sheet_event_handler, 'DocEvents'); } } } } Win32::OLE->WithEvents($xl_app, \&app_event_handler, 'AppEvents'); sub sheet_event_handler { my ($obj, $event, @args) = @_; print "sheet_event_handler: ",get_ole_type($obj), " ($obj->{Name}) : + Event: <$event>",$/; if ($event eq 'SelectionChange' || $event eq 'Change') { my $range = $args[0]; print "$event received with " . get_ole_type($range) . " as argume +nt",$/; print <<EOF; The target range has: $range->{Columns}->{Count} column(s) and $range- +>{Rows}->{Count} row(s) It spans cell(s) $range->{Address} EOF } if ($event eq 'Change') { for my $cell ( in $args[0]->{Cells}) { print "Cell $cell->{Address} contains: $cell->{Value}",$/; } } } sub wb_event_handler { my ($obj, $event, @args) = @_; print "wb_event_handler: ",get_ole_type($obj), " ($obj->{Name}) : Ev +ent: <$event>",$/; } while (1) { $xl_app->SpinMessageLoop(); }

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://417092]
[erix]: hippo I think the 26 downvotes sick
[erix]: eh, 29 downvotes
[erix]: but I guess there's no stopping the herd
[1nickt]: Thank you haukex and hippo. I'm thinking of using IPC::System:: Simple::capture instead. But will go reread Hauke's treatise first :-)
[1nickt]: herveus is so weird. Love his reply in that SQLite thread! Much more effective warning system to have diverse monks debunk on technical merits...
[haukex]: IPC::System:: Simple does all the $? handling for you, and does it well
[erix]: weird problem btw (that SQLite question) - I asked astroboy to let us know if he finds the cause
[hippo]: The syntax is warning enough to me.

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (9)
As of 2017-12-13 11:40 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (359 votes). Check out past polls.