Re: List of worksheets in an Excel workbook

If you are using Win32::OLE, you can use its "in" function to enumerate any OLE collection:

#!perl
use warnings;
use strict;
use Win32::OLE qw(in); # import "in"

my $excel = Win32::OLE->new('Excel.Application', 'Quit');  
$excel->{Visible} = 1;
# q:\s\test.xls is a new worksheet with three sheets
my $xls = $excel->Workbooks->Open('q:\s\test.xls'); 

foreach my $sheet ( in $xls->Worksheets ) {
    print $sheet->Name, $/;
}
__END__
Output:
Sheet1
Sheet2
Sheet3
[download]

This also works with cells in a range, open workbooks, etc.

Comment on Re: List of worksheets in an Excel workbook Download Code

Replies are listed 'Best First'.
Re^2: List of worksheets in an Excel workbook by Anonymous Monk on Jun 08, 2005 at 08:16 UTC
This is what I tried `use strict; use warnings; use Win32; use Win32::OLE; use Win32::OLE qw(in with); use Win32::OLE::Const 'Microsoft Excel'; $Win32::OLE::Warn =2; my $excel = Win32::OLE->new('Excel.Application', 'Quit'); $excel->{Visible} = 1; my $xls = $excel->Workbooks->Open('c:/work/report1.xls'); foreach my $sheet ( in $xls->Worksheets ) { my $current_sheet = $sheet->Name, $/; print "Processing => $current_sheet\n"; }` [download] the correct result were returned with the following warning `Useless use of a variable in void context at C:\Perl\tool6.pl line 16. Processing => Midrange Servers Processing => Add-Del April 2005 Processing => Add-Del FEB 2005 Processing => Add-Del JAN 2005 Processing => Add-Del DEC 2004 Processing => Add-Del NOV 2004 Processing => Add-Del OCT 2004 Processing => Add-Del Sep 2004` [download] Nor sure what the warning is for! and what does /$ do? And if there is a way to obtain the names of the sheets directly rather than using a temp variable? Thanks	[reply] [d/l] [select]
Re^3: List of worksheets in an Excel workbook by davidrw (Prior) on Jun 08, 2005 at 14:44 UTC
look in `perldoc perlvar` for `$/` -- it's the input record separator. So `print $sheet->Name, $/;` is just a fancier, more generic way to do `print $sheet->Name, "\n";` what's wrong with using a temp variable? You can always turn the loop into a map and get an array: `# Loop method from above: foreach my $sheet ( in $xls->Worksheets ) { print $sheet->Name, $/; } # one line to get array of names: my @names = map { $_->Name } in $xls->Worksheets;` [download] As for the warning, it's because you have `my $current_sheet = $sheet->Name, $/;` but i think it was intended as: `my $current_sheet = $sheet->Name . $/;`	[reply] [d/l] [select]
Re^3: List of worksheets in an Excel workbook by bmann (Priest) on Jun 08, 2005 at 17:32 UTC
davidrw answered what $/ is, the input record separator. I just used it to print the newline. I most likely wouldn't want the newline if I were assigning the sheet name to a variable. Since $/ defaults to "\n", I use it in one-liners so I don't have to think about shell quoting issues (I use both Win32's cmd and bash regularly), and it looks like it's crept into my test scripts too. I would not use it as a newline in production code. About the warning, `my $current_sheet = $sheet->Name, $/` is parsed as `((my $current_sheet = $sheet->Name), $/);` [download] The comma operator in scalar context evaluates the left side, throws it away and returns the right side, a bare "$/". Look in perldiag for "Useless use of %s in void context".	[reply] [d/l] [select]