Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

Win32::OLE Excel Adding worksheet after last current sheet

by Anonymous Monk
on Nov 05, 2003 at 19:05 UTC ( #304828=perlquestion: print w/replies, xml ) Need Help??

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

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})} );
      Perfect!!!! Thanks alot!
      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;
        $Excel->Run("SetIO");

        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.

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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://304828]
Approved by Enlil
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (7)
As of 2021-03-04 13:20 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My favorite kind of desktop background is:











    Results (104 votes). Check out past polls.

    Notices?