I just want to say thanks for this module. I now never have to write ASP, VB, .Net again! My Win32::OLE::CrystalRuntime::Application module is a wrapper around this package and is rock solid. It runs multiple treads and has never had any issues.

Win32::OLE is a Perl module for interacting with Microsoft's OLE system for automating application programs. It can be used to control Office programs like Excel, Word, and PowerPoint from a Perl script. The entire object model of the Microsoft applications is available, so it is possible to almost completely replace VBA by Perl. (The parts you can't easily replace are things like forms.) I use this module daily and have had very few problems with it. The integration of the Microsoft objects into Perl object form has been done very well.

There are some books on how to use Perl with Microsoft applications, but I've never looked at them since it is easy to translate the online VBA documentation into Perl.

Another big plus is the ActiveState mailing lists where one can get very well-informed help for these modules.

* Pitfalls for the unwary

However, people who are thinking about using this module should know that the transition between the Microsoft and Perl worlds is not always entirely smooth. A bug which often catches me out is sending what I thought was a number (for example to specify an Excel sheet or column) to a Microsoft object and having an error thrown back at me, because what I thought was a number was actually a string:

my $text = "sheet12";
if ($text =~ /sheet(\d+)/) {

my $sheet = $1;

my $value = $Excel->Sheets($sheet)->cells(1,2)->Value();

# Crashes!
}

(The error here is that $sheet is a string not a number to OLE.)

Another source of trouble is Excel's date fields, which I solved thanks to advice found on Perlmonks.

Yet another problem is file names; sometimes / works, and sometimes it doesn't, and you have to

$filename =~ s:/:\\:g;

to get Windows applications to accept file names. For example, Excel will happily open a file /like/this but when you try to use the SaveAs method, it will only accept a file name \like\this.

* Comparison with other modules

For purposes of comparison, accessing Excel via Win32::OLE seems to be about five times faster than using the ParseExcel module on CPAN. On the other hand, you can't really use Excel via OLE non-interactively, so if you need to process Excel files in the background, ParseExcel is a better bet.

This module has saved me days of writing VBA by making all of VBA's utility available in Perl. I have dozens of scripts, now, to automate the creation of complex reports in Excel. Our engineers have used Win32::OLE to interface with Windows-based statistical analysis software. Without VBA in the way, it's surprising how useful the object model of many Microsoft applications can become.

Win32::OLE can save you from writing Visual Basic, and that oughta be enough for anyone.