Help with WIN32::OLE for Excel 
Author Message
 Help with WIN32::OLE for Excel

I am trying to write a program that automatically updates an Excel
worksheet at the end of the day.  Automation is already taken care of
but when I attempt to read from the excel document, in  order to
determine where the end of the row is it crashes and gives me the
error::
Can't call method "cells" on a undefined value at work1.pl  line 8.

Here is work1.pl::

use Win32::OLE;
$ex1 = Win32::OLE->new("Excel.Application",'quit');
$ex3 = $ex1->workbooks->open('c:\testing.xls');
$sheet = $ex3->workbook(1);
## $sheet->cells(2,1)->{value} = "also";
$row = 1;
$col = 1;
$ex2 = $sheet->cells(1,1)->{Value};  ## <-- line 8, here is the
problem
while ($ex2) {
  print "Existing cell : $row , $col : value = $cell";
  $row=$row+1;
  $col=$col+1;
  $ex2 = $sheet->cells($row,$col)->{value};

Quote:
}

undef $sheet;
undef $ex1;

Any help, any suggestions would be nice.

Brian Werner



Sun, 26 Aug 2001 03:00:00 GMT  
 Help with WIN32::OLE for Excel
[mailed & posted]

Quote:

>I am trying to write a program that automatically updates an Excel
>worksheet at the end of the day.  Automation is already taken care of
>but when I attempt to read from the excel document, in  order to
>determine where the end of the row is it crashes and gives me the
>error::
>Can't call method "cells" on a undefined value at work1.pl  line 8.

>Here is work1.pl::

>use Win32::OLE;
>$ex1 = Win32::OLE->new("Excel.Application",'quit');
>$ex3 = $ex1->workbooks->open('c:\testing.xls');
>$sheet = $ex3->workbook(1);

$ex3 is already a Workbook object. You want to get the Worksheet:

    $sheet = $ex3->Worksheets(1);

Please run your program under "perl -w". That would have shown you your error!

Quote:
>## $sheet->cells(2,1)->{value} = "also";
>$row = 1;
>$col = 1;
>$ex2 = $sheet->cells(1,1)->{Value};  ## <-- line 8, here is the
>problem
>while ($ex2) {
>  print "Existing cell : $row , $col : value = $cell";
>  $row=$row+1;
>  $col=$col+1;
>  $ex2 = $sheet->cells($row,$col)->{value};
>}
>undef $sheet;
>undef $ex1;

You might want to investigate the UsedRange property of the Worksheet object.
Here is some sample code that uses it to append entries to a worksheet:

#-----------------------------------------------------------------------------
use strict;
use Win32::OLE;

my $Excel = Win32::OLE->new('Excel.Application', 'Quit');
my $Book = $Excel->Workbooks->Open('i:\tmp\ole\test.xls');
my $Sheet = $Book->Worksheets(1);
print "UsedRange is ", $Sheet->UsedRange->Address, "\n";

my $LastRow = $Sheet->UsedRange->Row + $Sheet->UsedRange->Rows->Count - 1;
print "LastRow is $LastRow\n";
$Sheet->Cells($LastRow+1,1)->{Value} = localtime;
print $Sheet->UsedRange->{Value};
$Book->Save;
#-----------------------------------------------------------------------------

-Jan



Mon, 27 Aug 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Win32::OLE, Excel, and coloring

2. Win32::ole and excel

3. win32::ole excel hiding columns

4. Win32::OLE writing to excel.

5. Win32::OLE and excel?

6. Problem with Win32::OLE, Excel and ChartWizard

7. Problem publishing MS Excel worksheet to HTML using perl Win32::OLE

8. Calling subtotal method using Win32::OLE in Excel

9. Win32::OLE and Excel Sorts

10. Win32::OLE overwriting Excel sheets

11. Win32::OLE hiding Excel sheets

12. Example needed for Win32::OLE and Excel

 

 
Powered by phpBB® Forum Software