Home > Concepts and Solutions > Perl on Windows > Using Microsoft OLE Objects
Using Microsoft OLE Objects
User Rating: / 0
PoorBest 
Perl on Windows
Written by Philip L Yuson   

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:

use Win32::OLE



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.



 
Copyright: © 2018 Philip Yuson