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

Question

Hello, can anybody tell me, why the "...->{FormulaR1C1} = '=SUMME( "R[-3]C:R[-1]C" )';" doesn't work. In the Cell where should appear the result 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 } );

Answer 1

You do not need quotes around the SUM range. It should be explicit:

=SUMME(R[-3]C:R[-1]C)

Additional point - your IF/WENN formula is incorrect. It is trying to compare the string "RC[-1]" to the number 5 and coming up with YES! THE STRING IS GREATER. It is not doing what you think it is doing... you should take out the quotes around the references here as well.

EDIT: Here's my version of you code, which runs without any errors. Changes are commented. Had to apply a few changes for the English version of Excel. Ran against ActivePerl 5.10.1 Build 1006.

#!C:\Perl\bin\perl.exe
use warnings;
use strict;
# CHANGE - empty qw caused compilation error
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
$Win32::OLE::Warn = 3;
my $xl = Win32::OLE::Const -> Load( 'Microsoft Excel' );
# CHANGE - set path
my $excelfile = 'C:\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;
# CHANGE - Use IF, use commas, took quotes out around range
$sheet->Range( 'B3' )->{FormulaR1C1} = '=IF( RC[-1] > 5, OK, Not OK)'; # IF(,,); workes fine

$sheet->Range( 'G1' )->{Value} = 3;
$sheet->Range( 'G2' )->{Value} = 7;
$sheet->Range( 'G3' )->{Value} = 6;
# CHANGE - Use SUM, took quotes out around range
$sheet->Range( 'G4' )->{FormulaR1C1} = '=SUM(R[-3]C:R[-1]C)'; # SUM(); doesn't work

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

Answer 2

With the help of 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.