Extracting the value of an Excel Cell using Win32::OLE

[blackadder]

Dear Gods

How can I extract a cell value in Excel table using Win32::OLE?

Thanks Apologies for the above,...was posted in an error.

However, I have this code

my $new_book = $Excel->Workbooks->Open("c:\\work\\USA.xls");
my $new_sheet = $new_book->Worksheets("Sheet1");
my $array = $new_sheet->Range("A1:AQ1")->{'Value'}; 
my $counter = 0;

foreach my $ref_array (@$array) 
{
    my $col =0;    
    my $row =2;    
    foreach my $col_heads (@$ref_array) 
    {        
         if ($col_heads eq "")
         {
             $counter++;
             next;             
        }
        print ++$counter. ") $col_heads : ".$new_sheet->Cells($row,$co
+unter)->{'Value'}."\n";
                
    }    
    $row++;
}

I need to assign the value from this line $new_sheet->Cells($row,$counter)->{'Value'} to a variable. but I get this error;

Win32::OLE(0.1702) error 0x800a03ec
    in METHOD/PROPERTYGET "Cells" at C:\Perl\EDS\TempTool.pl line 111
Can't use an undefined value as a HASH reference at C:\Perl\ABN AMROS\
+TempTool.pl line 111.

if I do something like $var =$new_sheet->Cells($row,$counter)->{'Value'} Is there a way please That I can assign to it?

Thanks

[davidrw]

Tons of good info was posted just the other day here: Index of Spreadsheet FAQs, and there's also an entry or two in the Tutorials (this one: Using Win32::OLE and Excel - Tips and Tricks)

[mlh2003]

And it won't be too long before extracting data from Microsoft Office files will be made a whole lot easier. Their next version (Office 12) will save Word, Excel and Powerpoint files in XML format. More info. Perl is an adept language for extracting information from XML files (among many others).