Syntax um mehrere Excel-Dateien auszuwerten/Inhalte kopieren (VBA)

Matthias, Mittwoch, 01.06.2016, 13:23 (vor 1267 Tagen)

Geehrte VBA-Tutorial Gemeinde

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 (für mich noch theoretischen) Möglichkeiten faszinieren.


Mittels VBA sollen diverse Excel-Dateien, die alle samt identisch aufbereitet sind (also die gleiche Struktur haben), ausgewertet werden und 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 Bsp.):
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"

2) Aus Dateiname; zweite Zahl (zwei Stellen): 12_SalesReg_32_LAG.xls = 32
--> Ins zentrale Excel-File in die Spalte mit Bezeichnung "PeriodNr"

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.


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. 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

Oder Ansätzen wie
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.

Syntax um mehrere Excel-Dateien auszuwerten/Inhalte kopieren

Martin Asal @, Mittwoch, 01.06.2016, 14:19 (vor 1267 Tagen) @ Matthias

Hallo Matthias,

zunächst mal bin ich der festen Überzeugung, dass Excel das vollkommen falsche Programm für diese Zwecke ist. Die Wahrscheinlichkeit, dass irgendjemand Fehleingaben macht, ist extrem hoch, und um das in Excel auch nur einigermaßen in den Griff zu kriegen, muss man abenteuerliche Klimmzüge machen. Nehmen wir nur als Beispiel deinen "Artikel ABC": Da kann ganz schnell "Artikel ABV" oder "Artikel AVC" als Tippfehler draus werden (schau auf deiner Tastatur, wo die Buchstaben A,B,C, und V sind). Hinzu kommen etliche weitere Probleme, wie z.B. die Spalte A, in der nur "+" oder "-" stehen sollte - was beim Eingeben von Excel aber ganz schnell als der Beginn einer Formel verstanden werden könnte. Von Verschreibern in der Spalte B will ich lieber gar nicht erst anfangen, und ich könnte noch lange so weitermachen....

Langer Rede, kurzer Sinn: Mit einer Datenbank wie z.B. Access bist du weit besser bedient. Auch ein verteiltes Arbeiten, wie es bei euch der Fall zu sein scheint, ist da gut möglich, und falls alle User in einem Netzwerk hängen, kann sogar alles in einer einzigen Datenbank stattfinden, so dass dein "Zusammenführen" verschiedener Datenquellen gar nicht erst nötig ist.

Für den Fall, dass du trotz allem den abenteuerlicheren Weg gehen willst, hier ein paar Denkanstöße:

  • Wie willst du an den Dateinamen kommen? Tipp: Über Extras/Verweise kannst du die "Microsoft Office Object Libary" einbinden, und dann steht dir das Filedialog-Objekt zur Verfügung. Damit kannst du einen Datei-Öffnen-Dialog aufrufen und bekommst einen String mit einem Dateinamen zurück.

  • Die meisten deiner Probleme, die das Zerlegen eines Werts betreffen, lassen sich mit den Befehlen für Zeichenfolgen Left, Right und Mid lösen.

  • Um die Zeilen deiner Tabelle durchzugehen, würde ich keine "For Each"-Schleife nutzen, sondern ich würde in A1 beginnen, in der ersten Zeile alle Spalten komplett auswerten, dann die nächste Zeile, bis das Ende erreicht ist:
Dim Zeile as Integer
Do
   ...
   Zeile = Zeile + 1
loop until ...

Martin

Syntax um mehrere Excel-Dateien auszuwerten/Inhalte kopieren

Matthias, Dienstag, 07.06.2016, 14:58 (vor 1261 Tagen) @ Martin Asal

Hallo Martin

Besten Dank für deine wertvollen Inputs!

Ich musste die Artikelbezeichnungen/Angaben natürlich anonymisieren. Aber du hast natürlich Recht, eine Lösung mit Access, FileMaker o.ä. wäre sinnvoller. Ich werde mich dafür einsetzen, dass dies in Zukunft so genutzt wird.

Nun zu den eigentlichen Fragen:

- Wenn ich dich richtig verstanden habe, könnte ich z.B. mit dem unten aufgeführten Befehl jeweils die gewünschten Werte zerlegen?

Debug.Print Right("ABCD", 2)
CD

Wie krieg' ich die ausgelesenen Werte dann in das zentrale Excel-Sheet?


- Ich müsste ja im Teil "Do..." angeben, was er auszuwerten hat, dann arbeitet er sich ja in jeder einzelnen Zeile durch jede einzelne Spalte und geht dann eine Spalte weiter (+1). Wie krieg' ich nun die auzuwertenden Werte in ein zentrales Excel-Sheet? Ich müsste ja nach jedem einzelnen Befehl noch den Speicher- bzw. Kopierort angeben, oder?


- Bezüglich des FileDialogs um den String mit dem Dateinamen zu erhalten: Gibt es einen Befehl, welcher diesen String dann sozusagen ebenfalls direkt in das zentrale Excel-Sheet kopiert? Habe ich dich richtig verstanden, dass es im Ansatz so lösbar wäre?

Sub UseFileDialogOpen()

Dim lngCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For lngCount = 1 To .SelectedItems.Count
MsgBox .SelectedItems(lngCount)
Next lngCount

End With

End Sub

Wie gesagt, ich bin da noch ziemlicher VBA-Laie


Viele Grüsse,
Matthias

Syntax um mehrere Excel-Dateien auszuwerten/Inhalte kopieren

Martin Asal @, Dienstag, 07.06.2016, 16:02 (vor 1261 Tagen) @ Matthias

Aber du hast natürlich Recht, eine Lösung mit Access, FileMaker o.ä. wäre sinnvoller. Ich werde mich dafür einsetzen, dass dies in Zukunft so genutzt wird.

Ich würde von vornherein auf eine Datenbank drängen und mich gar nicht erst auf Excel einlassen. Ich kann mir einfach nicht vortellen, dass das gut geht.

Ich müsste ja im Teil "Do..." angeben, was er auszuwerten hat, dann arbeitet er sich ja in jeder einzelnen Zeile durch jede einzelne Spalte und geht dann eine Spalte weiter (+1). Wie krieg' ich nun die auzuwertenden Werte in ein zentrales Excel-Sheet? Ich müsste ja nach jedem einzelnen Befehl noch den Speicher- bzw. Kopierort angeben, oder?

Ja, denn wenn ich dich richtig verstanden habe, ist jede Zeile in der Quelldatei ein Datensatz und soll eine Zeile in der Zieldatei zur Folge haben. Also musst du immer genau dann, wenn du in der Quelldatei eine Zeile wechselst, auch in der Zieldatei weitergehen.

Bezüglich des FileDialogs um den String mit dem Dateinamen zu erhalten

Da bist du auf dem richtigen Weg. Nur, dass der User nur eine einzige Datei auswählen können sollte, also etwa so:

Sub UseFileDialogOpen()
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then MsgBox .SelectedItems(1)
     End With
End Sub


FileDialog hat noch weitere praktische Eigenschaften, mit denen du das Aussehen und Verhalten steuern kannst. Aber als Anfänger brauchst du dich noch nicht gleich damit zu beschäftigen.

Martin

RSS-Feed dieser Diskussion
powered by my little forum