How do I use Perl and Excel OLE to copy a worksheet to a new or existing workbook?

Question

I have tried using the following Perl code, but the worksheet does not copy (nor move) and no error message is returned. Thanks in advance for your ideas.

#!/usr/bin/perl -w
use strict;
use warnings;
use OLE;
use Win32::OLE::Const 'Microsoft Excel';

my $Excel = CreateObject OLE "Excel.Application";
my $Book2 = $Excel->Workbooks->Add();
my $Book3 = $Excel->Workbooks->Open("C:\\temp\\test.xlsm");
my $Sheet3 = $Book3->Worksheets(1);
$Sheet3->Select;
$Sheet3->Move("Before" => $Book2->Worksheets(1));
$Book3->Close(0);
$Book2->SaveAs({Filename =>"C:\\temp\\new.xlsm",FileFormat => xlOpenXMLWorkbookMacroEnabled}); 
$Book2->Close();
$Excel -> Quit();

Answer 1

If you need to die on errors in Excel, you need to set the relevant flag to 3 (works with Office 2000):

$Win32::OLE::Warn = 3; # Die on errors in Excel

A more fundamental issue may have to do with your Office version though. The underlying OLE architecture has been overhauled since (I believe) Office 2007. I faced this issue a while back when a script I developed in tandem with Office 2007 refused to run with Office 2000.

The best modus operandi I can suggest is to work with Excel's (rather confusing) OLE browser by hitting the F2 key when in VBA mode.

---

The 'Hello, World' of Perl + OLE + Excel (works with Excel 2000):

use strict;
use warnings;
use Win32::OLE qw/in with/;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # Die on errors in Excel

my $excel = Win32::OLE->GetActiveObject('Excel.Application')
   || Win32::OLE->new('Excel.Application', 'Quit');
my $workbook = $excel->Workbooks->Add || warn "Couldn't add a workbook";
my $sheet = $excel->Worksheets->Add || warn "Couldn't add a worksheet";
$sheet->{Name} = "Hello World";

Answer 2

I found a workaround here using Copy/Paste over a large cell range. This approach could likely be refined by programatically identifying the active cell range instead of hard coding, which could also lead to some problems when going between different versions of Excel.

#!/usr/bin/perl -w
use strict;
use warnings;
use Win32::OLE qw/in with/;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3; # Die on errors in Excel

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
   || Win32::OLE->new('Excel.Application', 'Quit');
$Excel -> {"Visible"} = 0;
$Excel -> {"DisplayAlerts"} = 0;  
my $Book2 = $Excel->Workbooks->Add();
my $Book3 = $Excel->Workbooks->Open("C:\\temp\\test.xlsm");
$Book3->Worksheets(1)->Range("A:AM")->Copy;
$Book2->Worksheets(1)->Range("A:AM")->PasteSpecial;
$Book3->Close(0);
$Book2->SaveAs({Filename =>"C:\\temp\\new.xlsm",FileFormat => xlOpenXMLWorkbookMacroEnabled}); 
$Book2->Close();
$Excel->Quit();