Hey all,

I am really new in Perl and I need to write a script that can read an excel file.
I got most of it done, excel i need to know the number of worksheets in the workbook. is there a method for that?

my script looks something like the following, but because i do not know the number of sheet, i have to use a while loop, and the program will fail once it reaches the last sheet

(The following script is to get the names of each worksheet. )

Code:

my $Excel = Win32::OLE->GetActiveObject('Excel.Application')|| Win32::OLE->new('Excel.Application', 'Quit'); my $excelFile = $Excel->Workbooks->Open($ARGV[0]); my $theName; my $fileNumber=1; while (defined $excelFile->Worksheets($fileNumber)->{name}) {      $theName= $excelFile->Worksheets($fileNumber)->{name};      $SheetName[$fileNumber]=$theName;      print "The sheets are: $theName\n";      $fileNumber++; }

There's so many functions in Perl ... I don't know them all so I can't see the problem straight off.   Can you tell me how it fails (does it go into an infinite loop?  does it report an error message and if so, what?) and I then may have a chance of working out what's going wrong for you.

The error I got is this:
Win32:LE(0.1601) error 0x8002000b:"Invalid index" in METHOD/PROPERTYGET "Worksheets" at file.pl line 58

That is the line of
while (defined $excelFile->Worksheets($fileNumber)->{name})

because there are only three sheets in the excel, when it is attempting to read the 4th one, it fails, so that's why i need to know the exact number of sheets in order to use a for loop instead of while loop. I try to use foreach loop, but I do not know the array variable in thise case.. can anyone help??

Have you tried checking whether the $excelFile->Worksheets($fileNumber) element exists rather than assuming it does and looking in it for the name element?

well...
the excel file contain 3 sheets. so after the 3rd one, fileNumber increment to 4th, and it is attempting to access the 4th sheet which does not exist in the file. shouldn't it return false on the defined and terminate the loop?
However, the program is not doing that, it died on that line when it attempt the non-existing sheet

I suspect that it's returning an empty worksheet and you're then trying to access the name element which is where the problem occurs. Did the alteration I suggested make any difference?  Did you try it?

You could also look at the range of the COM collection - that will give you the start and end indexex and you could use for/foreach to step through without having to trap the end. Do you have David Roth's book - there's an example there on page 235.  If you've noty got it, it might be worth tracking it down as there's many pages of useful stuff - far more than I can attempt to paraphrase here.

I got an online version of that book. would u mind please telling me which section is that example in (or the example #) because the online version does not have a page number on it.
Thanks~

o thanks! i actually get the problem solve from keep reading that book online~ Thanks a bunch~

---