mehrere Sheets zusammenführen und sortieren

  • Excel

Es gibt 22 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    mehrere Sheets zusammenführen und sortieren

    Hallo

    Ich habe mehre Excel Datei (4 Stück) und in jeder Excel Tabelle existiert 1 Sheet, welches vom Grundaufbau vollkommen identisch sind (nur Daten sind unterschiedlich).
    Nun möchte ich gerne das in einer neuen Excel Datei diese 4 Sheets zu einem zusammengefügt werden.
    In der ersten Spalte A ist immer eine Art "ID" und in Spalte B immer ein "Datum". Die anderen Spalten können wir erstmal vernachlässigen.
    Die Daten sollen erstmal einfach untereinander kopiert werden und dann nach Datum sortiert werden.
    Die Tabellen einzeln zu kopieren in jeweils einen unterschiedlichen Sheet geht ja, aber in einem Sheet ohne Leerzeile und dann noch sortieren funktioniert leider nicht.

    Hier mal der Code für einzeln kopieren:
    Vielen Dank für Eure Hilfe schonmal

    Visual Basic-Quellcode

    1. Private Function Datenkopieren(strPfad As String, strDateiname As String, arrTabellen() As String) As Boolean
    2. ' Rückgabe ist True, wenn Kopieren erfolgreich, False bei Fehler
    3. Dim ranBereich As Range
    4. Dim wbQuelle As Workbook
    5. Dim wbZiel As Workbook
    6. Dim wsQuelle As Worksheet
    7. Dim wsZiel As Worksheet
    9. fktKopiereDaten = False ' Rückgabewert initialisieren
    10. On Error GoTo Fehler ' Geordnetes Beenden, wenn z.B. beim Dateiöffnen ein Fehler auftritt
    12. Application.EnableEvents = False
    13. Application.ScreenUpdating = False
    14. ' Ziel = diese Datei
    15. Set wbZiel = ActiveWorkbook
    16. ' Quelle = oben angegebene Datei mit Pfad
    17. Set wbQuelle = Workbooks.Open(Filename:=strPfad + strDateiname, ReadOnly:=True, Editable:=False)
    19. Dim strTabellenname As Variant
    20. For Each strTabellenname In arrTabellen
    21. If strTabellenname = vbNullString Then ' wenn Ende der Liste erreicht, for-Schleife verlassen
    22. Exit For
    23. End If
    24. Set wsZiel = wbZiel.Worksheets(strTabellenname)
    25. Set wsQuelle = wbQuelle.Worksheets(strTabellenname)
    27. 'Werte auslesen und übertragen
    28. 'Debug.Print wsQuelle.Range("A1").SpecialCells(xlCellTypeLastCell).Address
    30. Set ranBereich = wsQuelle.Range("A2", wsQuelle.Range("A1").SpecialCells(xlCellTypeLastCell).Address)
    31. ranBereich.Copy Destination:=wsZiel.Range("A2")
    32. Next
    33. fktKopiereDaten = True ' alles OK
    35. Fehler:
    36. If Not wbQuelle Is Nothing Then
    37. wbQuelle.Close False
    38. End If
    39. Application.ScreenUpdating = True
    40. Application.EnableEvents = True
    41. If (fktKopiereDaten = False) Then
    42. MsgBox "Fehler beim Kopieren aus Datei " & strPfad + strDateiname
    43. End If
    44. End Function


    Zuordnung der einzelnen Tabellen und deren Zielort:

    Visual Basic-Quellcode

    1. Dim arrTabellen(20) As String ' Liste der zu kopierenden Tabellenblätter
    3. 'Datei 2
    4. Erase arrTabellen
    5. arrTabellen(0) = "Tabelle1"
    7. ' Kopierfunktion
    8. fktKopiereDaten "C:\Desktop\", "Datei1.xlsm", arrTabellen
    11. 'Datei 2
    12. Erase arrTabellen
    13. arrTabellen(0) = "Tabelle1"
    15. ' Kopierfunktion
    16. fktKopiereDaten "C:\Desktop\", "Datei2.xlsm", arrTabellen
    17. ......

    Ich hab gerade kein Excel parat.
    Aber kann man nicht nen Union-SQL Statement über die vier Sheets absetzen und als Source für eine neue Tabelle nehmen?
    So als Denkanstoß...
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    ... Nun solltest es selber wissen. :'D

    In wie fern nicht?
    Ich hab gerade mal testhalber ne Excel Datei mit drei Sheets erstellt...
    Hab die benannt gelassen wie standard und wenn ich als SQL Statement

    SQL-Abfrage

    1. SELECT * FROM [Tabelle1$] UNION SELECT * FROM [Tabelle2$] UNION SELECT * FROM [Tabelle3$]
    absetze bekomme ich eine Tabelle mit dem Inhalt der Dreien.
    Wo ist denn das Problem?
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    ... Nun solltest es selber wissen. :'D

    Hallo
    Ja aber dazu müsste ich die vier Sheets doch zuerst in die Excel kopieren und das will ich ja nicht.
    Es soll direkt aus dem anderen Datein die Sheets untereinander eingefügt werden
    und dann nicht einfach das vollständige sheet, sondern ohne Zeile 1

    Was dann nur heißen würde... im SQL String...
    SELECT * FROM [Dateipfad].[Sheet] UNION SELECT * FROM [Dateipfad2].[Sheet]
    .. Fertig.
    Und da die erste Zeile denke ich die Überschrift ist... kannst ja auch das eingrenzen... Dafür gibt es SQL doch...
    Mal davon ab, dass er im Standard die erste Zeile eh als Überschrift interpretiert und zusammenfassen würde.
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    ... Nun solltest es selber wissen. :'D

    Auch in VBA kann man problemlos mit SQL-Befehlen arbeiten.
    Aber damit musst dich auch beschäftigen. Copy-Paste-Code gibt es nicht.
    Schwierig ist aber eine so triviale Abfrage nicht.
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    Es war einmal ein kleiner Bär... der wollte eine Geschichte hörn... Da erzählte ihm seine Mutti:
    ... Nun solltest es selber wissen. :'D

    Visual Basic-Quellcode

    1. UsedRange.Delete xlUp
    2. For Each wbName in Split("c:\x1.xlsx","c:\x2.xlsx",",")
    3. Set wb=Workbooks.Open(wbName, False, True)
    4. wb.Sheets(1).UsedRange.Copy Cells(Rows.Count,1).End(xlUp).Offset(1)
    5. wb.Close False
    6. Next
    Code ins Destination-Sheet packen
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Hallo
    VIelen Dank erstmal für die Hilfestellung.
    Das Problem ist, dass in den Zieldateien immer so 10 Sheets sind und er soll sich aus jeder Datei immer das gleiche Sheet nehmen
    und diese in die geöffnete Zieldatei zusammenführen (ohne Zeile 1). Das Sheet heißt in jeder Datei "Zeiterfassung".
    Ich muss somit ja noch definieren, dass er immer nur das Sheet "Zeiterfassung" wählt und nicht alle Sheets.

    Visual Basic-Quellcode

    1. Public Function Zusammenfuehren()
    2. Dim wbName As Workbook
    3. Dim UsedRange As Range
    4. Dim wb As Workbook
    6. UsedRange.Delete xlUp
    7. For Each wbName In Split("C:\Users\User\Desktop\test\Datei1.xlsm", "C:\Users\User\Desktop\test\Datei2.xlsm", ",")
    8. Set wb = Workbooks.Open(wbName, False, True)
    9. wb.Sheets(1).UsedRange.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    10. wb.Close False
    11. Next
    13. End Function

    Ja aber selbst wenn ich das ändere bekomme ich noch eine Fehlermeldung,das Objektvariable oder With-Blockvariable nicht festgelegt.
    Muss in der Zieldatei schon ein Sheet "Zeiterfassung" vorhanden sein?

    Visual Basic-Quellcode

    1. Public Function Zusammenfuehren()
    2. Dim wbName As Workbook
    3. Dim UsedRange As Range
    4. Dim wb As Workbook
    6. UsedRange.Delete xlUp
    7. For Each wbName In Split("C:\Users\User\Desktop\test\Auftrennen.xlsm", "C:\Users\User\Desktop\test\Zuschnitt.xlsm", ",")
    8. Set wb = Workbooks.Open(wbName, False, True)
    9. wb.Sheets("Zeiterfassung").UsedRange.Copy Cells(Rows.Count, 1).End(xlUp).Offset(1)
    10. wb.Close False
    11. Next
    13. End Function

    TeamBob schrieb:

    Dim wbName As Workbook
    Dim UsedRange As Range
    Mach keine unsinnigen Deklarationen
    wbName ist ein String
    UsedRange ist eine bereits bestehende Eigenschaft des Worksheet.
    Wenn er das nicht findet, hast du den Code an der falschen Stelle.

    P.S.:
    Warum schreibst du eine Funktion, die keinen Wert zurückgibt? Da reicht eine Sub.
    Im Übrigen solltest du Option Explicit verwenden, damit du dich zwingst, sauber zu deklarieren.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Oh ok Danke.
    Also Option Explicit verwende ich oben eigentlich immer.
    Was meinst du mit nicht an der richtigen Stelle.
    Ich habe Public Function verwendet, da ich den Code in ein Modu eingefügt habe, wo auch die Kopierfunktionen sind.
    Dort habe ich ihn wie oben beschrieben eingefügt.
    Trotz der Änderungen findet er UsedRange nicht.

    TeamBob schrieb:

    da ich den Code in ein Modu eingefügt habe

    petaod schrieb:

    Code ins Destination-Sheet packen


    Wenn du mit Modulen arbeitest, ist die ganze Objektorientierung ad absurdum geführt.
    Dann musst du halt überall dazu schreiben, welches Objekt du meinst.
    Also z.B.
    ​Worksheets("Tabelle1").UsedRange.Delete xlUp
    oder
    ​wb.Sheets("Zeiterfassung").UsedRange.Copy Worksheets("Tabelle1").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Selbst wenn ich das hinzufüge bekomme ich eine Fehlermeldung.
    Wenn ich den Code von Oben in die Tabelle einfüge und nicht in Modul bekomme ich die Selbe Fehlermeldung,
    dass "Steuervariable für For EACH muss vom Typ Variant ob Obejct sein".

    Der Code steht im Modul drin

    Visual Basic-Quellcode

    1. Public Function Zusammenfuehren()
    2. Dim wbName As String
    3. Dim wb As Workbook
    5. Worksheets("Zeiterfassung").UsedRange.Delete xlUp
    6. For Each wbName In Split("C:\Users\User\Desktop\test\Datei1.xlsm", "C:\Users\User\Desktop\test\Datei2.xlsm", ",")
    7. Set wb = Workbooks.Open(wbName, False, True)
    8. wb.Sheets("Zeiterfassung").UsedRange.Copy Worksheets("Zeiterfassung").Cells(Rows.Count, 1).End(xlUp).Offset(1)
    9. wb.Close False
    10. Next
    12. End Function

Facebook
Telefonspende