.xls Exel Datei Splitten nach Kundennummer mit batch
Underdog81 (Level 1) - Jetzt verbinden
17.08.2011, aktualisiert 11:50 Uhr, 6036 Aufrufe, 15 Kommentare
Mir wurde schon viel geholfen hier im Forum und ich würde gerne mehr über verschiedene möglichkeiten lernen um nicht ständig zu fragen.
Doch leider muss ich wieder nerven 
Hallo an alle die diesen Beitrag lesen und im besten Fall auch noch hilfreich sein kann.
Es geht um eine .xls Datei die in verschiedenen Dateien aufgelistet werden muss.
Hier soll er dann die Kundennummer der Spalte A nehmen, um eine neue Datei anzulegen (gerne eine .xls)
Als Bezeichnung der neuen Datei soll er dann den Inhalt der Spalte B nehmen.
Die restlichen Spalten soll er so in die neue Datei Schreiben und die Spalte A und B eben Weglassen.
In Zeile 1 soll er dann in jeder Datei Artikelnummer, Bezeichnung und Menge anzeigen.
cih weiss nicht ob es wichtig ist aber einige Artikelnummern fangen mit einem Buchstaben an. (nicht alle)
Hier ein Beispiel des Aufbau der Exportierten Datei.
A B C D E
1 Kundennummer Kunde Artikelnummer Bezeichnung Menge
2 12345 12345 Hans Wurst, Wohnort 12309 Zucker 150
3 12345 12345 Hans Wurst, Wohnort 8520 Ball 10
4 67890 67890 Max Mustermann, Wohnort G1234 Limoade 900
5 67890 67890 Max Mustermann , Wohnot 222111 Beton 1
Ich habe leider keinen Plan obdas mit Batch geht, sorry.
Aber ich bin guter Dinge hier hilfe zu bekommen.
Hier eine besser Ansicht
Es geht um eine .xls Datei die in verschiedenen Dateien aufgelistet werden muss.
Hier soll er dann die Kundennummer der Spalte A nehmen, um eine neue Datei anzulegen (gerne eine .xls)
Als Bezeichnung der neuen Datei soll er dann den Inhalt der Spalte B nehmen.
Die restlichen Spalten soll er so in die neue Datei Schreiben und die Spalte A und B eben Weglassen.
In Zeile 1 soll er dann in jeder Datei Artikelnummer, Bezeichnung und Menge anzeigen.
cih weiss nicht ob es wichtig ist aber einige Artikelnummern fangen mit einem Buchstaben an. (nicht alle)
Hier ein Beispiel des Aufbau der Exportierten Datei.
A B C D E
1 Kundennummer Kunde Artikelnummer Bezeichnung Menge
2 12345 12345 Hans Wurst, Wohnort 12309 Zucker 150
3 12345 12345 Hans Wurst, Wohnort 8520 Ball 10
4 67890 67890 Max Mustermann, Wohnort G1234 Limoade 900
5 67890 67890 Max Mustermann , Wohnot 222111 Beton 1
Ich habe leider keinen Plan obdas mit Batch geht, sorry.
Aber ich bin guter Dinge hier hilfe zu bekommen.
Hier eine besser Ansicht
Antworten
- Mehr
Auf Facebook teilen
Auf Twitter teilen15 Antworten
- LÖSUNG bastla schreibt am 17.08.2011 um 11:33:44 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 11:47:43 Uhr
- LÖSUNG Skyemugen schreibt am 17.08.2011 um 12:13:13 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 12:16:45 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 17:26:29 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 17:40:13 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 21:50:03 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 21:58:40 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 22:02:19 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 22:03:06 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 22:13:23 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 22:19:16 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 22:35:31 Uhr
- LÖSUNG Underdog81 schreibt am 22.08.2011 um 09:19:19 Uhr
- LÖSUNG bastla schreibt am 22.08.2011 um 15:29:58 Uhr
- LÖSUNG Underdog81 schreibt am 22.08.2011 um 09:19:19 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 22:35:31 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 22:19:16 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 22:13:23 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 22:03:06 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 22:02:19 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 21:58:40 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 21:50:03 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 17:40:13 Uhr
- LÖSUNG bastla schreibt am 17.08.2011 um 17:26:29 Uhr
- LÖSUNG Underdog81 schreibt am 17.08.2011 um 12:16:45 Uhr
LÖSUNG 17.08.2011 um 11:33 Uhr
LÖSUNG 17.08.2011 um 11:47 Uhr
Klar gehört sich eine nette Berüßung 
hatte ich leider vergessen ( editiert im ersten Beitrag)
Nun zu dem Ergebnis............
Spalte A soll er nur zum identifizieren nutzen und dann löschen
Datei 1 hat den Namen "hans wurst, wohnort" und der Inhalt ist alles an Spalten nach B.
Datei 2 hat den gleichen inhaltsaufbau aber den namen " max mustermann, wohnort".
Bild kommt gleich...
Diese Ansicht wäre dann die Datei mit dem namen "hans wurst, wohnort"
hatte ich leider vergessen ( editiert im ersten Beitrag)Nun zu dem Ergebnis............
Spalte A soll er nur zum identifizieren nutzen und dann löschen
Datei 1 hat den Namen "hans wurst, wohnort" und der Inhalt ist alles an Spalten nach B.
Datei 2 hat den gleichen inhaltsaufbau aber den namen " max mustermann, wohnort".
Bild kommt gleich...
Diese Ansicht wäre dann die Datei mit dem namen "hans wurst, wohnort"
LÖSUNG 17.08.2011 um 12:13 Uhr
LÖSUNG 17.08.2011 um 12:16 Uhr
LÖSUNG 17.08.2011 um 17:26 Uhr
Hallo Underdog81!
Dann versuch es mit folgendem VBA-Script:
Vorausgesetzt habe ich, dass alle Kundennamen sich unterscheiden und somit kein Dateiname doppelt vorkommt - sicherer wäre (als Zeile 29)
Grüße
bastla
Dann versuch es mit folgendem VBA-Script:
Option Base 1
Sub Aufteilen()
'Quelle
Const AbZeileQuelle As Integer = 2 'erste Datenzeile
Const SpalteQuelleKNr As String = "A"
Const SpalteQuelleName As String = "B"
Const AbSpalteDaten As String = "C"
'Ziel
Const Zielpfad As String = "D:\Einzeldateien\" 'Pfad muss mit "\" enden
Const AbZeileZiel As Integer = 1 'Kopfzeile
Const AbSpalteZiel As String = "A"
Dim Kopfzeile() As Variant
Kopfzeile = Array("Artikelnummer", "Bezeichnung", "Menge") 'Kopfzeile = Spaltenüberschriften in Zieldatei
Spalten = UBound(Kopfzeile) 'Anzahl der Datenspalten lt festgelegter Kopfzeile
Set Quelle = ActiveWorkbook.ActiveSheet
ZeileQuelle = AbZeileQuelle
With Quelle
KNr = .Cells(ZeileQuelle, SpalteQuelleKNr).Value 'KNr auslesen
KNrZuletzt = ""
Do While KNr <> "" 'solange Daten vorhanden (KNr-Spalte nicht leer)
If KNr <> KNrZuletzt Then 'neuer Kunde?
If Not KNrZuletzt = "" Then 'Wenn nicht allererster Datensatz,
Zieldatei.ActiveSheet.Cells.EntireColumn.AutoFit 'optimale Spaltenbreite festlegen,
Zieldatei.SaveAs Zielpfad & Dateiname 'Zieldatei speichern und
Zieldatei.Close 'schließen
End If
Dateiname = .Cells(ZeileQuelle, SpalteQuelleName).Value 'Dateinamen auslesen
Set Zieldatei = Workbooks.Add 'neue Datei erstellen
ZeileZiel = AbZeileZiel
Zieldatei.ActiveSheet.Cells(ZeileZiel, AbSpalteZiel).Resize(1, Spalten).Value = Kopfzeile 'Kopfzeile eintragen
KNrZuletzt = KNr
End If
ZeileZiel = ZeileZiel + 1 'nächste Zeile in Zieltabelle
.Cells(ZeileQuelle, AbSpalteDaten).Resize(1, Spalten).Copy Zieldatei.ActiveSheet.Cells(ZeileZiel, AbSpalteZiel) 'Datenspalten in Zieldatei kopieren
ZeileQuelle = ZeileQuelle + 1 'nächste Zeile in Quelltabelle
KNr = Quelle.Cells(ZeileQuelle, SpalteQuelleKNr).Value 'KNr auslesen
Loop
If Not KNrZuletzt = "" Then 'Wenn nicht allererster Datensatz,
Zieldatei.ActiveSheet.Cells.EntireColumn.AutoFit 'optimale Spaltenbreite festlegen,
Zieldatei.SaveAs Zielpfad & Dateiname 'Zieldatei speichern und
Zieldatei.Close 'schließen
End If
End With
End SubDateiname = KNr & "_" & .Cells(ZeileQuelle, SpalteQuelleName).Value 'Dateinamen aus KNr und Namen erstellenbastla
LÖSUNG 17.08.2011 um 17:40 Uhr
LÖSUNG 17.08.2011 um 21:50 Uhr
LÖSUNG 17.08.2011 um 21:58 Uhr
LÖSUNG 17.08.2011 um 22:02 Uhr
LÖSUNG 17.08.2011 um 22:03 Uhr
LÖSUNG 17.08.2011 um 22:13 Uhr
LÖSUNG 17.08.2011 um 22:19 Uhr
Na schön, dann hangeln wir uns weiter ...
Tausche den ersten Teil gegen
aus ...
Grüße
bastla
[Edit] Zeile 7 angepasst [/Edit]
Tausche den ersten Teil gegen
Sub Aufteilen()
AbZeileQuelle = 2
SpalteQuelleKNr = "A"
SpalteQuelleName = "B"
AbSpalteDaten = "C"
Zielpfad = "D:\Einzeldateien\" 'Pfad muss mit "\" enden
AbZeileZiel = 1
AbSpalteZiel = "A"
Dim Kopfzeile()Grüße
bastla
[Edit] Zeile 7 angepasst [/Edit]
LÖSUNG 17.08.2011 um 22:35 Uhr
LÖSUNG 22.08.2011 um 09:19 Uhr
So nun läuft es und ich will euch auch verraten wie
Erst mal ein Danke an Bastla der mir über gut geholfen hat.
Ich öffne also Excel und gehe unten auf Tabelle1 und mache einen rechtsklick. Hier wähle ich Code anzeigen aus!
Dann öffnet sich Visual Basic. Dort wähle ich "Diese Arbeitsmappe" mit doppelklick aus.
Jetzt füge ich noch folgendes hinzu in dem rechten Feld.
Alles was zwischen den ## und den ### steht steht oben schon.
Nun speichern wir alles ab und schliessen das ganze einmal.
Beim nächsten öffnen kommt die Frage ob makros aktiviert werden sollen. Wähle jetzt Ja aus und er fängt an die Datei zu splitten.
Sollte die Abfrage nicht kommen geh auf "Extras-Makro-Sicherheit" Sicherheitsstufe auf Mittel setzen.
[Edit Biber] Codefomatierung, falls zu den einen oder anderen Zeilennummern noch Anmerkungen kommen sollten. [/Edit]
Erst mal ein Danke an Bastla der mir über gut geholfen hat.
Ich öffne also Excel und gehe unten auf Tabelle1 und mache einen rechtsklick. Hier wähle ich Code anzeigen aus!
Dann öffnet sich Visual Basic. Dort wähle ich "Diese Arbeitsmappe" mit doppelklick aus.
Jetzt füge ich noch folgendes hinzu in dem rechten Feld.
Alles was zwischen den ## und den ### steht steht oben schon.
Sub Workbook_Open()
Aktualisieren
End Sub
Sub Aktualisieren()
Dim varItem As Variant
Dim strName As String
On Error GoTo ErrHandler
Application.EnableEvents = True 'Makros einschalten !
For Each varItem In Array("C:\Ordner_der_zu_bearbeitenden_Datei\") 'Pfad muss mit "\" enden
strName = Dir(varItem & "*.xls")
Do While Len(strName) > 0
Workbooks.Open varItem & strName, UpdateLinks:=True
'MsgBox ActiveWorkbook.FullName
'##
'Quelle
Const AbZeileQuelle As Integer = 2 'erste Datenzeile
Const SpalteQuelleKNr As String = "A"
Const SpalteQuelleName As String = "B"
Const AbSpalteDaten As String = "C"
'Ziel
Const Zielpfad As String = "C:\Zielordner\" 'Pfad muss mit "\" enden
Const AbZeileZiel As Integer = 1 'Kopfzeile
Const AbSpalteZiel As String = "A"
Dim Kopfzeile() As Variant
Kopfzeile = Array("Artikelnummer", "Bezeichnung", "Menge") 'Kopfzeile = Spaltenüberschriften in Zieldatei
Spalten = UBound(Kopfzeile) + 1 'Anzahl der Datenspalten lt festgelegter Kopfzeile
Set Quelle = ActiveWorkbook.ActiveSheet
ZeileQuelle = AbZeileQuelle
With Quelle
KNr = .Cells(ZeileQuelle, SpalteQuelleKNr).Value 'KNr auslesen
KNrZuletzt = ""
Do While KNr <> "" 'solange Daten vorhanden (KNr-Spalte nicht leer)
If KNr <> KNrZuletzt Then 'neuer Kunde?
If Not KNrZuletzt = "" Then 'Wenn nicht allererster Datensatz,
Zieldatei.ActiveSheet.Cells.EntireColumn.AutoFit 'optimale Spaltenbreite festlegen,
Zieldatei.SaveAs Zielpfad & Dateiname 'Zieldatei speichern und
Zieldatei.Close 'schließen
End If
Dateiname = .Cells(ZeileQuelle, SpalteQuelleName).Value 'Dateinamen auslesen
Set Zieldatei = Workbooks.Add 'neue Datei erstellen
ZeileZiel = AbZeileZiel
Zieldatei.ActiveSheet.Cells(ZeileZiel, AbSpalteZiel).Resize(1, Spalten).Value = Kopfzeile 'Kopfzeile eintragen
KNrZuletzt = KNr
End If
ZeileZiel = ZeileZiel + 1 'nächste Zeile in Zieltabelle
.Cells(ZeileQuelle, AbSpalteDaten).Resize(1, Spalten).Copy Zieldatei.ActiveSheet.Cells(ZeileZiel, AbSpalteZiel) 'Datenspalten in Zieldatei kopieren
ZeileQuelle = ZeileQuelle + 1 'nächste Zeile in Quelltabelle
KNr = Quelle.Cells(ZeileQuelle, SpalteQuelleKNr).Value 'KNr auslesen
Loop
If Not KNrZuletzt = "" Then 'Wenn nicht allererster Datensatz,
Zieldatei.ActiveSheet.Cells.EntireColumn.AutoFit 'optimale Spaltenbreite festlegen,
Zieldatei.SaveAs Zielpfad & Dateiname 'Zieldatei speichern und
Zieldatei.Close 'schließen
End If
End With
'###
strName = Dir
Loop
Next
Application.EnableEvents = True
ErrHandler:
If Err.Number Then
MsgBox Err.Description, vbCritical, Err.Number
Resume Next
End If
End SubNun speichern wir alles ab und schliessen das ganze einmal.
Beim nächsten öffnen kommt die Frage ob makros aktiviert werden sollen. Wähle jetzt Ja aus und er fängt an die Datei zu splitten.
Sollte die Abfrage nicht kommen geh auf "Extras-Makro-Sicherheit" Sicherheitsstufe auf Mittel setzen.
[Edit Biber] Codefomatierung, falls zu den einen oder anderen Zeilennummern noch Anmerkungen kommen sollten. [/Edit]
LÖSUNG 22.08.2011 um 15:29 Uhr
Hallo Underdog81!
Schön, wenn es funktioniert
- das tut es (ohne Fehlermeldungen) aber sicher nur, wenn sich nur eine einzige Datei im Ordner "C:\Ordner_der_zu_bearbeitenden_Datei" befindet; anderenfalls würde jede "Const"-Zeile (Zeilen 20 bis 28) einen Fehler bewirken ...
Wenn es aber nur um eine einzelne Datei geht, sind die beiden Schleifen (Zeilen 13 und 15) zu hinterfragen - dort wird nämlich festgelegt, dass alle der (per "Array" in Zeile 13) angegebenen Ordner (ist aber nur einer) bearbeitet werden sollen, bzw dass der folgende Code auf alle sich in diesen Ordnern befindlichen ".xls"-Dateien angewendet werden soll (wobei Letzteres tatsächlich aber auch für eine einzelne Datei sinnvoll sein kann, wenn deren Name nicht konstant ist). Jedenfalls wären aber die Zeilen 19 bis 32 vor der Zeile 13 zu platzieren.
Dass der Kommentar in der Zeile 11 nicht dem davor stehenden Befehl entspricht, ist zu vernachlässigen (wenngleich dieser Befehl aber eigentlich nur mit einem "False" Sinn ergäbe; ansonsten könntest Du diese Zeile zusammen mit Zeile 70 entsorgen) ...
Noch kurz (für Interessierte) zur Erklärung, warum die beschriebene Vorgangsweise gewählt wurde: Da die zu bearbeitende Datei regelmäßig (per Export) neu erstellt wird, kann das Script nicht in dieser Datei selbst gespeichert werden - als Alternative blieben dann nur die "Persönliche Makroarbeitsmappe" oder eben das (hier verwendete) Ablegen in einer weiteren Excel-Datei ...
Grüße
bastla
P.S.: Thx @Biber (und dass der Konjunktiv nicht ernst gemeint war, ist auch klar)
Schön, wenn es funktioniert
- das tut es (ohne Fehlermeldungen) aber sicher nur, wenn sich nur eine einzige Datei im Ordner "C:\Ordner_der_zu_bearbeitenden_Datei" befindet; anderenfalls würde jede "Const"-Zeile (Zeilen 20 bis 28) einen Fehler bewirken ...Wenn es aber nur um eine einzelne Datei geht, sind die beiden Schleifen (Zeilen 13 und 15) zu hinterfragen - dort wird nämlich festgelegt, dass alle der (per "Array" in Zeile 13) angegebenen Ordner (ist aber nur einer
) bearbeitet werden sollen, bzw dass der folgende Code auf alle sich in diesen Ordnern befindlichen ".xls"-Dateien angewendet werden soll (wobei Letzteres tatsächlich aber auch für eine einzelne Datei sinnvoll sein kann, wenn deren Name nicht konstant ist). Jedenfalls wären aber die Zeilen 19 bis 32 vor der Zeile 13 zu platzieren.Dass der Kommentar in der Zeile 11 nicht dem davor stehenden Befehl entspricht, ist zu vernachlässigen (wenngleich dieser Befehl aber eigentlich nur mit einem "False" Sinn ergäbe; ansonsten könntest Du diese Zeile zusammen mit Zeile 70 entsorgen) ...
Noch kurz (für Interessierte) zur Erklärung, warum die beschriebene Vorgangsweise gewählt wurde: Da die zu bearbeitende Datei regelmäßig (per Export) neu erstellt wird, kann das Script nicht in dieser Datei selbst gespeichert werden - als Alternative blieben dann nur die "Persönliche Makroarbeitsmappe" oder eben das (hier verwendete) Ablegen in einer weiteren Excel-Datei ...
Grüße
bastla
P.S.: Thx @Biber (und dass der Konjunktiv nicht ernst gemeint war, ist auch klar
)
