Alle Excel-Files eines Verzeichnisses

Alle *.xls* + *.csv + weitere in eine Liste oder Array schreiben

Mitunter ist es wünschenswert, dass alle Excel-Dateien und/oder alle von Excel verarbeitbaren Dateien (dazu gehören beispielsweise auch *.csv, *.txt, *.dat) in einem Array oder einer Liste eines Tabellenblattes erfasst werden. Das ist mit ein wenig Code durchaus machbar. Wir stellen Ihnen hier drei Möglichkeiten vor; zwei davon ähneln sich stark, sind nur etwas anders in der Auswahl der Dateiendungen programmiert.

Grundsätzlich geht es darum, alle Dateien eines (Unter-) Verzeichnisses in einer Liste oder einem Array zu erfassen, welche eine per Code definierte Dateiendung haben. Im Beispielcode sind das die typischen Excel-Dateien (aber nicht alle, es fehlen beispielsweise *.xlt und *.xla) und die CSV-Dateien; diese haben zwar ein Excel-Symbol in der File-Darstellung, sind aber reine Textdateien. Bei Bedarf können auch andere Dateitypen mit erfasst werden, beispielsweise *.txt, *.prn, *.dat. Es wird nur die eine, die aktuelle Ebene ausgewertet, also keine Unterverzeichnisse, welche eventuell innerhalb dieses Folders existieren.

1. Als Liste direkt in ein Worksheet

Vorab angemerkt: Es wird eher eine Auflistung in das Arbeitsblatt geschrieben, keine „echte“ Liste, also keine Intelligente Tabelle. Es obliegt Ihnen, wie Sie die ausgegebenen Daten dann strukturieren.

Sub FilesInFolder2sheet()
   Dim oFs As Object
   Dim oVerz As Object, oDatei As Object, oFiles As Object
   Dim cDat As String
   Dim Ze As Long, cExt As String
   
   Set oFs = CreateObject("scripting.FileSystemObject")
   Set oVerz = oFs.GetFolder("C:\Temp\")  'Anpassen
   Set oFiles = oVerz.Files
   
   For Each oDatei In oFiles
      If InStr(oDatei, "") > 0 Then
         cExt = oFs.GetExtensionName(oDatei)
         Select Case LCase(cExt)
         Case "xlsx", "xlsm", "xlsb", "xlb", "xls", "csv"   'Anpassen
            Ze = Ze + 1
            Cells(Ze, 1) = oDatei.Name
          End Select
      End If
   Next oDatei
End Sub

Hier muss natürlich das auszulesende Verzeichnis geändert werden und eventuell die Dateitypen. Soll die Ausgabe nicht in A1 beginnen, geben Sie unterhalb der drei Set-Anweisungen beispielsweise diese Code-Zeile ein:

Ze = 2

wenn die Ausgabe in der dritten(!) Zeile beginnen soll; und um beispielsweise die Ausgabe in Spalte C durchzuführen, wird die entsprechende Zeile so geändert:

'Cells(Ze, 1) = oDatei.Name
Cells(Ze, 3) = oDatei.Name  '3. Spalte = Spalte C

▲ nach oben …

2. Die gefundenen Files in ein Array schreiben

Hier bieten wir Ihnen zwei Varianten an, welche sich marginal unterscheiden. Der kleine aber feine Unterschied liegt in der Definition der File-Typen. Im ersten aufgeführten Beispiel FilesInFolder2Array() wird jeder Dateityp explizit angegeben. Im gezeigten Code wird der Dateityp *.xlst nicht mit erfasst. Im zweiten Code FilesInFolder2Array2() wird durch etwas „Trickserei“ mit Jokern gearbeitet, die ja eigentlich bei einer Case-Selektion nicht möglich sind.

Version 1:

Sub FilesInFolder2Array()
   Dim oFs As Object
   Dim oVerz As Object, oDatei As Object, oFiles As Object
   Dim cDat As String
   Dim Z As Long, cExt As String, aFiles(), rng As Range
   
   Set oFs = CreateObject("scripting.FileSystemObject")
   Set oVerz = oFs.GetFolder("C:\Temp\")
   Set oFiles = oVerz.Files
   Set rng = Range("C5")   'Start für die Ausgabe, anpassen
   
   For Each oDatei In oFiles
      If InStr(oDatei, "") > 0 Then
         cExt = oFs.GetExtensionName(oDatei)
         Select Case LCase(cExt)
         Case "xlsx", "xlsm", "xlsb", "xlb", "xls", "csv"
            Z = Z + 1
            ReDim Preserve aFiles(Z)
            aFiles(Z) = oDatei.Name
          End Select
      End If
   Next oDatei
   If Z > 0 Then rng.Resize(UBound(aFiles), 1) = WorksheetFunction.Transpose(aFiles)
End Sub

Version 2:

Sub FilesInFolder2Array2()
   Dim oFs As Object
   Dim oVerz As Object, oDatei As Object, oFiles As Object
   Dim cDat As String
   Dim Z As Long, cExt As String, aFiles(), rng As Range
   
   Set oFs = CreateObject("scripting.FileSystemObject")
   Set oVerz = oFs.GetFolder("C:\Temp\")
   Set oFiles = oVerz.Files
   Set rng = Range("F5")   'Start für die Ausgabe, anpassen
   
   For Each oDatei In oFiles
      If InStr(oDatei, "") > 0 Then
         cExt = oFs.GetExtensionName(oDatei)
         Select Case LCase(cExt)
         Case "xls" To "xlsÿ", "csv"   'ÿ = chr(255) | wirkt wie xls*, genauer gesagt: wie *.xls?
            Z = Z + 1
            ReDim Preserve aFiles(Z)
            aFiles(Z) = oDatei.Name
          End Select
      End If
   Next oDatei
   If Z > 0 Then rng.Resize(UBound(aFiles), 1) = WorksheetFunction.Transpose(aFiles)
End Sub

Wie Sie mit den Daten des Arrays weiter arbeiten, bleibt Ihnen überlassen. Im Beispielcode wurde erst überprüft, ob überhaupt Daten erfasst worden sind und der Inhalt des Arrays wird dann an unterschiedlichen Positionen in das aktuelle Arbeitsblatt geschrieben.

In den meisten Fällen ist es angebracht, den gewünschten Code in das Modul des gewünschte, des zu füllenden WorkSheets zu schreiben. Eine Datei mit allen drei Codes können Sie hier herunterladen. Denken Sie vor dem Test jedoch daran, entweder ein Verzeichnis C:\Temp\ anzulegen und mit genügend Testdaten zu füllen oder im Code den Pfad Ihren Gegebenheiten entsprechend anzupassen.

▲ nach oben …

Dieser Beitrag wurde unter Daten-Import / -Export, Mit VBA/Makro, Musterlösungen, Tipps und Tricks, Verschiedenes abgelegt und mit , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.