Win32::OLE::TPJ(3) User Contributed Perl Documentation Win32::OLE::TPJ(3)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 "Oeffnen" used instead of
"Open". In these cases, you can use the Invoke() method:
$server->Invoke('Oeffnen', @Arguments);
This is necessary because "$Server->Oeffnen(@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.
perl v5.14.2 2005-09-17 Win32::OLE::TPJ(3)
