Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Zeilen mit Daten aus verschiedenen Datenblättern in einem zusammenfügen





Frage

Hallo... hab schon etwas durchgelesen, find die Hilfe hier echt klasse, nur leider konnte ich nicht das passende finden. Habe ca. 20 Excel-Dokumente, die ich verschicke und dann mit Inhalt zurückbekomme, dabei werden von A5:I? verschiedene Informationen eingetragen, weiß leider nie wieviele Zeilen es sind, die Spalten verändern sich nicht. Diese Daten sollen in dem "Metadokument" von B2:J? eingefügt werden. Das nächste Dokument das kommt soll dann von B?, leider weiß ich ja dann die Zeile nicht, da ich vorher nicht wusste wieviele Zeilen im ersten Dokument kommen.... Kann mir jemand helfen wie ich dieses Problem lösen kann?? Ist glaub etwas knifflig... Sven

Antwort 1 von coros

Hallo Sven,

nachfolgend ein Makro, dass Dir alle Dateien in einem Verzeichnis öffnet und den Bereich A2:J? kopiert und die aktuelle Datei in das aktive Tabellenblatt einfügt.
Kopiere das Makro in ein StandardModul und starte es z.B. über eine Befehlsschaltfläche.

Option Explicit

Sub Alle_xls_öffnen_und_kopieren()
Application.ScreenUpdating = False
Dim Dateiname           As String
Dim Pfad                As String
Dim Zieldatei           As String
Dim firstRow            As Long
Dim lastRow            As Long
Dim Zieleblatt          As String
'Name der Datei, in das kopiert werden soll,
'wird automatisch aus dem Dateinamen ausgelesen
Zieldatei = ActiveWorkbook.Name
'Name des taberllenblattes, in das kopiert werden soll
'wird automatisch aus aktuellen Tabellenblattnamen ausgelesen
Zieleblatt = ActiveSheet.Name
'Pfad, in dem sich die Dateien, die ausgelesen werden sollen, befinden
Pfad = "C:\Textdateien\"
Dateiname = Dir(Pfad & "*.xls")
'Schleife zum Öffnen aller Exceldateien im Pfad aus der Variablen "Pfad"
Do While Dateiname <> ""
'Datei wird geöffnet
Workbooks.Open Pfad & Dateiname
'erste freie Zeile in Zieldatei wird ermittelt und in Variable "firstRow" geschrieben
firstRow = Workbooks(Zieldatei).Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Row
'letzte beschriebene Zeile in Quelldatei wird ermittelt und in Variable "lastRow" geschrieben
lastRow = Workbooks(Dateiname).Sheets(1).Range("J65536").End(xlUp).Row
'Daten werden aus dem Bereich A2:J? kopiert und in Zieldatei in ersten freien Zeile eingefügt
Workbooks(Dateiname).Sheets(1).Range("A2:J" & lastRow).Copy _
Workbooks(Zieldatei).Sheets(Zieleblatt).Cells(firstRow, 1)
'Quelldatei wird wieder geschlossen
ActiveWorkbook.Close True
Dateiname = Dir
'Nächste Datei wird geöffnet
Loop
End Sub


Du musst in dem Makro in der Zeile

Pfad = "C:\Textdateien\"


den Pfad noch anpassen. Eventuell müssen auch Blattindexzahlen angepasst werden. Da ich keinerlei weiteren Infos hatte, bin ich vom 1. Tabellenblatt der Quelldatei ausgegangen.
Außerdem müssen sich alle auszulesenden Dateien in einem Verzeichnis befinden. Aber nur diese Dateien, keine weiteren, da diese ansonsten auch ausgelesen werden.

Ich hoffe, es funktioniert. Ich habe das Makro nicht getestet, da ich zu faul bin mir 20 Dateien zu basteln. die Deinen Dateien entsprechen.

Solltest Du nicht wissen, wie Du den Code in Deine Datei bekommst, dann schau mal auf meiner HP in der Rubrik Anleitungen und dort dann in der Anleitungsnummer 3 nach. Dort stelle ich dazu eine bebilderte Anleitung zur Verfügung, die Dir sicherlich helfen wird.

Bei Fragen melde Dich.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du
ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 2 von bostler

Hallo Oliver,

Schon mal vielen Dank, aber irgendwie funzt das noch nicht...
Hab es jetzt schon mal angepasst...

Muss ich irgendwas machen, damit das ganze startet?
Gibt es die Möglichkeit das ganze in einer bestimmten Reihenfolge ablaufen zu lassen, also das zuerst Datei 1, dann Datei2 usw. ausgelesen wird?
Was passiert wenn ich in einer der Dateien etwas ändere, ändert sich dann auch die Hauptdatei?
Ist es möglich am Ende der eingelesenen Dateien in einer Zeile einige Berechnungen durchzuführen, wie z.B. Summe(), Anzahl()

Vielen Dank nochmal!!

Viele Grüße Sven


Option Explicit

Sub Alle_xls_öffnen_und_kopieren()
Application.ScreenUpdating = False
Dim Dateiname As String
Dim Pfad As String
Dim Zieldatei As String
Dim firstRow As Long
Dim lastRow As Long
Dim Zieleblatt As String
'Name der Datei, in das kopiert werden soll,
'wird automatisch aus dem Dateinamen ausgelesen
Zieldatei = ActiveWorkbook.Name
'Name des taberllenblattes, in das kopiert werden soll
'wird automatisch aus aktuellen Tabellenblattnamen ausgelesen
Zieleblatt = ActiveSheet.Name
'Pfad, in dem sich die Dateien, die ausgelesen werden sollen, befinden
Pfad = "F:\Verkauf\Marketing\05_Anzeigen\Anzeigenplanung\Vertretungen\"
Dateiname = Dir(Pfad & "*.xls")
'Schleife zum Öffnen aller Exceldateien im Pfad aus der Variablen "Pfad"
Do While Dateiname <> ""
'Datei wird geöffnet
Workbooks.Open Pfad & Dateiname
'erste freie Zeile in Zieldatei wird ermittelt und in Variable "firstRow" geschrieben
firstRow = Workbooks(Zieldatei).Sheets(1).Range("A65536").End(xlUp).Offset(1, 0).Row
'letzte beschriebene Zeile in Quelldatei wird ermittelt und in Variable "lastRow" geschrieben
lastRow = Workbooks(Dateiname).Sheets(1).Range("J65536").End(xlUp).Row
'Daten werden aus dem Bereich A2:J? kopiert und in Zieldatei in ersten freien Zeile eingefügt
Workbooks(Dateiname).Sheets(1).Range("A2:J" & lastRow).Copy _
Workbooks(Zieldatei).Sheets(Zieleblatt).Cells(firstRow, 1)
'Quelldatei wird wieder geschlossen
ActiveWorkbook.Close True
Dateiname = Dir
'Nächste Datei wird geöffnet
Loop
End Sub

Antwort 3 von bostler

Ok, nehme das mit dem nicht funktionieren zurück, aber zumindest noch nicht richtig....

Es fügt mir alles ab der Zeile 1 ein, leider steht in B1 etwas, vielleicht fügt es deswegen das ganze ein.... brauche aber erst die Sache ab Zeile 2.

Musste die Datei jetzt auch etwas verändern....
In A2 steht immer der Firmenname, jedoch nur in A2, die weiteren Infos kommen aus B2:J.
Das heißt, es sollte ab B2 die Zeile einfügen, bis eine leere kommt, aber auch A2...

Ich mach es nicht grad einfach :P

Viele Grüße Sven

Antwort 4 von Spiderschwein

Probiers mal ganz einfach mit der Funktion "Verketten". Is deutlich einfacher.

Sieht dann z.B. so aus: =VERKETTEN(A4;Tabelle2!C8;B5;Tabelle3!B7)

Antwort 5 von coros

Hallo Sven,

das Makro kopiert definitiv nicht ab der Zeile 2. Der Bereich der kopiert wird ist A2 bis J letzte beschriebene Zeile. Das sagt auch der Befehl

Workbooks(Dateiname).Sheets(1).Range("A2:J" & lastRow).Copy _


aus.

Die Reihenfolge bestimmt der Dateiname. Das bedeutet eine Datei mit dem Namen test 1 wird vor z.B. test 2 eingelesen. Es stand aber auch in Deiner Fragestellung nichts über eine bestimmte Reihenfolge, die eingehalten werden soll/ muss.

Nein, wenn in einer der Dateien sich ein Wert ändert, ändert der sich nicht in der Haupdatei. Dann muss das Makro erneut angestoßen werden. Vorher muss aber das gesamte Blatt, in das die Daten eingefügt werden soll, geleert werden, da ansonsten die Daten unten, also in der 1. freien Zeile angehängt würden. Kann man ebenfalls im Makro erledigen lassen. Stand aber auch nicht in Deiner Frage, dass bei Änderung in einer der Dateien, sich auch die Werte in der Hauptdatei ändern soll.

Ja, am Ende des Makros können Summen oder sonstiges gebildet werden. Stand genau wie die anderen Sachen´ebenfalls nicht in Deiner Fragestellung. Und auch jetzt kann ich aus

Zitat:
wie z.B. Summe(), Anzahl()


nicht entnehmen, was genau Du möchtest.

Wie Du siehst, eine genaue Beschreibung würde ein gutes Ergebnis erzielen. Eine weniger gute Beschreibung liefert ein Ergebnis wie aus meiner AW1, die nicht so ist, wie Du sie Dir vorgestellt hast.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du
ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 6 von bostler

Hallo Oliver,

tut mir leid, das ich das wohl doch ungenauer erklärt habe, als das sein sollte.

Ich versuche es jetzt nochmal ganz genauer:

Es gibt 20 Dateien in einem Ordner, diese Dateien werden mit Daten befüllt, in der ersten Zeile sind Daten, die nicht mit ausgelesen werden sollen. Ab Zeile 2 sollen Daten ausgelesen werden von A bis J.

Nach jeder von einer dieser Dateien wäre es nützlich, wenn Formeln in einer Zeile eingefügt werden könnten, z.B.: in Spalte H die Summe der Kosten, die aus dem einen Dokument ausgelesen wurde.

Die Reihenfolge vom einlesen der Dateien kann ich ja dann durch den Namen beeinflussen. Und wenn sich ein Wert ändert lasse ich es einfach neu durchlaufen!!

Hoffe ich habe dieses mal alles genauer erklärt und es ist für dich machbar....

Viele Grüße

Antwort 7 von coros

Hallo Sven,

nee, eigentlich ist diese Beschreibung genau so unvollständig wie Deine 1. Frage.

Das was Du geschreiben hast, macht das Makro. Es kopiert Daten von Spalte A, Zeile 2 bis Spalte J bis zur letzten beschriebenen Zelle und fügt diese Daten in die erste freie Zeile.

Um die Daten vor dem nächsten Einlesen zu löschen, müsste am Anfang des Makros die Zeile

Cells.ClearContents


eingefügt werden.

Summe bilden ist auch kein Problem. Nur von was soll die Summe gebildet werden. Du schreibst nur in Spalte H soll diese gebildet werden. Wo in Spalte H und von was?

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du
ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 8 von bostler

Ok, ich hoffe ich kriegs jetzt sinnvoll hin....

Wenn die Datei "Test1" eingelesen wird, dann soll in allen Zeilen, die aus dieser Datei kommen in Spalte H eine Summe direkt danach gebildet werden, genauso dann für "Test2, 3, 4, usw.

Wenn also Test1 4 Zeilen hat, mit den Zahlen 500, 400, 600 und 500, dann sollte in der Metadatei in der 5. Zeile die Summe dieser 4 stehen, sprich 2000.

Ich hoff ich werde etwas genauer :-P

Tut mir leid, dass deine Probleme mit mir hast...

Antwort 9 von coros

Hallo Sven,

nachfolgendes Makro sollte Dir vor dem Einfügen der Daten das gesamte aktive Tabellenblatt leeren, dann die Daten einfügen und in SPalte H nach jeder Tabelle die Summe der Spalte H bilden.
Ich habe das Makro nicht getestet, da ich keine Lust habe, mir 20 Dateien nachzubilden, die Deinen Dateien entsprechen würden. Daher kann ich auch nicht mit bestimmtheit sagen, ob es komplett funktioniert. Um es zu testen, müsstest Du mir Deine Dateien zur Verfügung stellen.

Option Explicit

Sub Alle_xls_öffnen_und_kopieren()
Application.ScreenUpdating = False
Dim Dateiname As String
Dim Pfad As String
Dim Zieldatei As String
Dim firstRow As Long
Dim lastRow As Long
Dim Zieleblatt As String
Dim lastRowNew As Integer
'Alles im aktiven Blatt löschen
Cells.ClearContents
'Name der Datei, in das kopiert werden soll,
'wird automatisch aus dem Dateinamen ausgelesen
Zieldatei = ActiveWorkbook.Name
'Name des taberllenblattes, in das kopiert werden soll
'wird automatisch aus aktuellen Tabellenblattnamen ausgelesen
Zieleblatt = ActiveSheet.Name
'Pfad, in dem sich die Dateien, die ausgelesen werden sollen, befinden
Pfad = "F:\Verkauf\Marketing\05_Anzeigen\Anzeigenplanung\Vertretungen\"
Dateiname = Dir(Pfad & "*.xls")
'Schleife zum Öffnen aller Exceldateien im Pfad aus der Variablen "Pfad"
Do While Dateiname <> ""
'Datei wird geöffnet
Workbooks.Open Pfad & Dateiname
'erste freie Zeile in Zieldatei wird ermittelt und in Variable "firstRow" geschrieben
firstRow = Workbooks(Zieldatei).Sheets(1).Range("H65536").End(xlUp).Offset(1, 0).Row
'letzte beschriebene Zeile in Quelldatei wird ermittelt und in Variable "lastRow" geschrieben
lastRow = Workbooks(Dateiname).Sheets(1).Range("J65536").End(xlUp).Row
'Daten werden aus dem Bereich A2:J? kopiert und in Zieldatei in ersten freien Zeile eingefügt
Workbooks(Dateiname).Sheets(1).Range("A2:J" & lastRow).Copy _
Workbooks(Zieldatei).Sheets(Zieleblatt).Cells(firstRow, 1)
'Quelldatei wird wieder geschlossen
ActiveWorkbook.Close True
Dateiname = Dir
'letzte beschriebene Zeile in Quelldatei in Spalte H wird ermittelt und in Variable "lastRow" geschrieben
lastRowNew = Workbooks(Dateiname).Sheets(1).Range("H65536").End(xlUp).Row
'Summe aus Spalte H eintragen
Workbooks(Zieldatei).Sheets(Zieleblatt).Cells(8, lastRowNew + 1) = _
Application.WorksheetFunction.Sum(Workbooks(Zieldatei).Sheets(Zieleblatt).Range("H" & firstRow & ":H" & lastRowNew))
'Nächste Datei wird geöffnet
Loop
End Sub


MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du
ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 10 von bostler

Wenn du mir sagst, wie ich dir das zukommen lassen kann?

Antwort 11 von coros

Hallo Sven,


schau mal auf meiner HP nach. Dort steht überall meine E-Mailadresse. Binde in der Betreffzeile irgendwie das Wort "Supportnet" und den Namen (Nickname), unter dem Du hier gepostet hast mit ein, da ich alle Mails deren Absender ich nicht kenne, ungelesen lösche.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du
ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 12 von coros

Hi @all,

nachdem mir Sven seine Dateien geschickt hatte, habe ich gesehen, dass sich bei meinem Makro einige Fehler eingeschlichen haben, da ich es ja nicht getestet hatte. Hier nun aber das funktionierende Makro für alle, die es interessiert.

Option Explicit

Sub Alle_xls_öffnen_und_kopieren()
Application.ScreenUpdating = False
Dim Dateiname As String
Dim Pfad As String
Dim Zieldatei As String
Dim firstRow As Long
Dim lastRow As Long
Dim Zieleblatt As String
Dim lastRowNew As Integer
'Alles im aktiven Blatt löschen
Range("A2:IV65536").ClearContents
'Name der Datei, in das kopiert werden soll,
'wird automatisch aus dem Dateinamen ausgelesen
Zieldatei = ActiveWorkbook.Name
'Name des taberllenblattes, in das kopiert werden soll
'wird automatisch aus aktuellen Tabellenblattnamen ausgelesen
Zieleblatt = ActiveSheet.Name
'Pfad, in dem sich die Dateien, die ausgelesen werden sollen, befinden
Pfad = "F:\Verkauf\Marketing\05_Anzeigen\Anzeigenplanung\Vertretungen\"
Dateiname = Dir(Pfad & "*.xls")
'Schleife zum Öffnen aller Exceldateien im Pfad aus der Variablen "Pfad"
Do While Dateiname <> ""
'Datei wird unsichtbar geöffnet
GetObject (Pfad & Dateiname)
'erste freie Zeile in Zieldatei wird ermittelt und in Variable "firstRow" geschrieben
firstRow = Workbooks(Zieldatei).Sheets(1).Range("H65536").End(xlUp).Offset(1, 0).Row
'letzte beschriebene Zeile in Quelldatei wird ermittelt und in Variable "lastRow" geschrieben
lastRow = Workbooks(Dateiname).Sheets(1).Range("H65536").End(xlUp).Row
'Daten werden aus dem Bereich A2:J? kopiert und in Zieldatei in ersten freien Zeile eingefügt
Workbooks(Dateiname).Sheets(1).Range("A2:J" & lastRow).Copy _
Workbooks(Zieldatei).Sheets(Zieleblatt).Cells(firstRow, 1)
'Quelldatei wird wieder geschlossen
Workbooks(Dateiname).Close False
Dateiname = Dir
'letzte beschriebene Zeile in Quelldatei in Spalte H wird ermittelt und in Variable "lastRow" geschrieben
lastRowNew = Workbooks(Zieldatei).Sheets(Zieleblatt).Range("H65536").End(xlUp).Row
'Summe aus Spalte H eintragen
Workbooks(Zieldatei).Sheets(Zieleblatt).Cells(lastRowNew + 1, 8) = _
Application.WorksheetFunction.Sum(Workbooks(Zieldatei).Sheets(Zieleblatt).Range("H" & firstRow & ":H" & lastRowNew))
'Nächste Datei wird geöffnet
Loop
End Sub



MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du
ein Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 13 von bostler

Nochmal vielen Dank an Oliver!!!

Funktioniert sehr gut!!

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: