Using OLE with Perl
ActivePerl-faq12 - Using OLE with Perl
How to use OLE automation with Perl - through the Win32::OLE module
Yes - otherwise this FAQ wouldn't have been a separate FAQ. ;-)
If you want to use OLE with Perl you need the Win32::OLE module. And you need
to read the documentation that comes with it.
use Win32::OLE doesn't export any variables and functions to the
main namespace, so if you want easy access to the in and with
functions you should load the module with
use Win32::OLE qw(in with);
A lot - Gurusamy Sarathy and then Jan Dubois redesigned the code and added a
bundle of enhancements. Old scripts should run with little or no modifications.
When writing new scripts there is no excuse for not using the new Win32::OLE
module options.
Look at the Win::OLE module documentation (under Incompatibilities).
Use the method PrintOut on a document object, for example:
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Word';
my $Word = Win32::OLE->new('Word.Application', 'Quit');
# $Word->{'Visible'} = 1; #
if you want to see what's going on
$Word->Documents->Open("C:\\DOCUMENTS\\test.doc")
|| die("Unable to open document ",
Win32::OLE->LastError());
$Word->ActiveDocument->PrintOut({
Background => 0,
Append => 0,
Range =>
wdPrintAllDocument,
Item =>
wdPrintDocumentContent,
Copies => 1,
PageType => wdPrintAllPages,
});
or simply
$Word->ActiveDocument->PrintOut;
If you have a sheet object you can extract the values of a series of cells
through $Sheet->Range->{'Value'}, for example:
my $array = $Sheet->Range("A8:B9")->{'Value'};
Now $array[0][0] contains the value of cell A8, $array[0][1] the value of
cell B8, $array[1][0] the value of cell A9 and $array[1][1] the value of cell
B9.
What is returned is an two-dimensional array (OK, an array with references to
arrays) that contains the values of the requested cells.
A complete example is here:
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; #
die on errors...
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); #
get already active Excel
#
application or open new
my $Book = $Excel->Workbooks->Open("C:\\DOCUMENTS\\test.xls"); #
open Excel file
my $Sheet = $Book->Worksheets(1); #
select worksheet number 1
my $array = $Sheet->Range("A8:B9")->{'Value'}; #
get the contents
$Book->Close;
foreach my $ref_array (@$array) { #
loop through the array
#
referenced by $array
foreach my $scalar (@$ref_array) {
print "$scalar\t";
}
print "\n";
}
To retrieve the formatted value of a cell you should use the {'Text'}
property instead of the {'Value'} property. This returns exactly
what is being displayed on the screen though! If the column is not wide enough,
you get a value of '######':
my $array = $Sheet->Range("A8:B9")->{'Text'};
A good idea would be to record a macro in Microsoft Excel and then convert
it to Perl. But here is a complete example:
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
my $Excel = Win32::OLE->new("Excel.Application");
$Excel->{Visible} = 1;
my $Book = $Excel->Workbooks->Add;
my $Sheet = $Book->Worksheets(1);
my $Range = $Sheet->Range("A2:C7");
$Range->{Value} =
[['Delivered', 'En route', 'To be shipped'],
[504, 102, 86],
[670, 150, 174],
[891, 261, 201],
[1274, 471, 321],
[1563, 536, 241]];
my $Chart = $Excel->Charts->Add;
$Chart->{ChartType} = xlAreaStacked;
$Chart->SetSourceData({Source => $Range, PlotBy => xlColumns});
$Chart->{HasTitle} = 1;
$Chart->ChartTitle->{Text} = "Items delivered, en route and to be
shipped";
You can use the Export method of a chart. If you have a chartobject the code
looks like this
$ChartObj->Chart->Export({
FileName =>
"$graphics_filename",
FilterName => 'GIF',
Interactive => 0});
A complete example that opens an Excel workbook, loops through all the charts
and saves them as GIFs and then closes the Excel workbook is here:
use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # die on
errors...
my $filename = 'c:\\documents\\test.xls';
my $filter = 'GIF'; #
can be GIF, JPG, JPEG or PNG
my $count = 0;
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
|| Win32::OLE->new('Excel.Application', 'Quit'); #
use the Excel application if it's open, otherwise open new
my $Book = $Excel->Workbooks->Open( $filename ); #
open the file
foreach my $Sheet (in $Book->Sheets) { #
loop through all sheets
foreach my $ChartObj (in $Sheet->ChartObjects) { #
loop through all chartobjects in the sheet
my $savename =
"$filename." . $count++ . ".$filter";
$ChartObj->Chart->Export({
FileName =>
$savename,
FilterName =>
$filter,
Interactive =>
0});
}
}
$Book->Close;
Macros in Microsoft Excel can be run by using the $Excel->Run method, for
example:
$Excel->Run("PrintPDFFile");
In order to do this, you of course need to have a macro in Excel that's
called 'PrintPDFFile'...
Use the Names->Add method on a sheet, giving it a name and a range object
to apply the name to, for example:
$Sheet->Names->Add({Name => 'NetCost', RefersTo =>
$Sheet->Range('$B$10')});
Again, an example :-)
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Outlook';
my $Outlook = Win32::OLE->new('Outlook.Application', 'Quit');
my $ol = Win32::OLE::Const->Load($Outlook);
my $namespace = $Outlook->GetNamespace("MAPI");
my $Folder = $namespace->GetDefaultFolder(olFolderInbox);
my $NewFolder = $Folder->Folders->Add("Test1");
To use ActiveX Data Objects (ADO):
use strict;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft ActiveX Data Objects';
my $Conn = Win32::OLE->new('ADODB.Connection'); # creates a connection object
my $RS = Win32::OLE->new('ADODB.Recordset'); # creates a recordset object
$Conn->Open('DBname'); # opens the database connection
my $Fields = ['Id', 'Name', 'Phone'];
my $Values = [1, 'Joe Doe', '555-1234'];
$RS->AddNew($Fields, $Values); # adds a record
print "This didn't go well: ", Win32::OLE->LastError(), "\n";
if (Win32::OLE->LastError());
$RS->Close;
$Conn->Close;
More information can be found in:
Lotus Notes can be accessed through OLE like this:
use strict;
use Win32::OLE;
my $Notes = Win32::OLE->new('Notes.NotesSession')
or die "Cannot start Lotus Notes Session object.\n";
my ($Version) = ($Notes->{NotesVersion} =~ /\s*(.*\S)\s*$/);
print "The current user is $Notes->{UserName}.\n";
print "Running Notes \"$Version\" on \"$Notes->{Platform}\".\n";
my $Database = $Notes->GetDatabase('', 'help4.nsf');
my $AllDocuments = $Database->AllDocuments;
my $Count = $AllDocuments->Count;
print "There are $Count documents in the database.\n";
for (my $Index = 1 ; $Index <= $Count ; ++$Index) {
my $Document = $AllDocuments->GetNthDocument($Index);
printf "$Index. %s\n", $Document->GetFirstItem('Subject')->{Text};
my $Values = $Document->GetItemValue('Index_Entries');
foreach my $Value (@$Values) {
print " Index: $Value\n";
}
last unless $Index < 5;
}
You can access all objects that are accessible to LotusScript. The
LotusScript classes can be seen at http://www.lotus.com/products/lotusscript.nsf.
See also the Lotus Notes
section of The Perl Journal #10 -
Win32::OLE by Jan Dubois.
The active printer can be set and retrieved through the word application
object with $Word->{ActivePrinter} = $printername.
If you record a macro in Microsoft Office, this can often be translated
directly into Perl. In Visual Basic for Applications (VBA) the syntax is like
this:
object.method(argument).property = value
In Perl this becomes
object->method(argument)->{property} = value;
So for example this code from VBA:
ActiveChart.Axes(xlCategory, xlPrimary).CategoryType =
xlCategoryScale
becomes this in Perl:
$Chart->Axes(xlCategory, xlPrimary)->{CategoryType} =
xlCategoryScale;
The best way to learn about methods/properties would be through an OLE type
browser if the documentation is unavailable.
If you have Microsoft Excel or Microsoft Word available, go into the Visual
Basic Editor (Alt+F11). Now you can open the object browser window (F2) and see
what you find.
There is also an OleView program (various names and versions) included in
Microsoft Visual C++ / Microsoft Visual Studio if you don't have Office. Or you
can download it from the Microsoft COM website (http://www.microsoft.com/com/).
But it is still possible that Notes doesn't reveal anything; objects are not
required to provide type info support. For example Lotus Notes doesn't reveal
nothing about its internal constants, methods and properties; you have to look
them up in the documentation.
For Lotus Notes look at http://www.lotus.com/products/lotusscript.nsf.
Yes, you can use the following code example to view all the constants - you
really shouldn't need this, but if you want to know what's going on, it might
help:
use strict;
use Win32::OLE;
use Win32::OLE::Const;
my $xl = Win32::OLE::Const->Load("Microsoft Excel");
printf "Excel type library contains %d constants:\n", scalar keys
%$xl;
foreach my $Key (sort keys %$xl) {
print "$Key = $xl->{$Key}\n";
}
Generally you should look at the documentation for Win32::OLE::Const.
Error messages from Win32::OLE doesn't go to the $! variable, but can be
accessed as Win32::OLE->LastError()
For some reason you get an 'OleInitialize' error if you open an OLE
application first and then open an ODBC connection to the Access ODBC driver. If
you do it the other way around, there is no problem with this.
It looks like the Access ODBC driver calls OleInitialize(). This fails when
Win32::OLE already initialized the COM subsystem as "apartment
threaded".
In order to remove the error either start the ODBC driver before the OLE
application or, better yet, initialize the OLE system with Win32::OLE->Initialize(Win32::OLE::COINIT_OLEINITIALIZE);
Execute your scripts with perl -w and use strict -
this catches most of your errors. Apart from this, read the documentation for
Win32::OLE (a good start) and possibly the documentation for the object you are
trying to use.
In the case of Microsoft Office 97, make sure that you have at least updated
to Service Release 1 - much of the OLE in Microsoft Office 97 is broken without
this update.
This FAQ was compiled by Henning Michael Møller-Nielsen of RTO based on
examples by many people, especially Jan Dubois. It is maintained by Henning
Michael Møller-Nielsen, Philip Martin, Kevin Meltzer and Eric Smith at perlwin32faq@rto.dk.
This FAQ is in the public domain. If you use it, however, please ensure that
you give credit to the original authors.
|
ActivePerl FAQ - Using OLE with Perl
|
|
