Win32::OLE Excel Adding worksheet after last current sheet

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

I don't want to use the writeExcel module. I need to use the OLE methods. I'm trying to add a new worksheet after the last worksheet. It's not working and I don't know why. I thought the following code would work:

my $Sheet2 = $Book->Worksheets->Add({after}=>$Book->Worksheets($Book->Worksheets->{count}));

With use strict I get the error message:
Bareword "after" not allowed while "strict subs" in use
Without it it still doesn't work. I get the error message: Unable to get the Add property of the Sheets class
Win32::OLE(0.1603) error 0x800a03ec
in METHOD/PROPERTYGET "Add" at
Any tips? Here is a complete example.

#!e:/perl/bin/perl.exe -w

use strict;
use Win32::OLE qw(in with);
use Win32::OLE::Const 'Microsoft Excel';

if(scalar @ARGV != 1) {
    usage();
    exit 1;
}

$Win32::OLE::Warn = 3; # die on errors...

# get already active Excel application or open new
my $Excel = Win32::OLE->GetActiveObject('Excel.Application')
    || Win32::OLE->new('Excel.Application', 'Quit')  or die Win32::OLE
+->LastError(); 

$Excel->{'Visible'} = 1;    # 1 is visible, 0 is not visible

my $path = $ARGV[0];
my $filename = Win32::GetFullPathName($path);
my $Book = $Excel->Workbooks->Open($filename) or die Win32::OLE->LastE
+rror(); 


###############
# Adding new worksheet
##############
#my $Sheet2=$Book->WorkSheets->Add;    #This works ok

my $Sheet2 = $Book->Worksheets->Add({after}=>$Book->Worksheets($Book->
+Worksheets->{count}));  # Does not work
#my $Sheet2=$Book->WorkSheets->Add;
my $name = $Sheet2->Name;
print "Working on sheet name: $name\n";


#$Sheet2->Move(After=> $Book->Worksheets->Count);   # Does not work

$Sheet2 -> Activate();
$Sheet2->{Name} = 'CSV';

$name = $Sheet2->Name;

print "Working on sheet name: $name\n";

# open Excel file

my $wkSheetCount = $Book->Worksheets->Count;

  foreach my $sheetnum (1..$wkSheetCount)
  {
     my $Sheet = $Book->Worksheets($sheetnum);
     $Sheet -> Activate();
     
     $name = $Sheet->Name;
     print "Working on sheet # $sheetnum - Its name is $name\n";
  }

$Book->Close;
undef $Book;


$Excel->Quit;

exit 1;


######################################################################
+#########
# usage 
#
######################################################################
+#########
sub usage{
    print "Usage:\n" .
        "\tperl $0 Test.xls \n";
}
[download]

Comment on Win32::OLE Excel Adding worksheet after last current sheet Download Code

Replies are listed 'Best First'.
Re: Win32::OLE Excel Adding worksheet after last current sheet by jsprat (Curate) on Nov 05, 2003 at 20:35 UTC
You are so close! To use named parameters, you need to use a hash reference. This line uses a reference to an anonymous hash: `my $Sheet2 = $Book->Worksheets->Add( {after => $Book->Worksheets($Book->Worksheets->{count})} );` [download]	[reply] [d/l]
Re: Re: Win32::OLE Excel Adding worksheet after last current sheet by Anonymous Monk on Nov 05, 2003 at 21:30 UTC
Perfect!!!! Thanks alot!	[reply]
Re^2: Win32::OLE Excel Adding worksheet after last current sheet by Anonymous Monk on Apr 30, 2009 at 10:14 UTC
Hi, I am tring to run macro through the perl script but always getting following error The macro 'SetIO' cannot be found. Win32::OLE(0.1707) error 0x800a03ec in METHOD/PROPERTYGET "Run" at agilent.pm line 20 the value is Vcore Please check the code below, please help me to solve this problem I am new to perl and VB/Excel #use strict; use warnings; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; my $filename = "E:/DM360/Power/DM365_Test.xls"; my $Excel = Win32::OLE->GetActiveObject('Excel.Application')\|\|Win32::OLE->new('Excel.Application', 'Quit'); # use the Excel application i #f it's open, otherwise open new my $Book = $Excel->Workbooks->Open( "E:\\DM360\\Power\\Measure.xls" ); # open the file my $Sheet = $Book->Worksheets(Readings); $Sheet->Activate(); printf ("the value is %s \n",$Sheet->Cells(1,3)->{'Value'}); $Excel->Run("SetIO"); $Excel->Run("CommandButton1_Click"); #$Excel->Run("Temp"); $Book->Save; #optional - save any changes made by the macro $Book->Close;	[reply]
Re^3: Win32::OLE Excel Adding worksheet after last current sheet by Corion (Pope) on Apr 30, 2009 at 10:26 UTC
`$Excel->Run("SetIO");` [download] This tries to run a macro `SetIO`. Excel says it cannot find this macro. So maybe you want to check that a macro of this name exists. There is nothing that we can do from here to help you further.	[reply] [d/l] [select]
Re^2: Win32::OLE Excel Adding worksheet after last current sheet by dipak28 (Initiate) on Apr 30, 2009 at 10:25 UTC
Hi, I am tring to run macro through the perl script but always getting following error - The macro 'SetIO' cannot be found. Win32::OLE(0.1707) error 0x800a03ec in METHOD/PROPERTYGET "Run" at agilent.pm line 20 the value is Vcore Please check the code below, please help me to solve this problem I am new to perl and VB/Excel #use strict; use warnings; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn = 3; my $filename = "E:/DM360/Power/DM365_Test.xls"; my $Excel = Win32::OLE->GetActiveObject('Excel.Application')\|\|Win32::OLE->new('Excel.Application', 'Quit'); # use the Excel application i #f it's open, otherwise open new my $Book = $Excel->Workbooks->Open( "E:\\DM360\\Power\\Measure.xls" ); # open the file my $Sheet = $Book->Worksheets(Readings); $Sheet->Activate(); printf ("the value is %s \n",$Sheet->Cells(1,3)->{'Value'}); $Excel->Run("SetIO"); $Excel->Run("CommandButton1_Click"); #$Excel->Run("Temp"); $Book->Save; #optional - save any changes made by the macro $Book->Close;	[reply]
Re: Win32::OLE Excel Adding worksheet after last current sheet by Nkuvu (Priest) on Nov 05, 2003 at 19:29 UTC
I've had success with the following commands: `$worksheet = $workbook->Worksheets(1); # add a sheet after current sheet my $new_sheet = $workbook->Worksheets->Add(undef, $worksheet);` [download]	[reply] [d/l]
Re: Re: Win32::OLE Excel Adding worksheet after last current sheet by Anonymous Monk on Nov 05, 2003 at 19:47 UTC
I could see wh the undef is important. But it's still not working. :( I'm using Excel 2000, on Win2K with Activestate perl version 5.8.0. Could one of these be a factor?	[reply]


Welcome to the Monastery
	PerlMonks