Eine Dateiliste in Excel erzeugen 38

Artikelbild-230
Mit einer alten Excel-4-Makrofunktion eine Dateiliste erstellen
 

Manchmal benötigt man in Excel eine Liste aller Dateien eines bestimmten Verzeichnisses. Leider bietet Excel dafür keine eingebaute Funktion. Trotzdem lässt sich diese Aufgabe auf unterschiedlichen Wegen lösen, zum Beispiel per Makroprogrammierung oder mit Hilfe von Power Query („Daten abrufen und Transformieren“).

Im heutigen Artikel zeige ich dir jedoch einen anderen Weg, bei dem wir doch nur ein paar Formeln verwenden werden.

Und so geht’s:

Ein Blick in die Excel-Geschichte

Auch schon bevor Microsoft VBA als Programmiersprache in Excel integriert hat, gab es die Möglichkeit, Makros in Excel zu programmieren. Dazu wurden spezielle Makrofunktionen verwendet, die den bekannten Tabellenfunktionen von der Syntax her sehr ähnlich waren. Und da gab es unter anderem eine Funktion namens DATEIEN, mit der man eben die Dateien eines Verzeichnisses auflisten konnte.

Keine Angst, du musst jetzt hier keine Makros programmieren!

Mit einem kleinen Trick lässt sich diese DATEIEN-Funktion ganz normal in aktuellen Excel-Versionen nutzen, um zur gewünschten Dateiliste zu kommen.

Die Vorbereitung

In unserem Beispiel wollen wir annehmen, dass in Zelle A1 ein Verzeichnisname eingetragen wird, den wir anschließend mit der oben genannten DATEIEN-Funktion auslesen wollen.
Leider ist nicht möglich, diese Funktion in der gewohnten Weise direkt im Arbeitsblatt zu verwenden:

Die Funktion kann nicht direkt verwendet werden

Die Funktion kann nicht direkt verwendet werden

Aber…
…man kann die Funktion in einem definierten Namen verwenden. Legen wir dazu einen neuen Namen an und nennen ihn beispielsweise Dateiliste:
(Menü Formeln | Namen definieren)

Der Trick über den Namensmanager

Der Trick über den Namensmanager

Im Feld „Bezieht sich auf“ geben wir die Formel ein
=Dateien(Tabelle1!$A$1)
Blattname und Zelladresse hängen natürlich davon ab, wo du später den Namen des Verzeichnisses eingeben möchtest. Wichtig ist nur, dass du absolute Bezüge verwendest, daher die Dollarzeichen nicht vergessen!

Wenn, wie in meinem Beispiel, in Zelle A1 der Verzeichnispfad (inkl. der Suchmaske für die gewünschten Dateien) eingegeben ist, enthält nun der Name „Dateiliste“ ein eindimensionales Array aller Dateinamen, die dem Suchmuster entsprechen.

Wie können wir nun alle im Array enthaltenen Namen ausgeben?

Die Lösung

Dazu benötigen wir die INDEX-Funktion. Diese gibt den Wert einer Matrix oder eines Arrays zurück, der durch den zweiten Parameter bestimmt wird. Für den ersten Dateinamen also
=INDEX(Dateiliste;1)

Mit INDEX auf den definierten Namen zugreifen

Mit INDEX auf den definierten Namen zugreifen

Und so könnte man die Liste einfach fortsetzen und lediglich die Indexnummer immer um den Wert 1 erhöhen, bis man schließlich eine Fehlermeldung erhält, wenn die letzte Datei erreicht wurde:

Statische Index-Nummern sind mühsam

Statische Index-Nummern sind mühsam

So sieht übrigens mein Beispielverzeichnis aus:

Das Beispielverzeichnis

Das Beispielverzeichnis

Das ist natürlich etwas umständlich, vor allem, wenn das Verzeichnis sehr viele Dateien enthält. Wesentlich eleganter geht es, wenn statt der statischen Indexnummer die ZEILE-Funktion verwendet wird:
=INDEX(Dateiliste;ZEILE(A1))

Die ZEILE-Funktion liefert immer die Zeilennummer der angegeben Zelle, bei A1 also 1. Wird die Formel nach unten kopiert, dann wird aus A1 eben A2, A3 und so weiter:

Besser: INDEX kombiniert mit ZEILE

Besser: INDEX kombiniert mit ZEILE

Und wenn man das Ganze jetzt noch mit der WENNFEHLER-Funktion umschließt, lässt sich der unschöne #BEZUG!-Fehler auch unterdrücken:

Noch besser: Kombination mit WENNFEHLER

Noch besser: Kombination mit WENNFEHLER

Natürlich kannst du als Suchmuster statt *.xlsx auch *.* (oder nur *) angeben, um sämtliche Dateien angezeigt zu bekommen.

Ein kleiner Wermutstropfen

Die gezeigte Lösung ist also eigentlich ziemlich einfach. Einen kleinen Haken gibt es aber doch:
Da es sich bei der DATEIEN-Funktion eben um eine alte Excel-4-Makrofunktion handelt, muss die Datei auch im Excel-Arbeitsmappe mit Makros (*.xlsm) abgespeichert werden – auch wenn wir eigentlich keine Makros verwendet haben. Ansonsten gibt es beim Speichern eine Fehlermeldung und die Funktion geht verloren:

Fehlermeldung beim Speichern

Fehlermeldung beim Speichern

Sollte das Speichern im Makroformat also in deinem Fall ein Problem sein, musst du auf andere Techniken zurückgreifen, wie zum Beispiel Power Query. In Excel führen meistens mehrere Wege zum Ziel 🙂
 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!
Das könnte dich auch interessieren:
Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Lars Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

38 Gedanken zu “Eine Dateiliste in Excel erzeugen

    • Avatar-Foto
      Martin Weiß

      Hahaha, manche Aufgaben sollte man tatsächlich besser löschen!
      Vielen Dank für den Hinweis, wird gleich korrigiert.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Mim

    Das ist jetzt mal ’ne richtig g’schickte Funktion, die sich auch im täglichen Leben echt gewinnbringend einsetzen läßt!

    Das XLSM-Format ist kein Problem, benutze ich vorsorglich ohnehin bei fast allen meinen Dateien, freu mich nur schon auf die verdutzten Gesichter meiner Kollegen, die dieses leistungsfähige Makro dann nicht finden

  • Avatar-Foto
    Bernd M.

    Wie üblich hat mich der Newsletter wieder mal zum Exceln gebracht und ich habe gleich mal etwas rumgespielt.
    An dieser Stelle mal ein großes DANKE für die ganzen Anregungen die ich im Laufe der Zeit bekommen habe.

    Statt der Formel „=WENNFEHLER(INDEX(Dateiliste;ZEILE(A1));““) ersetze ich die Funktion „Zeile(A1)“ einfach durch „Zeile()-1“ oder ziehe einen anderen Wert ab, je nachdem, in welcher Zeile die Formel eingefügt wird (Start soll ja immer mit 1 sein).

    Was mir hier aber noch gefehlt hat, war ein Hyperlink um die Datei dann auch direkt aufrufen zu können:
    =HYPERLINK(LINKS($A$1;FINDEN(„#“;WECHSELN($A$1;“*“;“#“;LÄNGE($A$1)-LÄNGE(WECHSELN($A$1;“*“;““)))))&$D2;$D2)

    >> Zelle A1 = Dateipfad
    >> Zelle D2 = eine der gelisteten Dateien

    Aber was wäre ein Hyperlink in einem solchen Zusammenhang ohne Prüfung, ob die Datei noch existiert, deshalb musste auch noch ein kleines Makro her um diesen Link zu überprüfen und ggf. zu löschen:

    Sub Hyperlinkprüfung()
    For i = 2 To 200 ‚ Angabe 2 To 200 gibt die Zeilen an
    If Cells(i, 6) = „“ Then Exit Sub ‚ Cells (i, 6) gibt mit Ziffer 6 die sechste Spalte „F“ an
    hyp = „N:\Berechnungshilfen\“ & Cells(i, 6).Value
    If Dir(hyp) = „“ Then Cells(i, 6).Clear
    Next i
    End Sub

    >> „N:\Berechnungshilfen\“ = mein Dateipfad zum Verzeichnis

    • Avatar-Foto
      Martin Weiß

      Hallo Bernd,

      vielen Dank für diese tollen und sinnvollen Ergänzungen. Da wird sich mancher Leser sicher drüber freuen.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Michael B.

    Mit einer *.xlsb sollte es auch funktionieren!
    … die Dateien werden auch noch merklich kleiner.
    Ein wirklich ferner Artikel!
    gruß
    Michael

  • Avatar-Foto
    Lars

    Vielen Dank für diesen Tipp, großartig! 🙂

    Kannst du vielleicht noch nachreichen, wonach Excel die Dateinamen sortiert? Dies scheint völlig willkürlich zu funktionieren und ich finde keine Lösung…

    Vielen Dank und ein schönes Wochenende,
    Lars

    • Avatar-Foto
      Martin Weiß

      Hallo Lars,

      soweit ich es beurteilen kann, findet die Sortierung alphabetisch nach dem Dateinamen statt. Zumindest ist das bei mir der Fall.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Marcel Ulrich

      Hallo Lars,

      wir hatten bisher Tabellen mit ähnlichen Formeln, die auch immer fein alpahabetisch sortierte Dateinamen ausgegeben haben.
      Vor Kurzem wurden unsere Daten auf einen neuen Server gepackt und seit dem ist die Sortierung wie bei dir scheinbar willkürlich.
      Du bist also nicht allein mit deinem Problem. 🙂

      So bald wir herausfinden, welches Sortierkriterium benutzt wird geb ich dir Bescheid, oder vielleicht kann uns jemand mit einer Antwort helfen und meine heiß geliebten Listen retten. 🙂

      Schöne Grüße,

      Marcel

  • Avatar-Foto
    EuroCafe

    Viele Wege führen nach Rom, für mich ist der einfachte Query, denn damit kann man ein komplettes Verzeichnis bis in die unterste Eben auslesen lassen.
    Daten abrufen -> Aus Datei -> Aus Ordner -> Ordnerpfad auswählen und dann kann man die Spalten die Query automatisch gefunden hat noch bearbeiten, teilen oder löschen. Zum Schluss habe ich dann eine neuen Spalte an die Tabellen verknüpft mit dieser Formel
    =HYPERLINK(VERKETTEN(F7;A7)) F7 = Spalte Folder Path und A7 = Spalte Name
    zusammen ergbit dieses einen Link der beim anklicken die Datei öffnet.

    Was ich nicht hinbekommen habe, das man das in einer benutzerdefinierten Spalte direkt in Query anlegen kann und das die Dateigröße als Spalte mit übernommen wird. Warum Query die Spalte nicht lesen kann? Das muss ein Experte erklären.

    Schönen Sonntag
    Gruß EuroCafe

    • Avatar-Foto
      Martin Weiß

      Hallo Armin,

      nein, mit externen Webseiten funktioniert das leider nicht. Man kann damit nur lokale Verzeichnisse auslesen.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Martin Weiß

      Hallo Knatschi,

      leider nein. Es gab mal eine ähnliche Excel-4-Makrofunktion namens „VERZEICHNISSE“, die genau das gemacht hat. Aber die funktioniert leider nicht mehr, da sie zusätzlich ein weiteres Add-In benötigt.

      Schöne Grüße,
      Martin

    • Avatar-Foto
      Martin Weiß

      Hallo Pat,

      #NV! bedeutet entweder, dass in dem angegebenen Verzeichnis keine Datei gefunden wurde, die dem Suchmuster entspricht oder dass das Verzeichnis überhaupt nicht existiert (auf evtl. Tippfehler achten)

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Michael

        Hallo zusammen,

        zum Trotz der wunderbaren Anleitung erscheint bei mir auch nur #NV. Der Pfad ist aus dem Windows-Explorer kopiert, sodass ein Schreibfehler ausgeschlossen werden kann. Es stellt sich die Frage, inwieweit irgendwelche Einstellungen in den Excel-Optionen das Anzeigen verhindern oder gesetzt werden müssen? Und eine Weitere ist, steht die Formel =INDEX(Dateiliste;1) / =INDEX(Dateiliste;ZEILE(A1)), wie oben in der Ansicht in A4 respektive B4 und bleibt A1 fest ($A$1) oder ist der Term veränderlich, sodass in B5 „A2“ steht? Man kann es testen, aber manches Mal dreht man sich bei den einfachsten Dingen im Kreis.
        Grüße von dieser Seite des Bildschirmes, Michael

        • Avatar-Foto
          Martin Weiß

          Hallo Michael,

          hast du die Pfadangabe in Zelle A1 mit einem Backslash und einem Stern abgeschlossen? Ansonsten kommt auf jeden Fall ein #NV-Fehler.
          In den Formeln in Spalte B wird auch auf Zelle A1 verwiesen, da der Pfad nur in A1 eingetragen ist. Ja, ich hätte sinnvollerweise gleich $A$1 schreiben können 🙂

          Schöne Grüße,
          Martin

          • Avatar-Foto
            Michael

            Herr Weiß,

            ich danke Ihnen für den Hinweis. Wenn natürlich kein Suchmuster angegeben ist, wie in meinem Fall, so können auch keine Ergebnisse angezeigt werden.
            Der Rest meiner Fragestellung ist dadurch obsolet. Danke nochmals.

            Grüße, Michael

  • Avatar-Foto
    Peter Bassen

    Super Tipp, hat prima funktioniert. Vielen Dank.
    Ich suche noch nach einer Funktion, wie ich die Anzahl der Seiten der Word-Dateien automatisch zum Dateinamen hinzufügen kann. Im Explorer ist die Info ja vorhanden, aber wie bekomme ich sie nach Excel?

    • Avatar-Foto
      Martin Weiß

      Hallo Peter,

      dieser Informationen lassen sich mit Power Query leider nicht auslesen, da sie Dateityp-spezifisch sind. Man kann nur auf generelle Dateiattribute zugreifen, wie Zeitstempel, Dateigröße etc.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    Wilhelm V.

    Hallo Martin,
    dankeschön für deine tollen Tipps, die ich immer wieder gut brauchen kann. Mal sind es Sachen, die man einfach vergessen hat, dann wieder welche, von denen ich noch nie gehört habe. Aber immer wieder interessant aufgebaut und leicht zu lesen und zu lernen. Danke.
    Ich habe jetzt diese Dateiliste ausprobiert, aber ich kraxle ja immer noch mit Excel 2002 durch die Tabellen und habe wegen der dort noch nicht vorhandenen WENNFEHLER-Funktion so umgestellt:
    =WENN(ISTFEHLER(INDEX(Dateiliste;ZEILE(A1)))=WAHR;““;INDEX(Dateiliste;ZEILE(A1)))
    Aber leider endet meine Liste immer bei 256 Dateien, dann kommt die Fehlermeldung. Liegt das jetzt an meinem Alt-Programm oder gibt es (hoffentlich) noch eine andere Erklärung?
    Freundliche Grüße, Wilhelm

    • Avatar-Foto
      Martin Weiß

      Hallo Wilhelm,

      erst einmal vielen Dank für das schöne Feedback.
      Excel 2002 hat ja schon ein paar Jahre auf dem Buckel und ich vermute tatsächlich, dass die Beschränkung auf 256 Dateien auch daher kommt. Ich kann es mangels dieser Version nicht nachprüfen, aber 256 (bzw. 255) ist bei alten Versionen an vielen Stellen eine Größenbeschränkung, daher vermutlich auch hier.

      Schöne Grüße,
      Martin

  • Avatar-Foto
    boomcat22

    Hallo! Danke sehr für den Tipp. Hat bei mir reibungslos funktioniert.
    Wie kann ich die Inhalte der Unterordner auch auslesen???
    Danke sehr für weitere Hilfe!
    LG

  • Avatar-Foto
    Tobias

    Frage 1: Kann man zb auch einstellen, dass die Dateiendung „Speziell“ sein soll? Also – in meinem gedachten Beispiel geht es darum, dass die Dateiendungen „*.e01“ (und aufsteigend zb bis) „*.e30“ existieren (in VBA hab ich das versucht zu lösen mit gemeintem „das erste Zeichen ist ein ‚e‘, die zwei Zeichen danach müssen numerisch sein“ (um zb auch *.exe“ oder *.eml“ auszuschließen).
    Frage 2: Gibt es eine Funktion, wo man sowas auch inkl Unterordner durchsuchen kann? (also zb „zeige mir alle Ordner im Pfad ‚L:\‘ an“)?

      • Avatar-Foto
        Martin Weiß

        Hallo Tobias,

        nein, solche Spezialfilter sind mit der vorgestellten Funktion nicht möglich, es geht nur * oder ?, und damit bekommst du natürlich leider auch die von dir genannten Dateiendungen aufgelistet.
        Und das Durchsuchen von Unterordnern geht damit auch nicht.

        Bleibt wohl doch nur ein Upgrade auf eine neue Excel-Version, so dass du auch endlich Power Query nutzen kannst 😉

        Schöne Grüße,
        Martin

  • Avatar-Foto
    Thomas

    Hallo Martin Weiß,
    es ist zwar schon einige Monde har, dass dieser Artikel erschien, aber er hat für mich nichts an Aktualität verloren.
    Da ich Excel und das VBA darin schon immer entensiv nutzte (wenn auch nicht mit aller Rafinesse) habe ich mir ein Tool geschrieben, welches mit einem Shell Objekt die Dateinamen auflistet und auch die verschiedenen Dateieigenschaften, wie Größe, Elementtyp, Änderungsdatum etc.
    Insgesamt 34 Elemente.
    ABER: Um z. Bsp. Videodateien bewerten und sortieren zu können, benötige ich auch die Bildhöhe und die Bildbreite. Jedoch bietet mir das Shell Objekt diese Daten nicht an.
    Haben Sie eine Idee, wie ich das bewrkstelligen kann?
    Vielen Dank
    Thomas

    • Avatar-Foto
      Martin Weiß

      Hallo Thomas,

      tut mir leid, mir ist leider auch keine Möglichkeit bekannt, wie man die Metadaten aus Bild- oder Videodateien auslesen kann.

      Schöne Grüße,
      Martin