#!/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") -> {HorizontalAlignment} = 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(xlEdgeBottom)       -> {LineStyle}  = xlDouble;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom)       -> {Weight}     = xlThick;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom)       -> {ColorIndex} = 1;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeLeft)         -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeLeft)         -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeTop)          -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeTop)          -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom)       -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeBottom)       -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeRight)        -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlEdgeRight)        -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideVertical)   -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideVertical)   -> {Weight}     = xlThin;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideHorizontal) -> {LineStyle}  = xlContinuous;
$Sheet -> Range("$Start_col$Start_row:$End_col$End_row") -> Borders(xlInsideHorizontal) -> {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 already exists" message.

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