Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

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

by blackadder (Hermit)
on Jun 07, 2005 at 11:20 UTC ( #464240=perlquestion: print w/replies, xml ) Need Help??

blackadder has asked for the wisdom of the Perl Monks concerning the following question:

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\ line 111 Can't use an undefined value as a HASH reference at C:\Perl\ABN AMROS\ 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?


Replies are listed 'Best First'.
Re: Extracting the value of an Excel Cell using Win32::OLE
by davidrw (Prior) on Jun 07, 2005 at 12:33 UTC
Re: Extracting the value of an Excel Cell using Win32::OLE
by mlh2003 (Scribe) on Jun 07, 2005 at 12:46 UTC
    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).
    Code is untested unless explicitly stated

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://464240]
Approved by Limbic~Region
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2021-12-01 22:50 GMT
Find Nodes?
    Voting Booth?
    R or B?

    Results (15 votes). Check out past polls.