http://qs321.pair.com?node_id=304828

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]

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]
      Perfect!!!! Thanks alot!
[reply]
      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]
        $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]
      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]
      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]

Back to Seekers of Perl Wisdom