use Env;
use Time::tm; 
use Win32;
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel';
use warnings;


#===========================================================================================================================================
($sec, $min, $hour, $mday, $mon, $year, $wday, $yday, $isdst) = localtime(time);
$mon++;
$year = $year + 1900;
$hour--;

@dirs = ("D:\\DailyReports\\juristi","D:\\DailyReports\\ekonomisti","D:\\DailyReports\\sysadmini","D:\\DailyReports\\VIP");
foreach $dir (@dirs)
{
    
    DoCommand("dir \"$dir\" \/A:-D \/B");
    @filez=split(/\n/,$rez);
    
    foreach $file (@filez)
    {
        $file = CodePageEncDec("$file", "d2w");
        $ext=substr($file, length($file)-4, length($file));
        if (($ext eq "xlsx") || ($ext eq "xlsb"))
        {    $filename = "$dir"."\\"."$file";
            print("$filename\n");
            $Excel = Win32::OLE->GetActiveObject('Excel.Application') || Win32::OLE->new('Excel.Application', 'Quit'); 
            $Excel->{Visible} = 1;
            $Excel->{DisplayAlerts} = 0;

eval {            
            $Book = $Excel->Workbooks->Open($filename);
sleep(1);
};

if ( $@ ) {
print("error open file");
}    
else {
$Book->RefreshAll;
sleep(50);
$Book->RefreshAll;
sleep(50);
my $Sheet = $Book->Worksheets(1);
$Sheet -> Activate();
$Excel->{DisplayAlerts} = 1;
            $Excel->ActiveWorkbook->Save;
            $Book->Close;
            $Excel->Quit;
        };
        };
    };
};
#     S   U   B   S     =====================================================================================================================================================
# ---------------- RuN CoN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sub DoCommand
{
  my $cmd = shift;
  $rez="";
  my $line;
  open(CMD, "$cmd 2>&1 |");
  foreach $line (<CMD>)
  {
    $temp="$rez"."$line";
    $rez=$temp;
  }
    close CMD;
}
# ---------------- DoS / WiN EncodinG -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sub CodePageEncDec
{ 
  $_ = shift; 
  my ($EncDec) = @_; 
 
  my $win_chars = "\xA8\xB8\xB9\xC0\xC1\xC2\xC3\xC4\xC5\xC6\xC7\xC8\xC9\xCA\xCB\xCC\xCD\xCE\xCF\xD0\xD1\xD2\xD3\xD4\xD5\xD6\xD7\xD8\xD9\xDA\xDB\xDC\xDD\xDE\xDF\xE0\xE1\xE2\xE3\xE4\xE5\xE6\xE7\xE8\xE9\xEA\xEB\xEC\xED\xEE\xEF\xF0\xF1\xF2\xF3\xF4\xF5\xF6\xF7\xF8\xF9\xFA\xFB\xFC\xFD\xFE\xFF"; 
  my $dos_chars = "\xF0\xF1\xFC\x80\x81\x82\x83\x84\x85\x86\x87\x88\x89\x8A\x8B\x8C\x8D\x8E\x8F\x90\x91\x92\x93\x94\x95\x96\x97\x98\x99\x9A\x9B\x9C\x9D\x9E\x9F\xA0\xA1\xA2\xA3\xA4\xA5\xA6\xA7\xA8\xA9\xAA\xAB\xAC\xAD\xAE\xAF\xE0\xE1\xE2\xE3\xE4\xE5\xE6\xE7\xE8\xE9\xEA\xEB\xEC\xED\xEE\xEF"; 
 
    if ($EncDec eq "d2w") {                                                      # ЏаҐаҐЄ®¤Ёа㥬 dos2win  
      eval("tr/$dos_chars/$win_chars/"); 
    }else{                                                                       # ЏаҐаҐЄ®¤Ёа㥬 win2dos 
      eval("tr/$win_chars/$dos_chars/"); 
    } 
  return $_; 
};