19.18.1 Creating Objects

Automation objects are represented in Perl as instances of Win32::OLE objects. The module provides three constructors for creating objects from a registered automation server.

• new
• GetActiveObject
• GetObject

19.18.2 Automation Methods and Properties

Once you have created an automation object, you can use its methods or adjust its properties as you require. Automation methods are implemented as you'd expect with the Perl object syntax:

$obj->some_method(args);

$xl->WorkBooks(1)->SaveAs($f, undef, undef, undef, undef, 1, undef, undef, 1);

$xl->WorkBooks(1)->SaveAs($f, {AddtoMru => 1, CreateBackup => 1});

$val = $obj->{"property"};         # get a property value
$obj->{"property"} = $val;         # set a property value

You can enumerate the properties of an automation object using the normal methods for enumerating hashes, which are keys and each. Here's how you can print the properties and values contained within an object:

$xl = Win32::OLE->new('Excel.Application', 'Quit');
while( ($key,$value) = each %$xl ) {
    print "$key=$value\n";
}

19.18.3 Win32::OLE::Enum

The Win32::OLE::Enum module provides special support for collections. Collections are special automation data types that contain an array of objects or data. A collection supports enumeration - you can iterate through each item through a standard interface.

Collection objects should always provide a Count property (the number of items in the collection) and an Item method. The Item method is used to access a particular collection item using a subscript, which may be an integer or a string, depending on the server. Collection objects may also optionally contain an Add and a Remove method.

Collection objects also support a standard COM interface (IEnumVARIANT) that allows you to enumerate each item in a collection. It defines methods that let you advance the iteration to the next item, skip a given item, restart the enumeration, and create a new copy of the iterator. While all servers are supposed to provide this interface, some servers don't implement all of the methods (often Reset and Clone).

Win32::OLE::Enum defines these methods for enumerating collections. The collection object should provide the Count and Item methods, which are often all you need to use on collections. For example:

$cnt = $coll->Count();
if( $cnt) {
    $obj = $coll->Item(0);
    $obj->do_something();
}

For the enumeration methods, you need to create an enumeration object for the collection object:

$coll = $obj->some_coll();
$enum = Win32::OLE::Enum->new($coll);

19.18.4 Win32::OLE::Variant

All automation data has to be coerced into a special type called a Variant. Most of the time, you don't need to worry about explicit type coercion. You just provide your scalar data and the magic of automation takes care of the rest of it. How- ever, there are cases when you want to control the exact type of data you're send- ing to the automation server. The Win32::OLE::Variant module provides access to the Variant data type and lets you control exactly how the data is represented.

A Variant is an OLE data structure that contains a type field and a data field. The flags are implemented in Perl (as are many constants) as subroutines that return an integer value. The table below lists the Variant type flags, along with a brief description of each.

To convert data to a specific variant type, you create a variant object with either the new constructor method or the convenience function Variant:

$vnt = Win32::OLE::Variant->new(type, data);
$vnt = Variant(type, data);

$dt = Variant(VT_DATE, "August 24, 1970");   # create an explicit data type
$sheet->Cells(1,1)->{Value} = $dt;           # set it to a spreadsheet cell

19.18.5 Win32::OLE::Const

While browsing through the documentation for an automation object, you may have come across references to constant values. For example, if you're trying to save an Excel workbook to a different file format, you need to provide a file for- mat constant. Since the server documentation typically provides symbolic con- stants (e.g., xlExcel5 or xlTemplate), we need a way to access those from Perl. This is the purpose of Win32::OLE::Const, which imports the constants from an automation object into your script.

You can either import the constants directly into your namespace as subs that return the constant value, or you can have them returned as a hash reference with the constant name as the key and its value as the value. Here's an example of the former:

use Win32::OLE::Const ("Microsoft Excel");
print "xlExcel5 = ", xlExcel5, "\n";

xlExcel5 = 39

use Win32::OLE::Const;

my $constants = Win32::OLE::Const->Load("Microsoft Excel");
print "xlExcel5 = $constants->{xlExcel5}\n";

You can also provide the Load method with an automation object, which is then queried for its type library. Interestingly, the documentation notes that this seems to be slower than searching the Registry (though neither is really speedy with a large automation server like Excel). Here's an example of that:

use Win32::OLE;
use Win32::OLE::Const;

# create an Excel application object
my $xl = Win32::OLE->new('Excel.Application', 'Quit') ||
  die "Can't create Excel: ", Win32::OLE->LastError;

# import the constants from it 
my $constants = Win32::OLE::Const->Load($xl);