1 tabellenblatt aus ca. 1600 xls. bzw. xlsx Dateien in einem neuem Tabellenblatt zusammenführen

Question

Hallo liebe supporter,

ich habe hier schon die ein oder andere Lösung als "stiller Leser" finden und anwenden können.
Vorab: ich habe keinerlei Ahnung von Makros oder VBA oder ähnlichem - bin aber gewillt sowas anzuwenden.

Nun zu meinem Probelm:

Es gibt einen Ordnern, in dem ca. 1.600 Dateien liegen.
Es sind Dateien die Teils mit Excel 2003 als xls. und teils mit 2007 als xlsx gespeichert wurden.

Diese Dateien sind alle identisch aufgebaut.
Es gibt 2 TAbellenblätter.
Tabellenblatt 1 = Rohformat --> Dieses Tabellenblatt ist unwichtig

Tabellenblatt 2 = Endformat --> Darum geht es.
Es gibt jeweils eine fixirte erste Zeile. Mit immer gleichem Inhalt
Theoretisch könnte diese mit übernommen werden und nacher in der neuen Masterdatei nach dem sortieren einfach gelöscht werden).
Die Anzahl der Zeilen variiert stark (von 1 bis über 1.000).
mit einer überschrift versehen sidn die Spalten A, B, C, D, E, F, G, H, I
Nicht alle Zellen in den Spalten sind immer ausgefüllt.

Ziel ist es, eine Masterdatei zu haben, in der ich alle Inhalte der ca. 1.600 Quelldateien in exakt dieser Form untereinander dargestellt habe.

Meine Lösung wäre STRG+C und STRG+V
Da dies aber 1-2 TAge in Anspruch nähme und für Finderkrämpfe sorgen würde, hoffe ich, dass mir jemand eine schnellere und leichtere Lösung präsentieren kann...

Danke im Voraus
Grüße
derdok

Answer 1

Hi,

unter der Voraussetzung, dass es das Tabellenblatt "Endformat" in allen Arbeitsmappen tatsächlich gibt, kannst du ea mit folgenden Code lösen:

Sub ArbeitsmappenZusammenfuehren()
Dim strVerzeichnis As String
Dim strTyp As String
Dim strDateiname As String
Dim wksMappe As Workbook
Dim lngLetzteZiel As Long
Dim lngLetzteQuelle As Long
strTyp = "*.xls*"
Application.ScreenUpdating = False
strVerzeichnis = "D:\Test\"
strDateiname = Dir(strVerzeichnis & strTyp)
With ThisWorkbook.Worksheets("Tabelle1")
Do While strDateiname <> ""
lngLetzteZiel = .UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
Set wksMappe = Workbooks.Open(strVerzeichnis & strDateiname)
lngLetzteQuelle = wksMappe.Worksheets("Endformat").UsedRange.SpecialCells(xlCellTypeLastCell).Row
wksMappe.Worksheets("Endformat").Range("A2:I" & lngLetzteQuelle).Copy .Cells(lngLetzteZiel, 1)
ActiveWorkbook.Close True
Set wksMappe = Nothing
strDateiname = Dir
Loop
End With
Application.ScreenUpdating = True
End Sub

Kopiert wird in das Tabellenblatt "Tabelle1" in der Arbeitsmappe, aus der du den Code ausführst - eventuell musst du den Namen anpassen. Ebenso musst du den Verzeichnisnamen an deine Bedingungen anpassen.

Bis später,
Karin

Answer 2

super - der Code funktioniert bei einem Testlauf mit wenigen Dateien.

Vielen Dank...!
Ich werde das zum Feierabend mal für die gesamten Daten anwerfen und gucken, was er daraus macht...


Ein Ding ist nur (halb so wild eigentlich):
Allerdings kopiert excel eine Menge "leere" Zellen mit.
kann das sein, oder denkt Excel aus einem speziellen Grund, dass da ein Inhalt drin ist?

Answer 3

Hi,

ja, das ist möglich, wenn Zellinhalt und/oder Formatierungen vorhanden waren und dann gelöscht bzw. umformatiert wurden. Excel "merkt" sich diese Zellen und wenn sie sich unterhalb des aktuell benutzten Bereich liegen, werden sie beim Ermitteln der letzten belegten Zeile durch Verwendung der folgenden Codeteile berücksichtigt:

lngLetzteQuelle = wksMappe.Worksheets("Endformat").UsedRange.SpecialCells(xlCellTypeLastCell).Row

Ändere sie deshalb mal wie folgt:

lngLetzteQuelle = wksMappe.Worksheets("Endformat").Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Vielleicht bringt das ja Abhilfe. Andernfalls musst du halt die leeren Zeilen am Ende löschen
.

Bis später,
Karin

Answer 4

ah Danke - Jetzt nimmt er deutlich weniger leere Zellen mit rüber.

Nun tritt aber folgendes auf.
Nach einer Weile,erscheint die Fehlermeldung:

"Index ausserhalb des gültigen Bereichs"

Woran kan das liegen?

Danke schon mal..!
Echt toll, dass du dir die Zeit nimmst :)

Answer 5

Hi,

an welcher Stelle im Code kommt die Fehlermeldung? Möglicherweise gibt es in einer Arbeitsmappe kein Tabellenblatt mit dem Namen "Endformat"?

Bis später,
Karin