Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Perl Excel OLE help

by mmittiga17 (Scribe)
on Jan 06, 2010 at 20:32 UTC ( #815977=perlquestion: print w/replies, xml ) Need Help??

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\xls2tab.pl 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($_)}; }


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

        Regards,
        chinman
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.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://815977]
Approved by Corion
Front-paged by MadraghRua
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2022-09-29 01:53 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer my indexes to start at:




    Results (125 votes). Check out past polls.

    Notices?