Mitunter ist es wün­schenswert, dass alle Excel-Dateien und/oder alle von Excel ver­ar­beit­baren Dateien (dazu gehören beispiel­sweise auch *.csv, *.txt, *.dat) in einem Array oder ein­er Liste eines Tabel­len­blattes erfasst wer­den. Das ist mit ein wenig Code dur­chaus mach­bar. Wir stellen Ihnen hier drei Möglichkeit­en vor; zwei davon ähneln sich stark, sind nur etwas anders in der Auswahl der Dateien­dun­gen pro­gram­miert.

Grund­sät­zlich geht es darum, alle Dateien eines (Unter-) Verze­ich­niss­es in ein­er Liste oder einem Array zu erfassen, welche eine per Code definierte Dateien­dung haben. Im Beispiel­code sind das die typ­is­chen Excel-Dateien (aber nicht alle, es fehlen beispiel­sweise *.xlt und *.xla) und die csv-Dateien; diese haben zwar ein Excel-Sym­bol in der File-Darstel­lung, sind aber reine Text­dateien. Bei Bedarf kön­nen auch andere Date­itypen mit erfasst wer­den, beispiel­sweise *.txt, *.prn, *.dat. Es wird nur die eine, die aktuelle Ebene aus­gew­ertet, also keine Unter­verze­ich­nisse, welche eventuell inner­halb dieses Fold­ers existieren.

1. Als Liste direkt in ein WorkSheet

Vor­ab ange­merkt: Es wird eher eine Auflis­tung in das Arbeits­blatt geschrieben, keine „echte” Liste, also keine Intel­li­gente Tabelle. Es obliegt Ihnen, wie Sie die aus­gegebe­nen Dat­en dann struk­turi­eren.

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ür­lich das auszule­sende Verze­ich­nis geän­dert wer­den und eventuell die Date­itypen. Soll die Aus­gabe nicht in A1 begin­nen, geben Sie unter­halb der drei Set-Anweisun­gen beispiel­sweise diese Code-Zeile ein:

Ze = 2

wenn die Aus­gabe in der drit­ten(!) Zeile begin­nen soll; und um beispiel­sweise die Aus­gabe in Spalte C durchzuführen, wird die entsprechende Zeile so geän­dert:

'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 Vari­anten an, welche sich mar­gin­al unter­schei­den. Der kleine aber feine Unter­schied liegt in der Def­i­n­i­tion der File-Typen. Im ersten aufge­führten Beispiel FilesInFolder2Array() wird jed­er Date­ityp expliz­it angegeben. Im gezeigten Code wird der Date­ityp *.xlst nicht mit erfasst. Im zweit­en Code FilesInFolder2Array2() wird durch etwas „Trick­serei” mit Jok­ern gear­beit­et, die ja eigentlich bei ein­er Case-Selek­tion nicht möglich sind.

Ver­sion 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

Ver­sion 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 Dat­en des Arrays weit­er arbeit­en, bleibt Ihnen über­lassen. Im Beispiel­code wurde erst über­prüft, ob über­haupt Dat­en erfasst wor­den sind und der Inhalt des Arrays wird dann an unter­schiedlichen Posi­tio­nen in das aktuelle Arbeits­blatt geschrieben.

In den meis­ten Fällen ist es ange­bracht, den gewün­scht­en Code in das Mod­ul des gewün­schte, des zu fül­len­den Work­Sheets zu schreiben. Eine Datei mit allen drei Codes kön­nen Sie hier herun­ter­laden. Denken Sie vor dem Test jedoch daran, entwed­er ein Verze­ich­nis C:\Temp\ anzule­gen und mit genü­gend Test­dat­en zu füllen oder im Code den Pfad Ihren Gegeben­heit­en entsprechend anzu­passen.

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,00 € freuen … (← Klick mich!)