$ex = Win32::OLE->new('Excel.Application') or die "oops\n";
$ex->Amethod("arg")->Bmethod->{'Property'} = "foo";
$ex->Cmethod(undef,undef,$Arg3);
$ex->Dmethod($RequiredArg1, {NamedArg1 => $Value1, NamedArg2 => $Value2});
$wd = Win32::OLE->GetObject("D:\\Data\\Message.doc");
$xl = Win32::OLE->GetActiveObject("Excel.Application");
The Win32::OLE module uses the IDispatch interface exclusively. It is not possible to access a custom OLE interface. OLE events and OCX's are currently not supported.
Actually, that's no longer strictly true. This module now contains ALPHA level support for OLE events. This is largely untested and the specific interface might still change in the future.
The PROGID argument must be either the OLE program id or the class id of the required application. The optional DESTRUCTOR specifies a DESTROY-like method. This can be either a CODE reference or a string containing an OLE method name. It can be used to cleanly terminate OLE applications in case the Perl program dies.
To create an object via DCOM on a remote server you can use an array reference in place of PROGID. The referenced array must contain the machine name and the program id or class id. For example:
my $obj = Win32::OLE->new(['my.machine.com', 'Program.Id']);
If the PROGID is a program id then Win32::OLE will try to resolve the corresponding class id locally. If the program id is not registered locally then the remote registry is queried. This will only succeed if the local process has read access to the remote registry. The safest (and fastest) method is to specify the "class id" directly.
$Count = Win32::OLE->EnumAllObjects(sub {
my $Object = shift;
my $Class = Win32::OLE->QueryObjectType($Object);
printf "# Object=%s Class=%s\n", $Object, $Class;
});
The EnumAllObjects() method is primarily a debugging tool. It can be used e.g. in an END block to check if all external connections have been properly destroyed.
Be aware that objects implemented in Visual Basic have a buggy implementation of this functionality: They pretend to be unloadable while they are actually still running their cleanup code. Unloading the DLL at that moment typically produces an access violation. The probability for this problem can be reduced by calling the SpinMessageLoop() method and sleep()ing for a few seconds.
Valid values for COINIT are:
Win32::OLE::COINIT_APARTMENTTHREADED - single threaded Win32::OLE::COINIT_MULTITHREADED - the default Win32::OLE::COINIT_OLEINITIALIZE - single threaded, additional OLE stuff
COINIT_OLEINITIALIZE is sometimes needed when an OLE object uses additional OLE compound document technologies not available from the normal COM subsystem (for example MAPI.Session seems to require it). Both COINIT_OLEINITIALIZE and COINIT_APARTMENTTHREADED create a hidden top level window and a message queue for the Perl process. This may create problems with other application, because Perl normally doesn't process its message queue. This means programs using synchronous communication between applications (such as DDE initiation), may hang until Perl makes another OLE method call/property access or terminates. This applies to InstallShield setups and many things started to shell associations. Please try to utilize the "Win32::OLE->SpinMessageLoop" and "Win32::OLE->Uninitialize" methods if you can not use the default COINIT_MULTITHREADED model.
$Object->Invoke('Invoke', @Args);
The last OLE error is automatically reset by a successful OLE call. The numeric value can also explicitly be set by a call (which will discard the string value):
Win32::OLE->LastError(0);
$Object->{Property} = $OtherObject;
corresponds to this Visual Basic statement:
Set Object.Property = OtherObject
To get the by value treatment of the Visual Basic "Let" statement
Object.Property = OtherObject
you have to use the LetProperty() object method in Perl:
$Object->LetProperty($Property, $OtherObject);
LetProperty() also supports optional arguments for the property assignment. See OBJECT-SetProperty(NAME,ARGS,VALUE)> for details.
my $CP = Win32::OLE->Option('CP');
A single call can be used to set multiple options simultaneously:
Win32::OLE->Option(CP => CP_ACP, Warn => 3);
$Object->{Property} = $Value;
is equivalent to
$Object->SetProperty('Property', $Value);
Arguments must be specified between the property name and the new value:
$Object->SetProperty('Property', @Args, $Value);
It is not possible to use ``named argument'' syntax with this function because the new value must be the last argument to SetProperty().
This method hides any native OLE object method called SetProperty(). The native method will still be available through the Invoke() method:
$Object->Invoke('SetProperty', @Args);
Whenever Perl does not find a method name in the Win32::OLE package it is automatically used as the name of an OLE method and this method call is dispatched to the OLE server.
There is one special hack built into the module: If a method or property name could not be resolved with the OLE object, then the default method of the object is called with the method name as its first parameter. So
my $Sheet = $Worksheets->Table1;
or
my $Sheet = $Worksheets->{Table1};
is resolved as
my $Sheet = $Worksheet->Item('Table1');
provided that the $Worksheets object doesnot have a "Table1" method or property. This hack has been introduced to call the default method of collections which did not name the method in their type library. The recommended way to call the ``unnamed'' default method is:
my $Sheet = $Worksheets->Invoke('', 'Table1');
This special hack is disabled under "use strict 'subs';".
Optional parameters on method calls can be omitted by using "undef" as a placeholder. A better way is to use named arguments, as the order of optional parameters may change in later versions of the OLE server application. Named parameters can be specified in a reference to a hash as the last parameter to a method call.
Properties can be retrieved or set using hash syntax, while methods can be invoked with the usual perl method call syntax. The "keys" and "each" functions can be used to enumerate an object's properties. Beware that a property is not always writable or even readable (sometimes raising exceptions when read while being undefined).
If a method or property returns an embedded OLE object, method and property access can be chained as shown in the examples below.
if (Win32::OLE->LastError == HRESULT(0x80020003)) {
# your error recovery here
}
foreach my $value (in $collection) {
# do something with $value here
}
my $RefOf = $Object;
my $ValOf = valof $Object;
$Object->{Value} = $NewValue;
Now $ValOf still contains the old value wheras $RefOf would resolve to the $NewValue because it is still a reference to $Object.
The valof() function can also be used to convert Win32::OLE::Variant objects to Perl values.
use Win32::OLE qw(in valof with OVERLOAD);
You can still get the original string representation of an object ("Win32::OLE=0xDEADBEEF"), e.g. for debugging, by using the "overload::StrVal()" method:
print overload::StrVal($object), "\n";
Please note that "OVERLOAD" is a global setting. If any module enables Win32::OLE overloading then it's active everywhere.
use Win32::OLE qw(EVENTS);
which implicitly does something like:
use Win32::OLE;
Win32::OLE->Initialize(Win32::OLE::COINIT_OLEINITIALIZE);
The current interface to OLE events should be considered experimental and is subject to change. It works as expected for normal OLE applications, but OLE control events often don't seem to work yet.
Events must be enabled explicitly for an OLE object through the Win32::OLE->WithEvents() class method. The Win32::OLE module uses the IProvideClassInfo2 interface to determine the default event source of the object. If this interface is not supported, then the user must specify the name of the event source explicitly in the WithEvents() method call. It is also possible to specify the class name of the object as the third parameter. In this case Win32::OLE will try to look up the default source interface for this COCLASS.
The HANDLER argument to Win32::OLE->WithEvents() can either be a CODE reference or a package name. In the first case, all events will invoke this particular function. The first two arguments to this function will be the OBJECT itself and the name of the event. The remaining arguments will be event specific.
sub Event {
my ($Obj,$Event,@Args) = @_;
print "Event triggered: '$Event'\n";
}
Win32::OLE->WithEvents($Obj, \&Event);
Alternatively the HANDLER argument can specify a package name. When the OBJECT fires an event, Win32::OLE will try to find a function of the same name as the event in this package. This function will be called with the OBJECT as the first argument followed again by the event specific parameters:
package MyEvents;
sub EventName1 {
my ($Obj,@Args) = @_;
print "EventName1 event triggered\n";
}
package main;
Win32::OLE->WithEvents($Obj, 'MyEvents', 'IEventInterface');
If Win32::OLE doesn't find a function with the name of the event then nothing happens.
Event parameters passed by reference are handled specially. They are not converted to the corresponding Perl datatype but passed as Win32::OLE::Variant objects. You can assign a new value to these objects with the help of the Put() method. This value will be passed back to the object when the event function returns:
package MyEvents;
sub BeforeClose {
my ($self,$Cancel) = @_;
$Cancel->Put(1) unless $MayClose;
}
Direct assignment to $Cancel would have no effect on the original value and would therefore not command the object to abort the closing action.
0 Ignore error, return undef
1 Carp::carp if $^W is set (-w option)
2 always Carp::carp
3 Carp::croak
The error number and message (without Carp line/module info) are available through the "Win32::OLE-"LastError> class method.
Alternatively the Warn option can be set to a CODE reference. E.g.
Win32::OLE->Option(Warn => 3);
is equivalent to
Win32::OLE->Option(Warn => \&Carp::croak);
This can even be used to emulate the VisualBasic "On Error Goto Label" construct:
Win32::OLE->Option(Warn => sub {goto CheckError});
# ... your normal OLE code here ...
CheckError:
# ... your error handling code here ...
Win32::OLE->Option(_NewEnum => 1);
# ...
my @sheets = @{$Excel->Worksheets->{_NewEnum}};
In normal application code, this would be better written as:
use Win32::OLE qw(in);
# ...
my @sheets = in $Excel->Worksheets;
Using a unique proxy makes life easier for tree traversal algorithms to recognize they already visited a particular node. This option comes at a price: Win32::OLE has to maintain a global hash of all outstanding objects and their corresponding proxies. Identity checks on COM objects can also be expensive if the objects reside out-of-process or even on a different computer. Therefore this option is off by default unless the program is being run in the debugger.
Unfortunately, this option doesn't always help. Some programs will return new COM objects for even the same property when asked for it multiple times (especially for collections). In this case, there is nothing Win32::OLE can do to detect that these objects are in fact identical (because they aren't at the COM level).
The "_Unique" option can be set to either 1 (enabled) or 0 (disabled, default).
use Win32::OLE;
# use existing instance if Excel is already running
eval {$ex = Win32::OLE->GetActiveObject('Excel.Application')};
die "Excel not installed" if $@;
unless (defined $ex) {
$ex = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;})
or die "Oops, cannot start Excel";
}
# get a new workbook
$book = $ex->Workbooks->Add;
# write to a particular cell
$sheet = $book->Worksheets(1);
$sheet->Cells(1,1)->{Value} = "foo";
# write a 2 rows by 3 columns range
$sheet->Range("A8:C9")->{Value} = [[ undef, 'Xyzzy', 'Plugh' ],
[ 42, 'Perl', 3.1415 ]];
# print "XyzzyPerl"
$array = $sheet->Range("A8:C9")->{Value};
for (@$array) {
for (@$_) {
print defined($_) ? "$_|" : "<undef>|";
}
print "\n";
}
# save and exit
$book->SaveAs( 'test.xls' );
undef $book;
undef $ex;
Please note the destructor specified on the Win32::OLE->new method. It ensures that Excel will shutdown properly even if the Perl program dies. Otherwise there could be a process leak if your application dies after having opened an OLE instance of Excel. It is the responsibility of the module user to make sure that all OLE objects are cleaned up properly!
Here is an example of using Variant data types.
use Win32::OLE;
use Win32::OLE::Variant;
$ex = Win32::OLE->new('Excel.Application', \&OleQuit) or die "oops\n";
$ex->{Visible} = 1;
$ex->Workbooks->Add;
# should generate a warning under -w
$ovR8 = Variant(VT_R8, "3 is a good number");
$ex->Range("A1")->{Value} = $ovR8;
$ex->Range("A2")->{Value} = Variant(VT_DATE, 'Jan 1,1970');
sub OleQuit {
my $self = shift;
$self->Quit;
}
The above will put value ``3'' in cell A1 rather than the string ``3 is a good number''. Cell A2 will contain the date.
Similarly, to invoke a method with some binary data, you can do the following:
$obj->Method( Variant(VT_UI1, "foo\000b\001a\002r") );
Here is a wrapper class that basically delegates everything but new() and DESTROY(). The wrapper class shown here is another way to properly shut down connections if your application is liable to die without proper cleanup. Your own wrappers will probably do something more specific to the particular OLE object you may be dealing with, like overriding the methods that you may wish to enhance with your own.
package Excel;
use Win32::OLE;
sub new {
my $s = {};
if ($s->{Ex} = Win32::OLE->new('Excel.Application')) {
return bless $s, shift;
}
return undef;
}
sub DESTROY {
my $s = shift;
if (exists $s->{Ex}) {
print "# closing connection\n";
$s->{Ex}->Quit;
return undef;
}
}
sub AUTOLOAD {
my $s = shift;
$AUTOLOAD =~ s/^.*:://;
$s->{Ex}->$AUTOLOAD(@_);
}
1;
The above module can be used just like Win32::OLE, except that it takes care of closing connections in case of abnormal exits. Note that the effect of this specific example can be easier accomplished using the optional destructor argument of Win32::OLE::new:
my $Excel = Win32::OLE->new('Excel.Application', sub {$_[0]->Quit;});
Note that the delegation shown in the earlier example is not the same as true subclassing with respect to further inheritance of method calls in your specialized object. See perlobj, perltoot and perlbot for details. True subclassing (available by setting @ISA) is also feasible, as the following example demonstrates:
#
# Add error reporting to Win32::OLE
#
package Win32::OLE::Strict;
use Carp;
use Win32::OLE;
use strict qw(vars);
use vars qw($AUTOLOAD @ISA);
@ISA = qw(Win32::OLE);
sub AUTOLOAD {
my $obj = shift;
$AUTOLOAD =~ s/^.*:://;
my $meth = $AUTOLOAD;
$AUTOLOAD = "SUPER::" . $AUTOLOAD;
my $retval = $obj->$AUTOLOAD(@_);
unless (defined($retval) || $AUTOLOAD eq 'DESTROY') {
my $err = Win32::OLE::LastError();
croak(sprintf("$meth returned OLE error 0x%08x",$err))
if $err;
}
return $retval;
}
1;
This package inherits the constructor new() from the Win32::OLE package. It is important to note that you cannot later rebless a Win32::OLE object as some information about the package is cached by the object. Always invoke the new() constructor through the right package!
Here's how the above class will be used:
use Win32::OLE::Strict;
my $Excel = Win32::OLE::Strict->new('Excel.Application', 'Quit');
my $Books = $Excel->Workbooks;
$Books->UnknownMethod(42);
In the sample above the call to UnknownMethod() will be caught with
UnknownMethod returned OLE error 0x80020009 at test.pl line 5
because the Workbooks object inherits the class "Win32::OLE::Strict" from the $Excel object.
The class names are not always identical to the method/property names producing the object. E.g. the "Workbook" method returns an object of type "Workbook" in Office 95 and "_Workbook" in Office 97.
$Word->WordBasic->FileSaveAs($file);
The problem seems to be fixed by applying the Office 97 Service Release 1.
$Object->Invoke('Dispatch', @AdditionalArgs);
The same is true for names exported by the Exporter or the Dynaloader modules, e.g.: "export", "export_to_level", "import", "_push_tags", "export_tags", "export_ok_tags", "export_fail", "require_version", "dl_load_flags", "croak", "bootstrap", "dl_findfile", "dl_expandspec", "dl_find_symbol_anywhere", "dl_load_file", "dl_find_symbol", "dl_undef_symbols", "dl_install_xsub" and "dl_error".
Gurusamy Sarathy <gsar@activestate.com> subsequently fixed several major bugs, memory leaks, and reliability problems, along with some redesign of the code.
Jan Dubois <jand@activestate.com> pitched in with yet more massive redesign, added support for named parameters, and other significant enhancements. He's been hacking on it ever since.
Please send questions about problems with this module to the Perl-Win32-Users mailinglist at ActiveState.com. The mailinglist charter requests that you put an [OLE] tag somewhere on the subject line (for OLE related questions only, of course).
(c) 1995 Microsoft Corporation. All rights reserved.
Developed by ActiveWare Internet Corp., now known as
ActiveState Tool Corp., http://www.ActiveState.com
Other modifications Copyright (c) 1997-2000 by Gurusamy Sarathy
<gsar@activestate.com> and Jan Dubois <jand@activestate.com>
You may distribute under the terms of either the GNU General Public
License or the Artistic License, as specified in the README file.