Eine Dateiliste in Excel erzeugen 16

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 🙂
 

Das könnte dich auch interessieren:

No related posts found

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.



Schreibe einen Kommentar

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

16 Gedanken zu “Eine Dateiliste in Excel erzeugen

    • Martin Weiß Autor des Beitrags

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

      Schöne Grüße,
      Martin

  • 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

  • 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

    • Martin Weiß Autor des Beitrags

      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

  • Michael B.

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

  • 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

    • Martin Weiß Autor des Beitrags

      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

    • 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

  • 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