1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
use strict;
use warnings;
use Cwd;
use utf8;
use POSIX qw/ strftime /;
use File::Copy;
use OLE;
use Win32::OLE::Const "Microsoft Excel";
#Global#
my $start_dir = cwd;
my $d = strftime("%d.%m.%Y %H:%M:%S", localtime);
my @typen = ("Befestigungsmaterial", "Ausbaumaterial", "Hls", "Installation", "Profilholz", "Spanplatten", "Dachstuhlholz", "Deckenbalken", "Wandelementholz", "Giebelbau", "Wandfertigung", "Individual");
# CALL #
excellent(@typen);
####################
### SUBROUTINE ###
####################
sub excellent{
my $filename = cwd . "/" . "inventur.xls"; #dateiname
my $excel = CreateObject OLE "Excel.Application"; #excel-objekt erstellen
$excel -> {Visible} = 1; #parameter festlegen
$excel->{DisplayAlerts}=0;
my $workbook = $excel -> Workbooks -> Open($filename); # Arbeitsmappe öffnen
foreach my $tbl (@_)
{
# Sheet selektieren
my $sheet = $workbook -> Worksheets("$tbl");
$sheet -> Select;
# Unprotect Sheet
$sheet->Unprotect();
# leere Zeilen löschen
my $last_row = $sheet -> Range("D1000")->End(xlUp)->Row; # letzte Zeile ermitteln
for(my $i=11;$i<=$last_row;$i++) ###### LINE 65 ######
{
if( $sheet->Range("D" . $i)->{Value} eq "0")
{
$sheet->Cells($i, 4)->EntireRow->Delete;
}
# Leerzeichen in Spalte entfernen
$sheet->Range("Q" . $i)->{Value}->Replace(What => " ", Replacement => "");
}
# Speichern und Schließen
$workbook->SaveAs({Filename => cwd . "/$tbl.csv",
FileFormat => xlCSV});
}
$workbook->Close();
$excel->Quit();
}
Replace(What => " ", Replacement => "")
my $last_row = $sheet -> Range("D1000")->End(xlUp)->Row; # letzte Zeile ermitteln