#10 - Win32::OLE by Jan Dubois

- NAME
- INTRODUCTION
- THE OLE MINDSET
- WORKING WITH WIN32::OLE

- THE FIRST STEP: CREATING AN OLE SERVER OBJECT
- METHOD CALLS
- PROPERTIES
- SAMPLE APPLICATION
- DOWNLOADING A WEB PAGE WITH LWP
- MICROSOFT ACCESS
- MICROSOFT EXCEL
- ACTIVEX DATA OBJECTS
- LOTUS NOTES

- VARIANTS
- FURTHER INFORMATION

- DOCUMENTATION AND EXAMPLE CODE
- OLE AUTOMATION ON OTHER PLATFORMS

- COPYRIGHT

# NAME

The Perl Journal #10 - Win32::OLE by Jan Dubois

# INTRODUCTION

Suppose you're composing a document with Microsoft Word. You want to include an Excel spreadsheet. You could save the spreadsheet in some image format that Word can understand, and import it into your document. But if the spreadsheet changes, your document will be out of date.

Microsoft's OLE (Object Linking and Embedding, pronounced ``olay'') lets one program use objects from another. In the above scenario, the spreadsheet is the object. As long as Excel makes that spreadsheet available as an OLE object, and Word knows to treat it like one, your document will always be current.

You can control OLE objects from Perl with the Win32::OLE module, and that's what this article is about. First, I'll show you how to ``think OLE,'' which mostly involves a lot of jargon. Next, I'll show you the mechanics involved in using Win32::OLE. Then we'll go through a Perl program that uses OLE to manipulate Microsoft Excel, Microsoft Access, and Lotus Notes. Finally, I'll talk about Variants, an internal OLE data type.

# THE OLE MINDSET

When an application makes an OLE object available for other applications to use, that's called OLE automation. The program using the object is called the controller, and the application providing the object is called the server. OLE automation is guided by the OLE Component Object Model (COM) which specifies how those objects must behave if they are to be used by other processes and machines.

There are two different types of OLE automation servers. In-process servers are implemented as dynamic link libraries (DLLs) and run in the same process space as the controller. Out-of-process servers are more interesting; they are standalone executables that exist as separate processes - possibly on a different computer.

The Win32::OLE module lets your Perl program act as an OLE controller. It allows Perl to be used in place of other languages like Visual Basic or Java to control OLE objects. This makes all OLE automation servers immediately available as Perl modules.

Don't confuse ActiveState OLE with Win32::OLE. ActiveState OLE is completely different, although future builds of ActiveState Perl (500 and up) will work with Win32::OLE.

Objects can expose OLE methods, properties, and events to the outside world. Methods are functions that the controller can call to make the object do something; properties describe the state of the object; and events let the controller know about external events affecting the object, such as the user clicking on a button. Since events involve asynchronous communication with their objects, they require either threads or an event loop. They are not yet supported by the Win32::OLE module, and for the same reason ActiveX controls (OCXs) are currently unsupported as well.

# WORKING WITH WIN32::OLE

The Win32::OLE module doesn't let your Perl program create OLE objects. What it does do is let your Perl program act like a remote control for other applications-it lets your program be an OLE controller. You can take an OLE object from another application (Access, Notes, Excel, or anything else that speaks OLE) and invoke its methods or manipulate its properties.

## THE FIRST STEP: CREATING AN OLE SERVER OBJECT

First, we need to create a Perl object to represent the OLE server. This is a weird idea; what it amounts to is that if we want to control OLE objects produced by, say, Excel, we have to create a Perl object that represents Excel. So even though our program is an OLE controller, it'll contain objects that represent OLE servers.

You can create a new OLE server object with `Win32::OLE->new`. This takes a program ID (a human readable string like `'Speech.VoiceText'`) and returns a server object:

      my $server = Win32::OLE->new('Excel.Application', 'Quit');

Some server objects (particularly those for Microsoft Office applications) don't automatically terminate when your program no longer needs them. They need some kind of Quit method, and that's just what our second argument is. It can be either a code reference or a method name to be invoked when the object is destroyed. This lets you ensure that objects will be properly cleaned up even when the Perl program dies abnormally.

To access a server object on a different computer, replace the first argument with a reference to a list of the server name and program ID:

      my $server = Win32::OLE->new(['foo.bar.com',
                                    'Excel.Application']);

(To get the requisite permissions, you'll need to configure your security settings with DCOMCNFG.EXE.)

You can also directly attach your program to an already running OLE server:

      my $server = Win32::OLE->GetActiveObject('Excel.Application');

This fails (returning `undef`) if no server exists, or if the server refuses the connection for some reason. It is also possible to use a persistent object moniker (usually a filename) to start the associated server and load the object into memory:

      my $doc = Win32::OLE->GetObject("MyDocument.Doc");

## METHOD CALLS

Once you've created one of these server objects, you need to call its methods to make the OLE objects sing and dance. OLE methods are invoked just like normal Perl object methods:

      $server->Foo(@Arguments);

This is a Perl method call - but it also triggers an OLE method call in the object. After your program executes this statement, the `$server` object will execute its `Foo()` method. The available methods are typically documented in the application's object model.

Parameters. By default, all parameters are positional (e.g. `foo($first, $second, $third)`) rather than named (e.g. `foo(-name => "Yogi", -title => "Coach")`). The required parameters come first, followed by the optional parameters; if you need to provide a dummy value for an optional parameter, use undef.

Positional parameters get cumbersome if a method takes a lot of them. You can use named arguments instead if you go to a little extra trouble - when the last argument is a reference to a hash, the key/value pairs of the hash are treated as named parameters:

      $server->Foo($Pos1, $Pos2, {Name1 => $Value1,
                                  Name2 => $Value2});

Foreign Languages and Default Methods. Sometimes OLE servers use method and property names that are specific to a non-English locale. That means they might have non-ASCII characters, which aren't allowed in Perl variable names. In German, you might see `Öffnen` used instead of `Open`. In these cases, you can use the `Invoke()` method:

      $server->Invoke('Öffnen', @Arguments);

This is necessary because `$Server->Öffnen(@Arguments)` is a syntax error in current versions of Perl.

## PROPERTIES

As I said earlier, objects can expose three things to the outside world: methods, properties, and events. We've covered methods, and Win32::OLE can't handle events. That leaves properties. But as it turns out, properties and events are largely interchangeable. Most methods have corresponding properties, and vice versa.

An object's properties can be accessed with a hash reference:

      $server->{Bar} = $value;
      $value = $server->{Bar};

This example sets and queries the value of the property named `Bar`. You could also have called the object's `Bar()` method to achieve the same effect:

      $value = $server->Bar;

However, you can't write the first line as `$server->Bar = $value`, because you can't assign to the return value of a method call. In Visual Basic, OLE automation distinguishes between assigning the name of an object and assigning its value:

      Set Object = OtherObject

      Let Value = Object

The `Set` statement shown here makes `Object` refer to the same object as `OtherObject`. The `Let` statement copies the value instead. (The value of an OLE object is what you get when you call the object's default method.

In Perl, saying `$server1 = $server2` always creates another reference, just like the `Set` in Visual Basic. If you want to assign the value instead, use the `valof()` function:

      my $value = valof $server;

This is equivalent to

      my $value = $server->Invoke('');

## SAMPLE APPLICATION

Let's look at how all of this might be used. In Listing: 1 you'll see T-Bond.pl, a program that uses Win32::OLE for an almost-real world application.

The developer of this application, Mary Lynch, is a financial futures broker. Every afternoon, she connects to the Chicago Board of Trade (CBoT) web site at http://www.cbot.com and collects the time and sales information for U.S. T-bond futures. She wants her program to create a chart that depicts the data in 15-minute intervals, and then she wants to record the data in a database for later analysis. Then she wants her program to send mail to her clients.

Mary's program will use Microsoft Access as a database, Microsoft Excel to produce the chart, and Lotus Notes to send the mail. It will all be controlled from a single Perl program using OLE automation. In this section, we'll go through T-Bond. pl step by step so you can see how Win32::OLE lets you control these applications.

## DOWNLOADING A WEB PAGE WITH LWP

However, Mary first needs to amass the raw T-bond data by having her Perl program automatically download and parse a web page. That's the perfect job for LWP, the libwww-perl bundle available on the CPAN. LWP has nothing to do with OLE. But this is a real-world application, and it's just what Mary needs to download her data from the Chicago Board of Trade.

      use LWP::Simple;
      my $URL = 'http://www.cbot.com/mplex/quotes/tsfut';
      my $text = get("$URL/tsf$Contract.htm");

She could also have used the Win32::Internet module:

      use Win32::Internet;
      my $URL = 'http://www.cbot.com/mplex/quotes/tsfut';
      my $text = $Win32::Internet->new->FetchURL("$URL/tsf$Contract.htm");

Mary wants to condense the ticker data into 15 minute bars. She's interested only in lines that look like this:

      03/12/1998 US 98Mar 12116 15:28:34 Open

A regular expression can be used to determine whether a line looks like this. If it does, the regex can split it up into individual fields. The price quoted above, `12116`, really means 121 16/32, and needs to be converted to 121.5. The data is then condensed into 15 minute intervals and only the first, last, highest, and lowest price during each interval are kept. The time series is stored in the array `@Bars`. Each entry in `@Bars` is a reference to a list of 5 elements: Time, Open, High, Low, and Close.

      foreach (split "\n", $text) {
          # 03/12/1998 US 98Mar 12116 15:28:34 Open
          my ($Date,$Price,$Hour,$Min,$Sec,$Ind) =
               m|^\s*(\d+/\d+/\d+) # " 03/12/1998"
                  \s+US\s+\S+\s+(\d+) # " US 98Mar 12116"
                  \s+(\d+):(\d+):(\d+) # " 12:42:40"
                  \s*(.*)$|x; # " Ask"
          next unless defined $Date;
          $Day = $Date;

          # Convert from fractional to decimal format
          $Price = int($Price/100) + ($Price%100)/32;

          # Round up time to next multiple of 15 minutes
          my $NewTime = int(($Sec+$Min*60+$Hour*3600)/900+1)*900;
          unless (defined $Time && $NewTime == $Time) {
              push @Bars, [$hhmm, $Open, $High, $Low, $Close]
                                                if defined $Time;
              $Open = $High = $Low = $Close = undef;
              $Time = $NewTime;
              my $Hour = int($Time/3600);
              $hhmm = sprintf "%02d:%02d", $Hour, $Time/60-$Hour*60;
          }

          # Update 15 minute bar values
          $Close = $Price;
          $Open = $Price unless defined $Open;
          $High = $Price unless defined $High && $High > $Price;
          $Low = $Price unless defined $Low && $Low > $Price;
      }

      die "No data found" unless defined $Time;
      push @Bars, [$hhmm, $Open, $High, $Low, $Close];

## MICROSOFT ACCESS

Now that Mary has her T-bond quotes, she's ready to use Win32::OLE to store them into a Microsoft Access database. This has the advantage that she can copy the database to her lap-top and work with it on her long New York commute. She's able to create an Access database as follows:

      use Win32::ODBC;
      use Win32::OLE;

      # Include the constants for the Microsoft Access
      # "Data Access Object".

      use Win32::OLE::Const 'Microsoft DAO';

      my $DSN      = 'T-Bonds';
      my $Driver   = 'Microsoft Access Driver (*.mdb)';
      my $Desc     = 'US T-Bond Quotes';
      my $Dir      = 'i:\tmp\tpj';
      my $File     = 'T-Bonds.mdb';
      my $Fullname = "$Dir\\$File";

      # Remove old database and dataset name
      unlink $Fullname if -f $Fullname;
      Win32::ODBC::ConfigDSN(ODBC_REMOVE_DSN, $Driver, "DSN=$DSN")
                             if Win32::ODBC::DataSources($DSN);

      # Create new database
      my $Access = Win32::OLE->new('Access.Application', 'Quit');
      my $Workspace = $Access->DBEngine->CreateWorkspace('', 'Admin', '');
      my $Database = $Workspace->CreateDatabase($Fullname, dbLangGeneral);

      # Add new database name
      Win32::ODBC::ConfigDSN(ODBC_ADD_DSN, $Driver,
              "DSN=$DSN", "Description=$Desc", "DBQ=$Fullname",
              "DEFAULTDIR=$Dir", "UID=", "PWD=");

This uses Win32::ODBC (described in TPJ #9) to remove and create T-Bonds.mdb. This lets Mary use the same script on her workstation and on her laptop even when the database is stored in different locations on each. The program also uses Win32::OLE to make Microsoft Access create an empty database.

Every OLE server has some constants that your Perl program will need to use, made accessible by the Win32::OLE::Const module. For instance, to grab the Excel constants, say `use Win32::OLE::Const 'Microsoft Excel'`.

In the above example, we imported the Data Access Object con-stants just so we could use `dbLangGeneral`.

## MICROSOFT EXCEL

Now Mary uses Win32::OLE a second time, to have Microsoft Excel create the chart shown below.

      Figure 1: T-Bond data generated by MicroSoft Excel via Win32::OLE

      # Start Excel and create new workbook with a single sheet
      use Win32::OLE qw(in valof with);
      use Win32::OLE::Const 'Microsoft Excel';
      use Win32::OLE::NLS qw(:DEFAULT :LANG :SUBLANG);

      my $lgid = MAKELANGID(LANG_ENGLISH, SUBLANG_DEFAULT);
      $Win32::OLE::LCID = MAKELCID($lgid);

      $Win32::OLE::Warn = 3;

Here, Mary sets the locale to American English, which lets her do things like use American date formats (e.g. `"12-30-98"` rather than `"30-12-98"`) in her program. It will continue to work even when she's visiting one of her international customers and has to run this program on their computers.

The value of `$Win32::OLE::Warn` determines what happens when an OLE error occurs. If it's 0, the error is ignored. If it's 2, or if it's 1 and the script is running under `-w`, the Win32::OLE module invokes `Carp::carp()`. If `$Win32::OLE::Warn` is set to 3, `Carp::croak()` is invoked and the program dies immediately.

Now the data can be put into an Excel spreadsheet to produce the chart. The following section of the program launches Excel and creates a new workbook with a single worksheet. It puts the column titles ('Time', 'Open', 'High', 'Low', and 'Close') in a bold font on the first row of the sheet. The first column displays the timestamp in hh:mm format; the next four display prices.

      my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
      $Excel->{SheetsInNewWorkbook} = 1;
      my $Book = $Excel->Workbooks->Add;
      my $Sheet = $Book->Worksheets(1);
      $Sheet->{Name} = 'Candle';

      # Insert column titles
      my $Range = $Sheet->Range("A1:E1");
      $Range->{Value} = [qw(Time Open High Low Close)];
      $Range->Font->{Bold} = 1;

      $Sheet->Columns("A:A")->{NumberFormat} = "h:mm";
      # Open/High/Low/Close to be displayed in 32nds
      $Sheet->Columns("B:E")->{NumberFormat} = "# ?/32";

      # Add 15 minute data to spreadsheet
      print "Add data\n";
      $Range = $Sheet->Range(sprintf "A2:E%d", 2+$#Bars);
      $Range->{Value} = \@Bars;

The last statement shows how to pass arrays to OLE objects. The Win32::OLE module automatically translates each array reference to a `SAFEARRAY`, the internal OLE array data type. This translation first determines the maximum nesting level used by the Perl array, and then creates a `SAFEARRAY` of the same dimension. The `@Bars` array already contains the data in the correct form for the spreadsheet:

      ([Time1, Open1, High1, Low1, Close1],
      ...
      [TimeN, OpenN, HighN, LowN, CloseN])

Now the table in the spreadsheet can be used to create a candle stick chart from our bars. Excel automatically chooses the time axis labels if they are selected before the chart is created:

      # Create candle stick chart as new object on worksheet
      $Sheet->Range("A:E")->Select;

      my $Chart = $Book->Charts->Add;
      $Chart->{ChartType} = xlStockOHLC;
      $Chart->Location(xlLocationAsObject, $Sheet->{Name});
      # Excel bug: the old $Chart is now invalid!
      $Chart = $Excel->ActiveChart;

We can change the type of the chart from a separate sheet to a chart object on the spreadsheet page with the `$Chart->Location` method. (This invalidates the chart object handle, which might be considered a bug in Excel.) Fortunately, this new chart is still the 'active' chart, so an object handle to it can be reclaimed simply by asking Excel.

At this point, our chart still needs a title, the legend is meaningless, and the axis has decimals instead of fractions. We can fix those with the following code:

      # Add title, remove legend
      with($Chart, HasLegend => 0, HasTitle => 1);
      $Chart->ChartTitle->Characters->{Text} = "US T-Bond";

      # Set up daily statistics
      $Open  = $Bars[0][1];
      $High  = $Sheet->Evaluate("MAX(C:C)");
      $Low   = $Sheet->Evaluate("MIN(D:D)");
      $Close = $Bars[$#Bars][4];

The `with()` function partially mimics the Visual Basic With statement, but allows only property assignments. It's a convenient shortcut for this:

      { # open new scope
        my $Axis = $Chart->Axes(xlValue);
        $Axis->{HasMajorGridlines} = 1;
        $Axis->{HasMinorGridlines} = 1;
        # etc ...
      }

The `$High` and `$Low` for the day are needed to determine the minimum and maximum scaling levels. MIN and MAX are spreadsheet functions, and aren't automatically available as methods. However, Excel provides an `Evaluate()` method to calculate arbitrary spreadsheet functions, so we can use that.

We want the chart to show major gridlines at every fourth tick and minor gridlines at every second tick. The minimum and maximum are chosen to be whatever multiples of 1/16 we need to do that.

      # Change tickmark spacing from decimal to fractional
      with($Chart->Axes(xlValue),
          HasMajorGridlines => 1,
          HasMinorGridlines => 1,
          MajorUnit => 1/8,
          MinorUnit => 1/16,
          MinimumScale => int($Low*16)/16,
          MaximumScale => int($High*16+1)/16
      );

      # Fat candles with only 5% gaps
      $Chart->ChartGroups(1)->{GapWidth} = 5;

      sub RGB { $_[0] | ($_[1] >> 8) | ($_[2] >> 16) }

      # White background with a solid border

      $Chart->PlotArea->Border->{LineStyle} = xlContinuous;
      $Chart->PlotArea->Border->{Color} = RGB(0,0,0);
      $Chart->PlotArea->Interior->{Color} = RGB(255,255,255);

      # Add 1 hour moving average of the Close series
      my $MovAvg = $Chart->SeriesCollection(4)->Trendlines
            ->Add({Type => xlMovingAvg, Period => 4});
      $MovAvg->Border->{Color} = RGB(255,0,0);

Now the finished workbook can be saved to disk as i:\tmp\tpj\data.xls. That file most likely still exists from when the program ran yesterday, so we'll remove it. (Otherwise, Excel would pop up a dialog with a warning, because the `SaveAs()` method doesn't like to overwrite files.)

      # Save workbook to file my $Filename = 'i:\tmp\tpj\data.xls';
      unlink $Filename if -f $Filename;
      $Book->SaveAs($Filename);
      $Book->Close;

## ACTIVEX DATA OBJECTS

Mary stores the daily prices in her T-bonds database, keeping the data for the different contracts in separate tables. After creating an ADO (ActiveX Data Object) connection to the database, she tries to connect a record set to the table for the current contract. If this fails, she assumes that the table doesn't exists yet and tries to create it:

      use Win32::OLE::Const 'Microsoft ActiveX Data Objects';

      my $Connection = Win32::OLE->new('ADODB.Connection');
      my $Recordset = Win32::OLE->new('ADODB.Recordset');
      $Connection->Open('T-Bonds');

      # Open a record set for the table of this contract
      {
        local $Win32::OLE::Warn = 0;
        $Recordset->Open($Contract, $Connection, adOpenKeyset,
                             adLockOptimistic, adCmdTable);
      }

      # Create table and index if it doesn't exist yet
      if (Win32::OLE->LastError) {
          $Connection->Execute(>>"SQL");
            CREATE TABLE $Contract
            (
              Day DATETIME,
              Open DOUBLE, High DOUBLE, Low DOUBLE, Close DOUBLE
            )
      SQL
          $Connection->Execute(>>"SQL");
            CREATE INDEX $Contract
            ON $Contract (Day) WITH PRIMARY
      SQL
          $Recordset->Open($Contract, $Connection, adOpenKeyset,
                                    adLockOptimistic, adCmdTable);
      }

`$Win32::OLE::Warn` is temporarily set to zero, so that if `$Recordset-`Open> fails, the failure will be recorded silently without terminating the program. `Win32::OLE-`LastError> shows whether the Open failed or not. `LastError` returns the OLE error code in a numeric context and the OLE error message in a string context, just like Perl's `$!` variable.

Now Mary can add today's data:

      # Add new record to table
      use Win32::OLE::Variant;
      $Win32::OLE::Variant::LCID = $Win32::OLE::LCID;

      my $Fields = [qw(Day Open High Low Close)];
      my $Values = [Variant(VT_DATE, $Day),
                    $Open, $High, $Low, $Close];

Mary uses the Win32::OLE::Variant module to store `$Day` as a date instead of a mere string. She wants to make sure that it's stored as an American-style date, so in the third line shown here she sets the locale ID of the Win32::OLE::Variant module to match the Win32::OLE module. (`$Win32::OLE::LCID` had been set earlier to English, since that's what the Chicago Board of Trade uses.)

      {
          local $Win32::OLE::Warn = 0;
          $Recordset->AddNew($Fields, $Values);
      }

      # Replace existing record
      if (Win32::OLE->LastError) {
          $Recordset->CancelUpdate;
          $Recordset->Close;
          $Recordset->Open(>>"SQL",
                           $Connection, adOpenDynamic);
              SELECT * FROM $Contract
              WHERE Day = #$Day#
      SQL
          $Recordset->Update($Fields, $Values);
      }

      $Recordset->Close;
      $Connection->Close;

The program expects to be able to add a new record to the table. It fails if a record for this date already exists, because the Day field is the primary index and therefore must be unique. If an error occurs, the update operation started by `AddNew()` must first be cancelled with `$Recordset->CancelUpdate`; otherwise the record set won't close.

## LOTUS NOTES

Now Mary can use Lotus Notes to mail updates to all her customers interested in the T-bond data. (Lotus Notes doesn't provide its constants in the OLE type library, so Mary had to determine them by playing around with LotusScript.) The actual task is quite simple: A Notes session must be started, the mail database must be opened and the mail message must be created. The body of the message is created as a rich text field, which lets her mix formatted text with object attachments.

In her program, Mary extracts the email addresses from her customer database and sends separate message to each. Here, we've simplified it somewhat.

      sub EMBED_ATTACHMENT {1454;}     # from LotusScript

      my $Notes = Win32::OLE->new('Notes.NotesSession');
      my $Database = $Notes->GetDatabase('', '');
      $Database->OpenMail;
      my $Document = $Database->CreateDocument;

      $Document->{Form} = 'Memo';
      $Document->{SendTo} = ['Jon Orwant >orwant@tpj.com>',
                             'Jan Dubois >jan.dubois@ibm.net>'];
      $Document->{Subject} = "US T-Bonds Chart for $Day";

      my $Body = $Document->CreateRichtextItem('Body');
      $Body->AppendText(>>"EOT");
      I\'ve attached the latest US T-Bond data and chart for $Day.
      The daily statistics were:

      \tOpen\t$Open
      \tHigh\t$High
      \tLow\t$Low
      \tClose\t$Close

      Kind regards,

      Mary
      EOT

      $Body->EmbedObject(EMBED_ATTACHMENT, '', $Filename);

      $Document->Send(0);

# VARIANTS

In this final section, I'll talk about Variants, which are the data types that you use to talk to OLE objects. We talked about this line earlier:

      my $Values = [Variant(VT_DATE, $Day),
                    $Open, $High, $Low, $Close];

Here, the `Variant()` function creates a Variant object, of type `VT_DATE` and with the value `$Day`. Variants are similar in many ways to Perl scalars. Arguments to OLE methods are transparently converted from their internal Perl representation to Variants and back again by the Win32::OLE module.

OLE automation uses a generic `VARIANT` data type to pass parameters. This data type contains type information in addition to the actual data value. Only the following data types are valid for OLE automation:

      B<Data Type     Meaning>
      VT_EMPTY      Not specified
      VT_NULL       Null
      VT_I2         2 byte signed integer
      VT_I4         4 byte signed integer
      VT_R4         4 byte real
      VT_R8         8 byte real
      VT_CY         Currency
      VT_DATE       Date
      VT_BSTR       Unicode string
      VT_DISPATCH   OLE automation interface
      VT_ERROR      Error
      VT_BOOL       Boolean
      VT_VARIANT    (only valid with VT_BYREF)
      VT_UNKNOWN    Generic COM interface
      VT_UI1        Unsigned character

The following two flags can also be used:

      VT_ARRAY      Array of values
      VT_BYREF      Pass by reference (instead of by value)

The Perl to Variant transformation. The following conversions are performed automatically whenever a Perl value must be translated into a Variant:

      Perl value                  Variant
      Integer values              VT_I4
      Real values                 VT_R8
      Strings                     VT_BSTR
      undef                       VT_ERROR (DISP_E_PARAMNOTFOUND)
      Array reference             VT_VARIANT | VT_ARRAY
      Win32::OLE object           VT_DISPATCH
      Win32::OLE::Variant object  Type of the Variant object

What if your Perl value is a list of lists? Those can be irregularly shaped in Perl; that is, the subsidiary lists needn't have the same number of elements. In this case, the structure will be converted to a ``rectangular'' `SAFEARRAY` of Variants, with unused slots set to `VT_EMPTY`. Consider this Perl 2-D array:

      [ ["Perl" ],            # one element
        [1, 3.1215, undef]    # three elements
      ]

This will be translated to a 2 by 3 `SAFEARRAY` that looks like this:

      VT_BSTR("Perl") VT_EMPTY      VT_EMPTY
      VT_I4(1) VT_R8(3.1415)        VT_ERROR(DISP_E_PARAMNOTFOUND)

The Variant To Perl Transformation. Automatic conversion from Variants to Perl values happens as follows:

      Variant                Perl value
      VT_BOOL, VT_ERROR      Integer
      VT_UI1, VT_I2, VT_I4   Integer
      VT_R4, VT_R8           Float value
      VT_BSTR                String
      VT_DISPATCH            Win32::OLE object

The Win32::OLE::Variant module. This module provides access to the Variant data type, which gives you more control over how these arguments to OLE methods are encoded. (This is rarely necessary if you have a good grasp of the default conversion rules.) A Variant object can be created with the `Win32::OLE::Variant->new` method or the equivalent `Variant()` function:

      use Win32::OLE::Variant;
      my $var1 = Win32::OLE::Variant->new(VT_DATE, 'Jan 1,1970');
      my $var2 = Variant(VT_BSTR, 'This is an Unicode string');

Several methods let you inspect and manipulate Variant objects: The `Type()` and `Value()` methods return the variant type and value; the `As()` method returns the value after converting it to a different variant type; `ChangeType()` coerces the Variant into a different type; and `Unicode()` returns the value of a Variant object as an object of the Unicode::String class.

These conversions are more interesting if they can be applied directly to the return value of an OLE method call without first mutilating the value with default conversions. This is possible with the following trick:

      my $RetVal = Variant(VT_EMPTY, undef);
      $Object->Dispatch($Method, $RetVal, @Arguments);

Normally, you wouldn't call `Dispatch()` directly; it's executed implicitly by either `AUTOLOAD()` or Invoke(). If `Dispatch()` realizes that the return value is already a Win32::OLE::Variant object, the return value is not translated into a Perl representation but rather copied verbatim into the Variant object.

Whenever a Win32::OLE::Variant object is used in a numeric or string context it is automatically converted into the corresponding format.

      printf "Number: %f and String: %s\n",
             $Var, $Var;

This is equivalent to:

      printf "Number: %f and String: %s\n",
             $Var->As(VT_R8), $Var->As(VT_BSTR);

For methods that modify their arguments, you need to use the `VT_BYREF` flag. This lets you create number and string Variants that can be modified by OLE methods. Here, Corel's `GetSize()` method takes two integers and stores the `x` and `y` dimensions in them:

      my $x = Variant( VT_I4 | VT_BYREF, 0);
      my $y = Variant( VT_I4 | VT_BYREF, 0);
      $Corel->GetSize($x, $y);

`VT_BYREF` support for other Variant types might appear in future releases of Win32::OLE.

# FURTHER INFORMATION

## DOCUMENTATION AND EXAMPLE CODE

More information about the OLE modules can be found in the documentation bundled with Win32::OLE. The distribution also contains other code samples.

The object model for Microsoft Office applications can be found in the Visual Basic Reference for Microsoft Access, Excel, Word, or PowerPoint. These help files are not installed by default, but they can be added later by rerunning setup.exe and choosing custom setup. The object model for Microsoft Outlook can be found on the Microsoft Office Developer Forum at: http://www.microsoft.com/OutlookDev/.

Information about the LotusScript object model can be found at: http://www.lotus.com/products/lotusscript.nsf.

## OLE AUTOMATION ON OTHER PLATFORMS

Microsoft also makes OLE technology available for the Mac. DCOM is already included in Windows NT 4.0 and can be downloaded for Windows 95. MVS and some Unix systems can use EntireX to get OLE functionality; see http://www.softwareag.com/corporat/solutions/entirex/entirex.htm.

# COPYRIGHT

Copyright 1998 The Perl Journal. http://www.tpj.com

This article originally appeared in The Perl Journal #10. It appears courtesy of Jon Orwant and The Perl Journal. This document may be distributed under the same terms as Perl itself.

#10 - Win32::OLE by Jan Dubois