Win32::Ole excel sheet test

[rmtsmith]

I am trying to populate an excel workbook and I want to use an existing sheet if it is there and if not create a sheet. Without cycling through all sheet names in a for loop, I am not sure how to do this. The code I have tried is...
unless (defined $UseSheet)
{
$Sheet = $Book->Worksheets()->Add();
$Sheet->{Name} = "$UseSheet";
}
else
{
$Sheet = $Book->Worksheets( $UseSheet );
}
$ec=Win32::OLE->LastError();
print "$ec\n";


The error returned is ...
Win32::OLE(0.1601) error 0x8002000b: "Invalid index"
in METHOD/PROPERTYGET "Worksheets"

 

[PaulTEG]

I think Excel expects the workbook index to be a an integer value, which it interpolates when its passed as a quoted string

Sheets(1).Activate is the same as
Sheets("First Sheet&quot.Activate

AFAIK

HTH
--Paul


It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[rmtsmith]

What I forgot to say was that the condition that caused the error was a non-existent sheet by the name in the variable $UseSheet.

.

 

[PaulTEG]

Try using a list of sheet names that you initialise at start-up, and add to when you create a new sheet?

--Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[hypochrismutreefuzz]

So, the error occurs when $UseSheet is defined and execution drops to the else block. Then the line

$Sheet = $Book->Worksheets($UseSheet);

causes the error? It might be you have to use the Item() function of the Worksheets:

$Sheet = $Book->Worksheets->Item($UseSheet);