underdog81
Goto Top

.xls Exel Datei Splitten nach Kundennummer mit batch

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 face-sad

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

a250f9994bc69a6b9d3974e38e34cd5b

Content-ID: 171615

Url: https://administrator.de/forum/xls-exel-datei-splitten-nach-kundennummer-mit-batch-171615.html

Ausgedruckt am: 24.12.2024 um 13:12 Uhr

bastla
bastla 17.08.2011 um 11:33:44 Uhr
Goto Top
Hallo Underdog81 (wie Du siehst, ist eine Begrüßung gar nicht so schwierig)!

Wenn Du jetzt auch noch das gewünschte Ergebnis darstellst, sollten alle Klarheiten beseitigt sein ... face-wink

Grüße (geht auch ganz leicht)
bastla
Underdog81
Underdog81 17.08.2011 um 11:47:43 Uhr
Goto Top
Klar gehört sich eine nette Berüßung face-wink hatte ich leider vergessen face-sad ( 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"
33a5d19a3cf7051861e56988a1ae7597
Skyemugen
Skyemugen 17.08.2011 um 12:13:13 Uhr
Goto Top
Aloha,

Frage: .xls-Zwang? Oder könntest du auch mit .csv arbeiten? Denn Ersteres dürfte wohl nur mit VBS umsetzbar sein und Letzteres auch mit Batch.

greetz André
Underdog81
Underdog81 17.08.2011 um 12:16:45 Uhr
Goto Top
Mir wäre am liebsten eine .xls wegen der Tabellenansicht.
Denn es soll später gegebenfalls ausgedruckt werden um diese den Kunden zu übergeben.
bastla
bastla 17.08.2011 um 17:26:29 Uhr
Goto Top
Hallo Underdog81!

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 Sub
Vorausgesetzt habe ich, dass alle Kundennamen sich unterscheiden und somit kein Dateiname doppelt vorkommt - sicherer wäre (als Zeile 29)
Dateiname = KNr & "_" & .Cells(ZeileQuelle, SpalteQuelleName).Value 'Dateinamen aus KNr und Namen erstellen
Grüße
bastla
Underdog81
Underdog81 17.08.2011 um 17:40:13 Uhr
Goto Top
boaa das ist ja eine mega Datei!!!
ich teste es gleich mal aus.
Die Kundennamen sind definitiv immer anders, da die Kundennummer jetzt in der Datei auch imk Feld Kunde anführend drin steht.

Ich danke schon mal
Underdog81
Underdog81
Underdog81 17.08.2011 um 21:50:03 Uhr
Goto Top
Syntaxfehler
zeile1 Zeichen 8....
also das B von Base in der ersten Zeile??
bastla
bastla 17.08.2011 um 21:58:40 Uhr
Goto Top
Hallo Underdog81!

Welche Excel-Version verwendest Du? In der 2007er funktioniert die Zeile ...

Grüße
bastla
Underdog81
Underdog81 17.08.2011 um 22:02:19 Uhr
Goto Top
ne leider ist es die 2003er Version

Gibt es hier so extreme unterschiede?
hoffe mal, dass es dennoch irgendwie geht.
Dumm nur, dass die Suchmaschiene mit dem G nix ausspuckt.
bastla
bastla 17.08.2011 um 22:03:06 Uhr
Goto Top
Hallo Underdog81!

Lass die erste Zeile weg und ändere die (derzeitige) Zeile 16 auf
Spalten = UBound(Kopfzeile) + 1 'Anzahl der Datenspalten lt festgelegter Kopfzeile
Grüße
bastla
Underdog81
Underdog81 17.08.2011 um 22:13:23 Uhr
Goto Top
Hab ich gemacht aber jetzt kommt der nächste Fehler
Zeile3 Zeichen21
fehler `=` erwatet
bastla
bastla 17.08.2011 um 22:19:16 Uhr
Goto Top
Na schön, dann hangeln wir uns weiter ...

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()
aus ...

Grüße
bastla

[Edit] Zeile 7 angepasst [/Edit]
Underdog81
Underdog81 17.08.2011 um 22:35:31 Uhr
Goto Top
hmmm.. nun bekomme ich keine Fehlermeldung und es passiert nix...? face-sad
Underdog81
Underdog81 22.08.2011 um 09:19:19 Uhr
Goto Top
So nun läuft es und ich will euch auch verraten wie face-wink

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 Sub



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]
bastla
bastla 22.08.2011 um 15:29:58 Uhr
Goto Top
Hallo Underdog81!

Schön, wenn es funktioniert face-smile - 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 face-wink) 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 face-wink)