Help with formula in excel

[padawan_linuxero]

Thank you !!!
I did not see that!
Thanks Kyle and Olus
:o)
---------------------------------------------------- Wise monks
I am working in automation of a file in Excel and it requieres that I do adding for example add all numbers in column H so in excel I can do this = sum (H1:H30) so I try to duplicate the same in perl but is giving me an error message in the workbook. #żNOMBRE? and when you press F2 this is what it shows: =sum("H17:H.$Lastrow3") my excel is in spanish but I think is #NAME? in english this is a little example of the problem that I have:

#!/usr/perl/bin 
use Win32::OLE;
use Win32::OLE::Variant;
use Win32::OLE::Const 'Microsoft Excel';

my $LastRow = $Sheet->UsedRange->Find({What=>"*",
    SearchDirection=>xlPrevious,
    SearchOrder=>xlByRows})->{Row};
my $LastCol = $Sheet->UsedRange->Find({What=>"*", 
                  SearchDirection=>xlPrevious,
                  SearchOrder=>xlByColumns})->{Column};

my ($Start_col, $Num_Cols,$End_col) = ('A', $LastCol, 'A');
++$End_col while --$Num_Cols;
my ($Start_row, $Num_rows) = (16, $LastRow);
my $End_row = $Start_row + $Num_rows - 1;
my $Range_str = "$Start_col$Start_row:$End_col$End_row";
my $range = $Sheet->Range($Range_str);

$range->AutoFormat(2);

$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> {Horizonta
+lAlignment} = xlHAlignCenter; 
$Sheet -> Range('H:I') -> {HorizontalAlignment} = xlHAlignRight;
$Sheet -> PageSetup -> {Orientation}  = xlLandscape;
$Sheet -> PageSetup -> {Zoom}         = 75;
$Sheet -> PageSetup -> {FitToPagesWide} = 1;
$Sheet->Columns('H:H')->{NumberFormat}='#,##0.000';
$Sheet->Columns('I:I')->{NumberFormat}='#,##0';
$Sheet -> Range('A1:M15') -> Borders(xlEdgeBottom)       -> {LineStyle
+}  = xlNone; 
$Sheet -> Range('A1:M15') -> Borders(xlEdgeLeft)         -> {LineStyle
+}  = xlNone; 
$Sheet -> Range('A1:M15') -> Borders(xlEdgeTop)          -> {LineStyle
+}  = xlNone; 
$Sheet -> Range('A1:M15') -> Borders(xlEdgeRight)        -> {LineStyle
+}  = xlNone; 
$Sheet -> Range('A1:M15') -> Borders(xlEdgeBottom)       -> {LineStyle
+}  = xlNone; 
$Sheet -> Range('A1:M15') -> Select();
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeBottom)       -> {LineStyle}  = xlDouble;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeBottom)       -> {Weight}     = xlThick;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeBottom)       -> {ColorIndex} = 1;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeLeft)         -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeLeft)         -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeTop)          -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeTop)          -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeBottom)       -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeBottom)       -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeRight)        -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+EdgeRight)        -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+InsideVertical)   -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+InsideVertical)   -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+InsideHorizontal) -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xl
+InsideHorizontal) -> {Weight}     = xlThin;

$Sheet->Range("A$LastRow2:J$LastRow2")->Copy();
$Sheet->Paste({Destination => $Sheet->Range('A15:J15')});
$Sheet->Range("A$LastRow2:L$LastRow2")->{Value} = [[ '', '', '', '', '
+', '', '', '', '', '', '', 'toto']];
$Sheet->Range('A15')->Copy();
$Sheet->Paste({Destination => $Sheet->Range('C15')});
$Sheet->Range("A15:B15")->{Value} = [[ '', '']];   
$Sheet->Range("D15:I15")->{Value} = [[ '', '', '', '', '', '']];
$Sheet->Range('J15')->Copy();
$Sheet->Paste({Destination => $Sheet->Range('G15')});
$Sheet->Range("B15")->{Value} = [[ 'Patente :']];
$Sheet->Range("J15")->{Value} = [[ '']]; 
$Sheet->Rows('416:416')->Select();
$Sheet->Range('416:416')->Delete({Shift => xlUp});

my $Lastrow3 = $LastRow2 -1;
my $lastrow4 = $LastRow2 + 1;

####HERE IS THE PROBLEM #########
$Sheet->Range("H$lastrow4")->{Formula} = '=sum("H17:H.$Lastrow3")';
####END OF PROBLEM################

$Excel    -> {DisplayAlerts} = 0; # This turns off the "This file alre
+ady exists" message.

my $file2 = "M.$file";
$Book -> SaveAs("$file2");

I really hope you wise monks could help me TIA

[kyle]

$Sheet->Range("H$lastrow4")->{Formula} = '=sum("H17:H.$Lastrow3")';

Try this instead:

$Sheet->Range("H$lastrow4")->{Formula} = qq{=sum("H17:H.$Lastrow3")};

(Look in perlop for qq.) The problem you had is that you were trying to interpolate a variable ($Lastrow3) into a single quoted string (which does not do variable interpolation).

[olus]

You have the following piece of code, where I guess you want $Lastrow3 to be interpolated but, since it is inside a single-quote, it doesn't.

$Sheet->Range("H$lastrow4")->{Formula} = '=sum("H17:H.$Lastrow3")';

Try changing it to the following

$Sheet->Range("H$lastrow4")->{Formula} = '=sum("H17:H.'.$Lastrow3.'")'
+;