Bestimmte Zellen aus mehreren Excel-Dateien auslesen
Schritt-für-Schritt-Anleitung
Um bestimmte Zellen aus mehreren Excel-Dokumenten auszulesen, kannst Du das folgende VBA-Makro verwenden. Dieses Makro öffnet die Dateien in einem ausgewählten Verzeichnis, liest die Werte aus einem bestimmten Blatt und speichert diese in einer neuen Excel-Datei.
- Öffne Excel und drücke
ALT + F11, um den VBA-Editor zu starten.
- Füge ein neues Modul hinzu: Rechtsklick auf "VBAProject (DeinWorkbookName)" > Einfügen > Modul.
- Kopiere den folgenden Code in das Modul:
Sub alle_Dateien_Verzeichnis()
Dim dlg As FileDialog
Dim StatusCalc
Dim varItem, Ext$, Datei$
Dim wkbNeu As Workbook, wkbQuelle As Workbook
Dim wksNeu As Worksheet, wksQuelle As Worksheet, LR
On Error GoTo Fehler
With Application
.ScreenUpdating = False
.EnableEvents = False
StatusCalc = .Calculation
.Calculation = xlCalculationManual
End With
Set dlg = Application.FileDialog(msoFileDialogFolderPicker) 'Verzeichnis wählen
If dlg.Show = True Then
Set wkbNeu = Application.Workbooks.Add(Template:=xlWBATWorksheet)
Set wksNeu = wkbNeu.Worksheets(1)
With wksNeu
.Cells(1, 1) = "Verzeichnis:"
.Cells(3, 1) = "Dateiname"
.Cells(3, 2) = "Wert 1"
.Cells(3, 3) = "Wert 2"
.Cells(3, 4) = "Wert 3"
End With
For Each varItem In dlg.SelectedItems
Ext = "*.xls*"
Datei = Dir(varItem & "\" & Ext)
Do While Datei <> ""
If LCase(Datei) = LCase(ThisWorkbook.Name) Then GoTo NextDatei
Set wkbQuelle = Workbooks.Open(Filename:=varItem & "\" & Datei, ReadOnly:=True, UpdateLinks:=False)
Set wksQuelle = wkbQuelle.Sheets("Tabelle1") 'Tabelle aus der gelesen wird
With wksNeu
LR = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
.Cells(LR, 1) = wkbQuelle.Name
.Cells(LR, 2) = wksQuelle.Range("A1")
.Cells(LR, 3) = wksQuelle.Range("B2")
.Cells(LR, 4) = wksQuelle.Range("B3")
End With
wkbQuelle.Close SaveChanges:=False
Set wkbQuelle = Nothing
NextDatei:
Datei = Dir()
Loop
wksNeu.Cells(1, 2) = varItem
Next
With wksNeu
.Columns.AutoFit
End With
End If
Fehler:
With Err
Select Case .Number
Case 0
Case 1004
If Not wkbQuelle Is Nothing Then wkbQuelle.Close SaveChanges:=False
Resume NextDatei
Case -2147221080
If Not wkbQuelle Is Nothing Then wkbQuelle.Close SaveChanges:=False
Resume NextDatei
Case Else
MsgBox "Fehler-Nr.: " & .Number & vbLf & .Description, vbOKOnly, "Fehler-Makro"
End Select
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = StatusCalc
End With
End Sub
- Schließe den VBA-Editor und kehre zu Excel zurück.
- Führe das Makro aus: Drücke
ALT + F8, wähle alle_Dateien_Verzeichnis und klicke auf "Ausführen".
Häufige Fehler und Lösungen
-
Fehler 1004: Dieser Fehler tritt auf, wenn das Makro versucht, auf eine Datei zuzugreifen, die nicht geöffnet werden kann. Stelle sicher, dass die Dateien im gewählten Verzeichnis nicht schreibgeschützt sind.
-
Automatisierungsfehler: Dieser Fehler kann auftreten, wenn die Excel-Anwendung nicht richtig reagiert. Überprüfe, ob andere Excel-Dateien geöffnet sind, und schließe sie, bevor Du das Makro ausführst.
Alternative Methoden
Wenn Du kein VBA verwenden möchtest, kannst Du auch Power Query nutzen, um Daten aus mehreren Excel-Dateien zu konsolidieren:
- Gehe zu Daten > Abrufen und transformieren > Daten abrufen > Aus Datei > Aus Ordner.
- Wähle den Ordner, der die Excel-Dateien enthält.
- Wähle die gewünschten Dateien aus und lade die Daten in eine neue Arbeitsmappe.
Praktische Beispiele
Angenommen, Du hast mehrere Excel-Dateien, und in jeder Datei steht in Zelle A1 der Umsatz eines Produkts. Mit dem obigen VBA-Code kannst Du alle Umsätze in einer neuen Datei zusammenfassen.
Wenn Du die Werte in den Zellen A1, B2 und B3 auslesen möchtest, kannst Du die Zeilen im Makro anpassen, um andere Zellen zu referenzieren.
Tipps für Profis
- Benutze Fehlerbehandlung im VBA-Code, um sicherzustellen, dass Dein Makro auch bei unerwarteten Fehlern robust bleibt.
- Dokumentiere Deinen Code gut, um die Wartung zu erleichtern, insbesondere bei komplexeren Makros.
- Experimentiere mit Array-Variablen, um die Leistung beim Verarbeiten großer Datenmengen zu verbessern.
FAQ: Häufige Fragen
1. Kann ich das Makro auch für andere Excel-Versionen verwenden?
Ja, dieses Makro sollte in den meisten Excel-Versionen ab 2010 funktionieren, die VBA unterstützen.
2. Was ist, wenn ich die Zellen A1, B2 und B3 in jeder Datei nicht finde?
Das Makro wird einen Fehler werfen. Du kannst das Makro anpassen, um sicherzustellen, dass die Zellen existieren, bevor Du auf sie zugreifst.
3. Wie kann ich den Blattnamen ändern?
Ändere die Zeile Set wksQuelle = wkbQuelle.Sheets("Tabelle1") und gib den neuen Blattnamen an.
4. Wie lange dauert es, 250 Dateien zu verarbeiten?
Die Verarbeitungszeit hängt von der Größe der Dateien und der Anzahl der Zellen ab, die ausgelesen werden. In der Regel sollte es jedoch nur wenige Minuten in Anspruch nehmen.