Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: VBA Dateien(alle Tab) aus Pfad in Datei kopieren

VBA Dateien(alle Tab) aus Pfad in Datei kopieren
Carmen
Hallo zusammen,
ich habe, mit Hilfe folgendes Makro, zum Zusammenführen von Dateien aus einem Pfad in eine neue Datei und ein Tabellenbaltt, in Benutzung.
Es funktioniert ganz wunderbar, nur möchte ich jetzt, dass alle Tabellenblätter aus den Dateien in die neue Datei eingefügt werden. Ich dachte ich ersetze ActiveSheet durch Worksheets, aber es geht leider nicht!
Hat jemand einen kleinen Tipp für mich?
Vielen Dank schon Mal vorab!!!
Sub Dateien_in_eine_Tabelle_zusammenfuehren()
Dim strPfad As String
Dim oWBEx As Workbook
Dim rngNextCell As Range
Dim FileArray()
Dim LCount As Long, MaxRow As Long
'Pfad angepasst
strPfad = "C:\Dokumente und Einstellungen\Eigene Dateien\Periode\2011\01 Januar\"
'Suche Dateien im Ordner
ListFilesInFolder FileArray, strPfad, "*.xlsx", False, LCount
'was gefunden?
If LCount > 0 Then
Application.ScreenUpdating = False
For LCount = LBound(FileArray) To UBound(FileArray)
'Öffnet die Datei
Set oWBEx = Workbooks.Open(FileArray(LCount), ReadOnly:=True)
'Kopiert von den Zeilen 2 bis zum Ende wenn ab Zeile 2 was vorhanden
'aktive Tabelle in dieser Datei
With ThisWorkbook.ActiveSheet
'nächste freie Zelle
Set rngNextCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
'erste Tabelle aus der externen Datei
With oWBEx.Worksheets(1)
'letzte belegte Zelle
MaxRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'letzte belegte Zeile ist nicht Zeile 1
If MaxRow > 1 Then
'Daten kopieren
.Range("A2", .Cells(MaxRow, 1)).EntireRow.Copy rngNextCell
End If
'Datei schließen
oWBEx.Close False
End With 'oWBEx.Worksheets(1)
End With 'ThisWorkbook.ActiveSheet
Next LCount
Application.ScreenUpdating = True
End If
End Sub

Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
AW: VBA Dateien(alle Tab) aus Pfad in Datei kopieren
12.07.2012 15:34:31
fcs
Hallo Carmen,
dazu muss man das Makro etwas anders strukturieren und die Tabellenbllätter in einer Schleife abarbeiten.
Ich hab den Code mal angepasst, aber Mangels Datenbasis natürlich nicht getestet. Ich hoffe das es funktioniert.
Gruß
Franz
Sub Dateien_in_eine_Tabelle_zusammenfuehren()
Dim strPfad As String
Dim oWBEx As Workbook, oShEx As Worksheet, oShZiel As Worksheet
Dim rngNextCell As Range
Dim FileArray()
Dim LCount As Long, MaxRow As Long
'Pfad angepasst
strPfad = "C:\Dokumente und Einstellungen\Eigene Dateien\Periode\2011\01 Januar\"
'Suche Dateien im Ordner
ListFilesInFolder FileArray, strPfad, "*.xlsx", False, LCount
'was gefunden?
If LCount > 0 Then
Application.ScreenUpdating = False
Set oShZiel = ActiveSheet 'oder = ThisWorkbook.ActiveSheet
For LCount = LBound(FileArray) To UBound(FileArray)
'Öffnet die Datei
Set oWBEx = Workbooks.Open(FileArray(LCount), ReadOnly:=True)
With oWBEx
'Kopiert von den Zeilen 2 bis zum Ende wenn ab Zeile 2 was vorhanden
For Each oShEx In .Worksheets 'Tabellen aus der externen Datei
With oShEx
'letzte belegte Zelle
MaxRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'letzte belegte Zeile ist nicht Zeile 1
If MaxRow > 1 Then
With oShZiel 'aktive Tabelle in dieser Datei
'nächste freie Zelle
Set rngNextCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With 'ThisWorkbook.ActiveSheet
'Daten kopieren
.Range("A2", .Cells(MaxRow, 1)).EntireRow.Copy rngNextCell
End If
End With 'oshex
Next oShEx
'Datei schließen
oWBEx.Close False
End With 'oWBEx
Next LCount
Application.ScreenUpdating = True
End If
End Sub

Anzeige
AW: VBA Dateien(alle Tab) aus Pfad in Datei kopieren
12.07.2012 15:40:52
Carmen
Franz, großartig es funktioniert perfekt!!!
Tausend Dank und einen traumhaften Tag!!
AW: VBA Dateien(alle Tab) aus Pfad in Datei kopieren
12.07.2012 17:18:56
Carmen
Hallo Franz,
nun wollte ich einfügen dass nur die Werte eingefügt werden durch value, aber dann gibt es einen Objektfehler.
Ich würde das wirklich gerne selber können....komm aber leider nicht weiter!
Kann mir vielleicht noch jemand sagen, warum es die Einführung VBA mit dem Link von Wikibooks nicht zum Download gibt?
Vielen Dank schon jetzt!
Carmen
Anzeige
AW: VBA Dateien(alle Tab) aus Pfad in Datei kopieren
13.07.2012 07:26:04
fcs
Hallo Carmen,
wenn man selektiv Inhalte einfügen will, dann sollte man mit der PasteSpecial-Methode arbeiten.
Link für Download für Offline-Version von VBA in Excel: https://www.herber.de/materialien/vbainexcel.zip
Gruß
Franz
                  'Daten kopieren
.Range("A2", .Cells(MaxRow, 1)).EntireRow.Copy
'                  rngNextCell.PasteSpecial Paste:=-4122 'xlPasteFormats
rngNextCell.PasteSpecial Paste:=-4163 'xlPasteValues
Application.CutCopyMode = False

Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

VBA Dateien aus Pfad in eine neue Datei kopieren


Schritt-für-Schritt-Anleitung

Um alle Tabellenblätter aus mehreren Excel-Dateien in eine neue Datei zu kopieren, kannst du das folgende VBA-Makro verwenden. Dieses Skript öffnet die Dateien in einem angegebenen Pfad und kopiert die Inhalte von jedem Tabellenblatt in die aktive Tabelle deiner neuen Datei.

  1. Öffne Excel und drücke ALT + F11, um den VBA-Editor zu öffnen.
  2. Füge ein neues Modul ein: Klicke auf Einfügen > Modul.
  3. Kopiere den folgenden Code in das Modul:
Sub Dateien_in_eine_Tabelle_zusammenfuehren()
    Dim strPfad As String
    Dim oWBEx As Workbook, oShEx As Worksheet, oShZiel As Worksheet
    Dim rngNextCell As Range
    Dim FileArray()
    Dim LCount As Long, MaxRow As Long
    'Pfad angepasst
    strPfad = "C:\Dokumente und Einstellungen\Eigene Dateien\Periode\2011\01 Januar\"
    'Suche Dateien im Ordner
    ListFilesInFolder FileArray, strPfad, "*.xlsx", False, LCount
    'was gefunden?
    If LCount > 0 Then
        Application.ScreenUpdating = False
        Set oShZiel = ActiveSheet 'oder = ThisWorkbook.ActiveSheet
        For LCount = LBound(FileArray) To UBound(FileArray)
            'Öffnet die Datei
            Set oWBEx = Workbooks.Open(FileArray(LCount), ReadOnly:=True)
            With oWBEx
                For Each oShEx In .Worksheets 'Tabellen aus der externen Datei
                    With oShEx
                        'letzte belegte Zelle
                        MaxRow = .Cells(.Rows.Count, 1).End(xlUp).Row
                        'letzte belegte Zeile ist nicht Zeile 1
                        If MaxRow > 1 Then
                            With oShZiel 'aktive Tabelle in dieser Datei
                                'nächste freie Zelle
                                Set rngNextCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
                            End With 'ThisWorkbook.ActiveSheet
                            'Daten kopieren
                            .Range("A2", .Cells(MaxRow, 1)).EntireRow.Copy rngNextCell
                        End If
                    End With 'oShEx
                Next oShEx
                'Datei schließen
                oWBEx.Close False
            End With 'oWBEx
        Next LCount
        Application.ScreenUpdating = True
    End If
End Sub
  1. Schließe den VBA-Editor und gehe zurück zu Excel.
  2. Führe das Makro aus, um die Daten erfolgreich zu kopieren.

Häufige Fehler und Lösungen

  • Fehler: "Objektfehler" beim Einfügen von Werten
    • Stelle sicher, dass du die PasteSpecial-Methode verwendest, um nur die Werte einzufügen. Beispiel:
rngNextCell.PasteSpecial Paste:=xlPasteValues
  • Fehler: Kein Daten gefunden
    • Überprüfe den Dateipfad. Stelle sicher, dass der strPfad korrekt ist und die Dateien im angegebenen Ordner vorhanden sind.

Alternative Methoden

Eine alternative Methode besteht darin, den Pfad einer Datei zu kopieren und diesen als Link in die neue Datei einzufügen. Hierzu kannst du das Hyperlink-Objekt in VBA verwenden, um die Datei und den Pfad als Link zu speichern.

ActiveSheet.Hyperlinks.Add Anchor:=rngNextCell, Address:=FileArray(LCount), TextToDisplay:="Link zur Datei"

Praktische Beispiele

Wenn du nur Daten ohne Formatierungen kopieren möchtest, kannst du die PasteSpecial-Methode wie folgt verwenden:

' Daten kopieren ohne Formatierungen
rngNextCell.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Das obige Beispiel zeigt, wie du nur die Werte von einem Tabellenblatt in die aktive Tabelle einfügst, was besonders nützlich ist, wenn du nur die Daten ohne Formatierungen benötigst.


Tipps für Profis

  • Speichere die Dateien regelmäßig, um Datenverlust zu vermeiden, besonders wenn du mit großen Datenmengen arbeitest.
  • Nutze die Möglichkeit, den Pfad als Link zu kopieren, um später direkt auf die Quelldateien zugreifen zu können, was die Verwaltung und Nachverfolgbarkeit der Daten erleichtert.

FAQ: Häufige Fragen

1. Wie kann ich den Pfad einer Datei kopieren?
Du kannst den Pfad einer Datei kopieren, indem du mit der rechten Maustaste auf die Datei klickst und "Pfad kopieren" wählst.

2. Funktioniert das Makro in allen Excel-Versionen?
Das Makro sollte in den meisten modernen Excel-Versionen funktionieren, insbesondere ab Excel 2010. Überprüfe jedoch die VBA-Einstellungen, um sicherzustellen, dass alle erforderlichen Berechtigungen aktiviert sind.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige