Dateinamen eines Verzeichnisses mit VBA auslesen und in Excel schreiben

Mit einem kleinen Makro können Sie ganz bequem sämtliche Dateinamen inklusive der Dateiendungen in Excel schreiben. Das nachfolgende Makro brauchen Sie hierfür lediglich in ein Modul Ihrer Exceldatei hinzufügen. Alternativ können Sie auch die im Anschluss an diesen Beitrag anghängte Musterdatei nutzen um Dateinamen mit VBA auslesen zu können.

Bevor Sie starten gibt es zwei Wichtige Punkte zu beachten. Vor dem Ausführen des Makros müssen Sie den Ordnername im VBA Code noch abhändern. Im Beispiel würde das Makro ansonsten im Laufwerk “C:\” den Ordner mit dem Namen “Ordnername” suchen und falls vorhanden die Dateien dieses Ordners in Spalte A ab Zeile 1 auflisten. Sofern der Ordnerpfad nicht vorhanden ist, erhalten Sie die Fehlermeldung “Laufzeitfehler ’76’: Pfad nicht gefunden”.

Laufzeitfehler 76

Dateinamen mit VBA auslesen

Achten Sie zudem darauf, dass in der Tabelle keine Inhalte in Spalte A vorhanden sind, da diese sonst ggf. überschrieben werden! Möchten Sie die Dateinamen nicht schon ab Zeile 1, sondern z.B. erst ab Zeile 5 schreiben. Ändern Sie im VBA Code zudem den Code von

lngZeile = 1

auf

lngZeile = 5

ab.

Der gesamte Quellcode für dieses Makro lautet wie folgt:

Sub DateienAuflisten()

Dim lngZeile As Long
Dim objFileSystem As Object
Dim objVerzeichnis As Object
Dim objDateienliste As Object
Dim objDatei As Object

Set objFileSystem = CreateObject("scripting.FileSystemObject")
Set objVerzeichnis = objFileSystem.GetFolder("C:\Ordnername")
Set objDateienliste = objVerzeichnis.Files

lngZeile = 1

For Each objDatei In objDateienliste
     If Not objDatei Is Nothing Then
          ActiveSheet.Cells(lngZeile, 1) = objDatei.Name
          lngZeile = lngZeile + 1
     End If
Next objDatei

End Sub

Möchten Sie zudem nicht alle Dateinamen in Excel schreiben, können Sie auch anhand einer weiteren kleinen Modifikation bestimmte Dateiendungen oder Dateinamen ausschließen. Ändern Sie hierfür die Zeile mit dem VBA Code:

If Not objDatei Is Nothing Then

in

If Not objDatei Is Nothing And Right(objDatei.Name, 4) = ".jpg" Then

ab.

Die Ergänzung And Right(objDatei.Name, 4) = “.jpg” führt zu der Einschränkung, dass nur Dateien mit der Endung “.jpg” in Excel geschrieben werden. Möchten Sie alle Dateinamen außer einer bestimmten Endung in Excel schreiben, fügen Sie noch den Befehl “Not” hinzu. Beispiel: And Not Right(objDatei.Name, 4) = “.jpg”

Das hier vorgestellte Makro liest nur die Dateinamen des aktuellen Ordners aus. Die Namen von Unterordnern und darin liegender Dateien werden nicht berücksichtigt. Die Dateien von Unterordnern können selbstverständlich grds. auch berücksichtigt werden. In diesem Fall ist dann jedoch eine etwas umfangreichere Änderung am VBA Quellcode erforderlich.

Dateien aus Unterverzeichnissen auslesen

Um auch die Dateinamen aus Unterordnern auslesen zu können ist eine zusätzliche Prozedur erforderlich, die für jeden einzelnen Unterordner aufgerufen wird. Weil in diesem Beispiel die Dateien des Hauptordners nicht berücksichtigt werden, sind die beiden Makros ggf. zu kombinieren. Rufen Sie hierzu die zweite Prozedur am Ende des ersten Makros über den Befehl

Call UnterOrdnerAuslesen(objVerzeichnis)

auf. Über diese Programmzeile wird dann benötigte zusätzliche VBA Prozedur aufgerufen.

Sub UnterOrdnerAuslesen(ByVal strDateipfad As String)

Dim objFileSystem As Object
Dim objVerzeichnis As Object
Dim objUnterordner As Object
Dim objDatei As Object
Dim i As Long
  
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objVerzeichnis = objFileSystem.getfolder(strDateipfad)
  
If Cells(Rows.Count, 1).End(xlUp).Row > 1 Then
    i = Cells(Rows.Count, 1).End(xlUp).Row + 1
Else
    i = 1
End If
 
For Each objUnterordner In objVerzeichnis.subfolders
    For Each objDatei In objUnterordner.Files
        If Not objDatei Is Nothing And Not Right(objDatei.Name, 4) = ".jpg" Then
            ActiveSheet.Cells(i, 1) = objDatei.Name
            ActiveSheet.Cells(i, 2) = objUnterordner.Path
            i = i + 1
        End If
    Next objDatei
    Call UnterOrdnerAuslesen(objUnterordner.Path)
Next objUnterordner
     
End Sub

Excel Mustermappe:

Dateinamen in Excel auslesendateien_auflisten.xlsm

 
Gefällt Ihnen der Beitrag?

36 Gedanken zu “Dateinamen eines Verzeichnisses mit VBA auslesen und in Excel schreiben

  1. Hallo,
    ich möchte statt Endung “.jpg” ein TextBox haben (zum Beispiel “textbox1”) In TextBox werde ich der Name eintippen, danach folgt die Suche. Ich suche immer nach verschiedene Dateien, meistens sind PDF.
    VG

    • @Ilario: Dazu benötigen Sie eine zusätzliche Variable in der Sie den Wert aus einer Inputbox speichern und dann in der If-Bedingungen übergeben.

      zusätzlich also diese beiden Zeilen:

      Dim strDateiendung As String
      strDateiendung = InputBox(“Dateiendung eingeben:”)

      und zudem diese Zeile anpassen:

      If Not objDatei Is Nothing And Right(objDatei.Name, 4) = strDateiendung Then

  2. Hallo,
    gibt es auch die Möglichkeit das Makro so abzuändern, dass auch Dateigröße und Datum der letzten Änderung ausgelesen werden können?

    • @Dietmar: Ja, das geht. Mit den folgenden zusätzlichen Zeilen können weitere Informationen abgerufen werden.

      ActiveSheet.Cells(lngZeile, 2) = objDatei.DateCreated ‘Erstellungsdatum
      ActiveSheet.Cells(lngZeile, 3) = objDatei.DateLastModified ‘letztes Änderungsdatum
      ActiveSheet.Cells(lngZeile, 4) = objDatei.DateLastAccessed ‘letzter Zugriff
      ActiveSheet.Cells(lngZeile, 5) = objDatei.Size ‘Größe
      ActiveSheet.Cells(lngZeile, 6) = objDatei.Type ‘Dateityp

      • Hallo Marco
        ich hatte vor Jahren 2013 die gleich Fragestellung “Verzeichnisse und Unterverzeichnisse” nach Excel einlesen, damit ich sie dort lesen, drucken etc. kann. Es gab keine komplette Lösung in den Foren.
        Daraufhin habe ich mit VBA beigebracht
        Sende mir Deine eMail Adresse und ich sende Dir das Excel VBA Tool zurück

  3. Hallo zusammen,
    ich finde es echt super das hier Profis so Anfänger wie mir alles super erklären und noch beispiele anhängen!
    Gibt es auch ein Beispiel wo die Unterordenr berücksichtigt weren?
    Danke

  4. Hallo. Sehr interessantes Thema. Wie würde der Code aussehen, wenn ich das “Laufwerk” über eine Inputbox eingeben möchte.
    Danke im Voraus. Viele Grüße.

    • @Andreas: Dazu wäre eine zusätzliche Variable nötig, die mit dem Wert aus der Inputbox gefüllt wird. Anschließend übergibt man den Wert dann dem Objekt objVerzeichnis. Mehr muss hier nicht getan werden.

      strPfad = InputBox(“Bitte Ordnerpfad eingeben:”)
      Set objVerzeichnis = objFileSystem.GetFolder(strPfad)

  5. Hallo. Sicherlich habe ich einen Denkfehler. Ich möchte den Pfad zum Auslesen eines Ordners über eine Inputbox eingeben. Hierzu habe ich den folgenden Code erstellt, welcher aber nicht funktioniert 🙁
    ‘########################
    Sub Einlesen()
    Dim zeile As Variant
    Dim sFile As String, sPattern As String, sPath As String
    Dim iRow As Integer
    Columns(1).ClearContents

    strPfad = InputBox(„Bitte Ordnerpfad eingeben:“)
    Set objVerzeichnis = objFileSystem.GetFolder(strPfad)

    ‘If Right(strPfad, 1) “\” Then strPfad = sPath & “\”
    If Right(strPfad, 1) “\” Then strPfad = strPfad & “\”
    sPattern = “*.*”
    sFile = Dir(strPfad & sPattern) ‘(strPfad & sPattern)
    Do Until strPfad = “” ‘sFile = “”
    iRow = iRow + 1
    Cells(iRow, 1).Value = sFile
    strPfad = Dir()
    Loop
    For zeile = 1 To Cells.SpecialCells(xlLastCell).Row

    Next
    End Sub ‘Bei erneutem Aufruf werden die alten Funde überschrieben

  6. So, ich hab‘s dann doch noch hinbekommen. Dennoch vielen Dank für die vielen Hilfreichen Kommentare in diesem Forum…
    Diesen VBA Code in ein Modul:
    Sub Einlesen()
    Dim zeile As Variant
    Dim sFile As String, sPattern As String, sPath As String
    Dim iRow As Integer
    Columns(1).ClearContents

    Dim sTxt As String
    sTxt = InputBox(“Bitte Eingabe tätigen:”)
    If sTxt = “” Then Exit Sub
    ‘MsgBox sTxt
    Range(“A3”).Value = sTxt

    If Right(sTxt, 1) “\” Then sTxt = sTxt & “\”
    sPattern = “*.*”
    sFile = Dir(sTxt & sPattern)
    Do Until sTxt = “”
    iRow = iRow + 1
    Cells(iRow, 1).Value = sFile
    sFile = Dir()
    Loop
    For zeile = 1 To Cells.SpecialCells(xlLastCell).Row

    Next
    End Sub ‘Bei erneutem Aufruf werden die alten Funde überschrieben

    • @Josh: Allgemein kann man das nicht beantworten. Kommt immer darauf an, was im Makro gemacht wird. Wenn z.B. eine Schleife die Objekt-Variable nutzt, könnte es erst danach die Variable geleert werden. Ganz am Ende eines Makros macht es überhaupt keinen Sinn die Variable zu leeren, denn am Ende des Makros wird die Variable sowieso gelöscht. Es gibt aber natürlich bestimmte Konstellationen, wo eine Leerung sinnvoll ist. Wenn die Objekt-Variable z.B. erst innerhalb einer Schleife mit einem Wert gefüllt wird, und vermieden werden soll, dass ein alter Wert noch in der Variable gespeichert ist.

      Ich leere in meinen Makros die Objekt-Variablen nur, wenn dies wirklich erforderlich ist. Das spart (minimal) Rechenleistung und macht den VBA-Code insgesamt lesbarer. Von unnötig vielen Zeilen sollte grds. abgesehen werden.

  7. Hallo Herr Schade,
    leider übersieht das Programm aus ihrer Vorlage einige Unterordner. Selbst wenn ich einen neuen Ordner anlege findet er ihn und den Inhalt nicht. Es wäre schön wenn sie sich den Code nochmal ansehen könnten.
    (Es sind keine .jpg Dateien in den Ordnern enthalten)

    • @Wolfgang Schmid: Haben Sie den Beitrag eigentlich nicht gelesen? Da sind beide Fragen direkt beantwortet und verschiedene Anpassungsmöglichkeiten beschrieben.

      Im Beispiel wurden Bilddateien mit der Dateiendung “.jpg” ausgeschlossen:

      Die Ergänzung And Right(objDatei.Name, 4) = “.jpg” führt zu der Einschränkung, dass nur Dateien mit der Endung “.jpg” in Excel geschrieben werden. Möchten Sie alle Dateinamen außer einer bestimmten Endung in Excel schreiben, fügen Sie noch den Befehl “Not” hinzu. Beispiel: And Not Right(objDatei.Name, 4) = “.jpg”

      Und auch die Lösung bei Verwendung von Unterordnern ist im Beitrag beschrieben!

      Um auch die Dateinamen aus Unterordnern auslesen zu können ist eine zusätzliche Prozedur erforderlich, die für jeden einzelnen Unterordner aufgerufen wird. Weil in diesem Beispiel die Dateien des Hauptordners nicht berücksichtigt werden, sind die beiden Makros ggf. zu kombinieren.

      Da Sie leider keine Emailadresse angegeben haben, habe ich auch keine Möglichkeit mit Ihnen direkt in Kontakt zu treten.

  8. Hallo,

    danke für die tolle Anleitung. Klappt wunderbar und löst ein riesen Problem. Aber irgendwie nur bis zur ersten Unterordner-Ebene (vermute ich).

    Hat das Makro die maximale Anzahl an Unterordnern des 1. Unterordners (nennen wir ihn Fritz) durchlaufen, hüpft die Schleife zum 2. Unterordner (Max). Die Schleife hat sich hier aber (anscheinend) die Zeilennummer gemerkt bei der Fritz geendet hat und fängt nun an, 1 Zeile weiter die Dateien und Ordner von Max aufzulisten. D.h. die Werte von Fritz werden überschrieben.

    Kann man das Makro noch so anpassen, dass es diesen Umstand berücksichtigt? Ich weiß nämlich, dass ich in einem Ordner > 500 Dateien des Typs X habe, durch dieses Überschreiben jedoch, bekomme ich ein Ergebnis von nur 240 (oder so). Also ein verfälschtes Ergebnis.

    Nochmals danke, a) für die Lösung an sich und b) für dich sicherlich hilfreiche Antwort im Voraus 🙂

    LG Marco

  9. Hm … dann verhält sich mein Excel (Version 2007) anders als das Ihre. Je verschachtelter bzw. je mehr Ebenen eine Ordner-Struktur ist/enthält desto mehr steigt die Gefahr, dass Zeilen überschrieben werden.
    Ich hab das nun aber so gelöst, dass die Abfrage der Zeilennummer innerhalb der zweiten For-Each-Schleife statt findet. Damit ist das Problem für mich behoben und alle Dateien werden angezeigt 🙂

    Trotzdem Danke für die Hilfe und Mühen.

  10. Hallo Marco

    Tolles Makro welches (fast) alles (oder mehr?) Gewünschte ausliest…

    Ich habe einen Ordner mit Musik aus den 60-ern und 11 Unterordner. Erstaunlicherweise habe ich in der ausgelesenen Liste mit deinem angepassten Makro auch 4 “jpg” und 1 “ini” (sonst gut 140 “mp3”). Die 4 “jpg” zeigt es aber auch mit suchen nicht an…

    Jtzt aber das grössere Problem. Wenn ich auch den Zusatzcode mit den wirklich nützlichen Inputboxen genau so hineinschreibe, kommt immer die Meldung
    > Erwartet: Listentrennzeichen oder )!!!

    Hast du einen Tipp oder kann ich dir sogar diese kleine Datei senden?
    Besten Dank für die Hilfe

    Gruss
    Adrian

    PS: Wäre auch die Auflistung sogar mit den Ordnernamen möglich (damit gut sichtbar evtl. mit fetter Schrift)

    • @Adrian: Es handelt sich bei dem bereitgestellten VBA-Code um ein Beispiel. Natürlich kann man dies beliebig ändern und anpassen. Im Beispiel werden Dateien mit der Dateiendung .jpg nicht berücksichtigt. Ist im Beitrag auch erklärt. Kann selbstverständlich geändert werden. Manuelle Auftragsprogrammierungen biete ich gerne an, allerdings handelt es sich hierbei um eine professionelle Dienstleistung, die nicht kostenlos angeboten wird.

  11. Hallo Marco,
    vielen dank für deine Mühen, dass Programm läuft super.
    Was ich nun machen möchte ist, dass die Dateiendungen auch direkt weg sind, was muss ich dafür nun eingeben?

    Vielen Dank
    Gruß
    Mirko

  12. Hallo Marco Schade,

    erst einmal ein unglaublich großes Dankeschön für deine Mühe. Schön, dass es noch Menschen gibt, die anderen helfen (und das sogar noch freiwillig und ohne Bezahlung)!

    Ich habe deinen Code zum auslesen von bestimmten Dateien in einer Ordnerstruktur (mit Unterordnern) soweit umschreiben können, dass auch nur entsprechende aufgelistet werden. Jedoch habe ich nun das Problem, dass zwar in einem bestimmten Ordner auch der Unterordner durchsucht wird, jedoch “eben nur” in diesem einen Unterordner.

    Kann man den Code relativ leicht so umgestalten, dass nach dem Auslesen eines Unterordners wieder zurück in den Ursprungspfad gesprungen wird und von dort aus weitere Unterordner ausgelesen werden?

    Bsp: UrsprungsPfad = C:\Testordner
    – C:\Testordner\OrdnerA\OrdnerA.1
    – C:\Testordner\OrdnerB\OrdnerB.1
    … and so on
    (Dateien in jeweils allen Ordnernebenen vorhanden)

    Ich danke dir und wünsche einen angenehnem Tag.
    Mit freundlichsten Grüßen,
    Sascha

  13. Hallo,

    erste einmal vielen Dank für das Skript. Eine Frage habe ich:
    Set objVerzeichnis = objFileSystem.GetFolder(“C:\Ordnername”)
    Ich möchte gerne den Pfad variabel gestalten. Der Pfad soll um den Namen des aktiven Arbeitsblattes erweitert werden. Also die Excel Datei liegt in C:\Ordnername. Dort gibt es noch die Ordner AAA und BBB. Im Excel File gibt es jetzt das Arbeitsblatt AAA und BBB. Entsprechend soll der Pfad also je nach Arbeitsblatt C:\Ordnername\AAA oder \BBB heißen. Gibt es hier eine Möglichkeit.

    Danke und Gruß

  14. Hallo mal eine Frage. Unter Set objVerzeichnis = objFileSystem.GetFolder(“C:\Ordnername”) gebe ich ja den genauen Pfad an. Ich würde aber gerne den Pfad aus einer Excel Zelle auslesen lassen. So könnte man später einfach die Zelle ändern und müsste nicht dafür noch in die VBA. Hab da nämlich mehrere Arbeitsblätter.
    Danke!!

    • @Florian: Das geht natürlich auch. Innerhalb der Klammer können alternativ zu einem fixen Ordnerpfad auch Variablen oder Zellen angesprochen werden. Variablen bieten sich an, wenn aus unterschiedlichen Arbeitsblättern unterschiedliche Ordnerpfade angesprochen werden sollen.

  15. Hallo, und wie gebe ich das ein? Hatte es z.B. mit Set objVerzeichnis = objFileSystem.GetFolder(“A1”)versucht. Funktionierte aber nicht.

    • Hallo, danke schonmal für die Hilfe :-). Ich möchte nun aber noch eins. Ich habe in einer Tabelle mehrere Arbeitsblätter. Für jedes Arbeitsblatt hab ich das oben stehende Makro mit jeweils unterschiedlichn Verzeichnissen (Blatt1 soll aus Ordner1 die Daten ziehen, Blatt2 aus Ordner2 usw.).
      Ich möchte jetzt das jedes der Makros beim Start der Datei ausgeführt wird.
      Alternativ könnte ich aber auch ein Arbeitsblatt haben wo in Festgelegten Zeilenbereichen die Daten eingetragen werden (Zeile 1 bis 100 die Daten aus Ordner1, Zeile 101 – 201 die Daten aus Ordner2…). Ich hätte damit ja dann nur ein Makro. Dieses müsste sich dann aber auch beim Start aktualisieren.

      Vielen Dank für die Hilfe :-).
      Florian

Schreibe einen Kommentar

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.