Can I use OLE with Perl?

Yes - otherwise this FAQ wouldn't have been a separate FAQ, but just part of perlwin32faq4 ;-)

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);

What has changed with OLE since build 3xx

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.

How do I print a Microsoft Word document?

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;

How do I extract a series of cells from Microsoft Excel?

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'};

How do I make a chart in Microsoft Excel?

How do I save a chart from Microsoft Excel as GIF/JPEG/PNG?

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;

How do I run a macro in Microsoft Excel?

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'...

How do I set the name of a cell in Microsoft Excel?

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')});

How do I create a new folder in Outlook?

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");

How do I use ADO?

In order to use ActiveX Data Objects (ADO) you can just

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;

How do I use Lotus Notes?

Lotus Notes can be accessed through OLE, for example 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;
}

How do I set the printer in Word?

The active printer can be set and retrieved through the word application object with $Word->{ActivePrinter} = $printername.

How do I convert a VBA macro to Perl?

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;

Where do I find documentation for the object models?

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.

OK, but can I at least find the constants that are exported from Win32::OLE::Const?

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.

Why doesn't $! get set with the error message I am generating?

Error messages from Win32::OLE doesn't go to the $! variable, but can be accessed as Win32::OLE->LastError()

Why do I get an error when using ODBC and OLE?

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);

Why doesn't it work - even after all this?

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.