Syntax um mehrere Excel-Dateien auszuwerten
07.06.2016 15:15:02
Matthias
Ich wende mich bezüglich einer etwas komplizierten Anfrage an Euch. Ausserdem bin ich ein VBA-Einsteiger, möchte jedoch gerne dazulernen weil mich die (a priori noch theoretischen) Möglichkeiten faszinieren.
In einem Hauptordner existieren ca. 20 Unterordner, die allesamt an die 20-30 Excel-Dateien enthalten.
Mittels VBA sollen die diversen Excel-Dateien, die alle samt identisch aufbereitet sind (also die gleiche Struktur haben), ausgewertet werden und die Auswertungen in ein zentrales Excel-File kopiert werden.
Verschiedene Abteilungen bzw. Mitarbeiter führten Listen mit verkauften Artikeln, bereits bezahlten Artikel und wieder stornierten Artikeln. Dazu wurde eine Artikel-Nr. und eine Artikel-Bezeichnung erfasst. Diese versch. Dateien sollen nun quasi für Auswertungen, Vergleiche etc. zusammengeführt bzw. ausgewertet werden. Ausserdem will man zukünftig weg von dieser Methodik bzw. eine etwas einfachere/standardisiertere Methode wählen.
Die Dateibeschriftungen der Excel-Files sehen so aus (als Beispiel):
12_SalesReg_32_LAG.xlsx
Die Inhalte der Excel-Dateien weisen folgende Struktur auf:
In Spalte A: (+/-); In Spalte B: Zusammengesetzte Nr. oder -----; Spalce C: Artikelname & Code oder ------
Alle Produkte bezahlt
------ ------
+ 4547.7755 Artikel ABC
+ 4354.9677 Artikel XYZ
+ 3632.1254 Artikel MNO
+ 4100.0201 Artikel PQR
- 9698.3300 Artikel EFG
- 7810.2500 Artikel TUV
Alle Produkte bezahlt
------ ------
+ 4547.7755 Artikel ABC
+ 4354.9677 Artikel XYZ
- 3632.1254 Artikel MNO
- 4100.0201 Artikel PQR
- 9698.3300 Artikel EFG
- 7810.2500 Artikel TUV
+ ------ ------
+ 2705.6301 Artikel KLM
+ 5454.0004 Artikel WER
- 3201.1707 Artikel HIJ
- 6022.7405 Artikel BMN
- 9698.3300 Artikel EFG
- 7810.2500 Artikel TUV
Alle Produkte bezahlt
------ ------
+ 4547.7755 Artikel ABC
+ 4354.9677 Artikel XYZ
- 3632.1254 Artikel MNO
- 6022.7405 Artikel BMN
- 9698.3300 Artikel EFG
Alle Produkte bezahlt
Was ausgewertet werden soll:
1) Aus Dateiname; erste Zahl (zwei Stellen): 12_SalesReg_32_LAG.xls = 12
--> Ins zentrale Excel-File in die Spalte mit Bezeichnung "AbtNr"
--> Dies wollte ich über den FileDialog mit folgendem Eintrag lösen:
Debug.Print Right("ABCD", 2)
CD
2) Aus Dateiname; zweite Zahl (zwei Stellen): 12_SalesReg_32_LAG.xls = 32
--> Ins zentrale Excel-File in die Spalte mit Bezeichnung "PeriodNr"
--> Auch dies wollte ich über den FileDialog mit dem o.g. Eintrag lösen.
3) Dichotomisierte Ausgabe, beim Eintrag "Alle Produkte bezahlt": wenn "Alle Produkte bezahlt" steht = 1; Sonst = 0)
--> Ins zentrale Excel-File in die Spalte mit Bezeichnung "AllSold"
4) Auswerten ob in Spalte A (+/-) ein + oder - steht und übertrag ins zentrale Excel-File in Spalte mit Bezeichnung "+/-"
5) Erste vier Ziffern (vor dem Punkt) der Artikel-Nr. (z.b 6022.7405: "6022" davon auswerten bzw. ins zentrale Excel-File kopieren)
6) Letzten vier Ziffern (nach dem Punkt) der Artikel-Nr. (z.b 6022.7405: "7405" davon auswerten bzw. ins zentrale Excel-File kopieren)
7) Kopieren der Artikelbezeichnung ins zentrale Excel-File; z.B. Artikel ABC kopieren
Idealerweise könnte man eine VBA-Syntax auf mehrere versch. Dateien (die allesamt aber ident. strukturiert sind; selbe Spaltenpositionen, selbe Formate) anwenden und VBA wertet die gewünschten Einträge ins zentrale Excel-File aus. Ich stelle mir vor, dass ich via VBA angebe, dass er alle xlsx im besagten Haupt- bzw. Unterordner durchgeht und dabei die Syntax anwendet. Dabei würde ich pro Zeile jede einzelne Spalte durchgehen:
Dim Zeile as Integer
Do
...
Zeile = Zeile + 1
loop until ...
Mir ist aber noch nicht ganz klar, welche Befehle für die oben aufgeführten 7 Punkte überhaupt nötig wären, damit die Auswertungen auch korrekt vorgenommen und das zentrale Excel-Sheet kopiert werden.
Das zentrale Excel-File, verfügt über folgende Spaltenbeschriftungen:
AbtNr PeriodNr ArtKrzel RegNr AllSold -/+ ArtListe ArtNr ArtTitle
Ich hatte bereits versucht, dies über "SelectCase" bzw. über die "BedingteKopieZeilen()" zu lösen. Dies scheint wohl aber nicht zu funktionieren bzw. ist nicht der korrekte Weg.
Z.B.
Public Sub Zeilen2()
Dim i As Integer
Dim cell As Range
i = 1
For Each cell In Eingabe.Range("C71:C877")
If Not cell Is Nothing Then
If cell.Value > "0" Then
cell.EntireRow.Copy 'Destination:=Tabelle2.Rows(i)
Tabelle1.Cells(i, 1).PasteSpecial , Paste:=xlPasteValues
i = i + 1
End If
End If
Next cell
End Sub
If .Cells(Zeile, 3).Value = "+" Then
.Rows(Zeile).Copy Destination:=Tabelle2.Rows(n)
Leider bisher ziemlich erfolglos - Evtl. setze ich als kompletter Neuling auch völlig am falschen Ort an.
Hätte jemand von Euch nützliche Tipps bzw. geeignete Formeln um dieses Problem zu lösen?
Vielen Dank im Voraus.
Freundliche Grüße
Matthias G.