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);

Automation methods can often take a number of optional parameters. You can pass undef for any unwanted parameters in the arguments list. For example, you can save a WorkBook in Excel with SaveAs . Additional settings allow you to add the WorkBook to the MRU list and create a backup copy:

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

For simplification, you can also use just the named parameters you want to set by passing a reference to a hash containing them. You can do this right in the argument list by creating an anonymous hash reference with {} . The previous example can therefore be written like this:

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

Properties of automation objects are accessed via hash reference notation on the object. For example:

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

Be aware that properties may not be writable (or even readable). Many automation objects have read-only properties and will generate an exception if you try to write to them. You'll need to consult the documentation for the object to find out which properties you can safely set.

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(); }

Count will tell you how many items are in the collection, and Item will return the desired item as a Win32::OLE object.

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);

Now you can use the enumeration methods on the object.

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.

Type	Description
VT_EMPTY	No value specified. Incidentally, automation does not use VT_EMPTY for empty optional parameters. Rather, it uses VT_ERROR with a value of DISP_E_PARAMNOTFOUND (which isn't exported by Perl: the value in current Win32 SDK headers is 0x80020004).
VT_NULL	A propagating NULL value was specified (not to be confused with a null pointer). This is used for things like the NULL in SQL.
VT_I2	A 2-byte integer value.
VT_I4	A 4-byte integer value.
VT_R4	An IEEE 4-byte real value.
VT_R8	An IEEE 8-byte real value.
VT_CY	An automation currency value.
VT_DATE	An automation date value.
VT_BSTR	A string value.
VT_DISPATCH	The value contains another automation object.
VT_ERROR	An error code was specified. The type of the error is determined by the actual value. As mentioned earlier, this is used to implement empty optional parameters.
VT_BOOL	A Boolean (true/false) value. If all bits are 1, it's true, if all bits are 0, it's false. Any other value is invalid.
VT_VARIANT	The value contains another Variant.
VT_UNKNOWN	The value contains an IUnknown pointer (the base class of COM objects).
VT_UI1	An unsigned 1-byte character.
VT_BYREF	Can be combined with some fields to indicate that the data is being passed by reference, rather than by value.
VT_ARRAY	The value contains an OLE SAFEARRAY (this flag is not currently exported by Perl).

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);

For example, to force a string to be interpreted as a date, create a variant object and set it to the VT_DATE type:

$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";

which produces something like:

xlExcel5 = 39

Here's an example using the Load method to return a hash reference populated with the constants and their values (this produces the same output as the previ- ous example, of course):

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

Notice that, in both cases, we're supplying a regular expression for the name of the type library from which we want to import. Win32::OLE::Const searches the registry for matching type libraries and loads the one with the highest version number (you can override this by supplying the version you want). You can also specify the language you'd like. The parameters (for either Load or Win32::OLE::Const ) are the typelib regular expression, the major version number, the minor version number, and the locale (LCID).

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);

Using Load (to get a hash reference for the constants) may be preferable to importing all of the constants into your namespace. Some automation servers pro- vide a large number of constants (the current version of Excel has some 900+), so importing them into your namespace can clutter things considerably.