[SOLVED] How do I set Excel formulas with Win32::OLE?

How do I set Excel formulas with Win32::OLE?

Can anybody tell me, why the ...->{FormulaR1C1} = '=SUMME( "R[-3]C:R[-1]C" )'; doesn't work. In the Cell where the result should appear I get "#Wert!" ( maybe "Value" in English ). With the WENN(IF)-formula I get what I expect.

#!C:\Perl\bin\perl.exe
use warnings;
use strict;
use Win32::OLE qw;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $xl = Win32::OLE::Const -> Load( 'Microsoft Excel' );
my $excelfile = 'win32_ole_excel.xls';
my $excel = Win32::OLE -> GetActiveObject( 'Excel.Application' ) || Win32::OLE -> new( 'Excel.Application', 'Quit' ) or die $!;

my $workbook = $excel -> Workbooks -> Add();
my $sheet = $workbook -> Worksheets( 1 );
$sheet -> Activate;


$sheet->Range( 'A3' )->{Value} = 10;
$sheet->Range( 'B3' )->{FormulaR1C1} = '=WENN( "RC[-1]" > 5; "OK"; "Not OK")'; # IF(,,); workes fine


$sheet->Range( 'G1' )->{Value} = 3;
$sheet->Range( 'G2' )->{Value} = 7;
$sheet->Range( 'G3' )->{Value} = 6;
$sheet->Range( 'G4' )->{FormulaR1C1} = '=SUMME( "R[-3]C:R[-1]C" )'; # SUM(); doesn't work


$workbook -> SaveAs( { Filename => $excelfile, FileFormat => xlWorkbookNormal } );

Solution

With the help of the perl-community.de I have now a solution: I have to set

$excel->{ReferenceStyle} = $xl->{xlR1C1};

and use Z1S1 instead of R1C1

=SUMME(Z(-2)S:Z(-1)S)

But it looks like that in the German version I have to choose between the A1 and the Z1S1 (R1C1) notation.