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

Hi All, Need help with syntax for selecting a worksheet within excell file by name. I have been trying $Sheet = $Book->Worksheets("Positions"); But I get invalid index error. Any help will be appreciated. I can not seem to find anything out there to show me how this is done. Thanks!

use strict; use Getopt::Std; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; use Cwd; $Win32::OLE::Warn = 3; # die on errors... my $Excel = Win32::OLE->GetActiveObject ('Excel.Application') || Win32::OLE->new ('Excel.Application', 'Quit'); $Excel->{'Visible'} = 0; #0 is hidden, 1 is visible $Excel->{DisplayAlerts}=0; #0 is hide alerts # application or open new opendir DIR , '.' or die "Could not open current dir: $!\n"; my @FilePattern = grep(/.xls/i, readdir DIR); closedir DIR; my $x=""; foreach $x (@FilePattern) { my $file = $x; my $out = (split(/\./,$file))[0]; print "in $file\n"; print "out $out\n"; my $dir = getcwd(); print "$dir\n"; my $book = $dir . "\\" . $file; my $new = $dir . "\\" . $out .".txt"; my $Book = $Excel->Workbooks->Open($book); # open Excel file #returns name of 9 worksheets for (1..$Book->Worksheets->Count) { print "$_: <", $Book->Worksheets($_)->Name, ">\n"; } my $Sheet = $Book->Worksheets('Position'); #There is a worksheet calle +d Position $Book->SaveAs({Filename => $new, FileFormat => xlText}); $Book->Close; $Excel->Quit(); } I get the following output: 1: <AccountSummary> 2: <EquitySummary> 3: <Requirements> 4: <CurrencyBalances> 5: <SMASummary> 6: <SMADetail> 7: <Activity> 8: <Position> 9: <OptionsHedgeSummary>
Win32::OLE(0.1709) error 0x8002000b: "Invalid index" in METHOD/PROPERTYGET "Worksheets" at C:\lab\gsc\ line 36 </code> This is driving me nuts. Thank to any who can point me in correct path.

Replies are listed 'Best First'.
Re: Perl Excel OLE help
by chinman (Monk) on Jan 06, 2010 at 23:59 UTC
    You could use a loop like this to set the $Sheet object equal to the Position worksheet:

    for (1..$Book->Worksheets->Count) { my $name = $Book->Worksheets($_)->{Name}; if ($name =~ /Position/) {$Sheet = $workbook->Worksheets($_)}; }

      This seems to work, thanks chinman!!!!
Re: Perl Excel OLE help
by marto (Cardinal) on Jan 06, 2010 at 21:32 UTC

      I have looked every where including that site. Can not seem to get it to pull sheet 8.

      Thanks MM
        Hi MM,

        You cannot access the sheet by name this way, because Excel is expecting the sheet index and not the name. You need to use the sheet index for 'Position', thusly:

        my $Sheet = $Book->Worksheets(8);

Re: Perl Excel OLE help
by Anonymous Monk on Jan 06, 2010 at 23:47 UTC

      When I try to record a macro I get this: Sheets("Position").Select Which shows a Tab called Position. Also, the below code returns worksheet names and one of them is Position.

      for (1..$Book->Worksheets->Count) { print "$_: <", $Book->Worksheets($_)->Name, ">\n"; }
      I do not get it.