|
Who is this for
This article discusses
on how to write perl scripts to communicate with Microsoft Office
documents through OLE. This will work only on Windows operating
system and if you have Microsoft Office installed on your machine.
What you need to
know
Perl
programming
Microsoft OLE
Objects and Methods. This is very important because the methods you
use will be those in the OLE objects.
Introduction
Let us say that you
have a database that you need to extract data from. Since you need to
extract data that will be easier done in Perl than in another
proprietary software, you wrote the program in Perl. But then, you
need to generate a Microsoft Word document. How do you do that?
Well, there is hope
because Perl has a module called Win32:OLE. This module allows you to
communicate with Microsoft Office components to generate Microsoft
Office documents.
First things first
First thing you need
is to install the Win32 and the Win32::OLE modules. Once you have
done this, you have to specify this in your script:
Initialize your
environment
The first thing to do
is to check if you have Microsoft Word installed. This is done
through this statement:
|
my
$x = Win32::OLE->GetActiveObject('Word.Application') ;
|
If everything is
installed, $x will return a reference to a Win32::OLE object.
If Word is installed,
you have to start it. You do this by:
|
my
$word = Win32::OLE->new('Word.Application',
sub
{ $_[0]->Quit; } )
|
The new method
of the OLE module starts Word. The second parameter is a routine that
executes when Word terminates. It can be a subroutine or an OLE
method
Create your
document
Now that Word has
started, you need to create a new document. In VBA, you create a new
document using this instruction: Documents.Add
In Perl, you write:
|
my
$doc = $word->Documents->Add;
my $select =
$word->Selection;
|
Notice how similar the
instructions are? You execute the Add method of the Documents
object referred by the $word object.
To add text to the
document, you need to add it to the Selection.
|
my
$text = "This is the first
line\n";
$select->TypeText($text);
|
To change the font of
the text, you need to define the Range.
|
my
$range = $doc->Range(0, length($text));
$range->Font->{Size}
= 12;
$range->Font->{ColorIndex} = 5; #Magenta
|
If you want to see
what other objects or properties are within an OLE object, here is a
code that you can use:
|
sub
ShowObjs {
my
$obj = shift;
foreach
(sort keys %$obj) {
print "Keys: $_ - $obj->{$_}\n";
}
}
|
Once you have an OLE
object, you can call this subroutine by passing the object like this:
|
ShowObjs($range);
# show all objects or properties of Obj
|
Sample Script
Here is a short script
to create a Word document:
|
#!perl use
Win32::OLE;
# check if Word exists
my $x =
Win32::OLE->GetActiveObject('Word.Application');
die "Word
not Installed" if $@;
# start Word program die if
unable to
unless (defined $x) {
$x =
Win32::OLE->new('Word.Application', sub { $_[0]->Quit; } )
or die 'Cannot start Word';
}
# Create new
document
my $d = $x->Documents->Add;
# define
selection
my $s = $x->Selection;
#set lines to be
written to document
@line = ('This is a test line',
'This
is second test Line',
'This is the third line',
);
#
$c is the color
# $start is the start of Range
# $end is
the end of Range
# $r is the Range object
my ($c, $start,
$end, $r) = (2, 0, 0, );
foreach (@line) {
$end +=
length($_) + 1;
# put the text
$s->TypeText($_);
#
define the Range
$r = $d->Range($start, $end);
# Set
font to 12 and color
$r->Font->{Size} =
12;
$r->Font->{ColorIndex} =
$c++;
$s->TypeText("\n");
$start = $end;
}
#
List Range Objects
ListObj($r);
#List Document
Objects
ListObj($d);
sub ListObj {
foreach (sort
keys %$r) {
print "Keys: $_ - $r->{$_}\n";
}
}
undef
$x;
|
For more
information
Read
the Win32::OLE documentation.
To
know more about OLE programming, go to the Microsoft
website or use the Help feature of Microsoft Office.
|
