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
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
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
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
So sieht übrigens mein Beispielverzeichnis aus:
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
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
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
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 🙂
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.
Löschen oder doch lösen!
“ … lässt sich diese Aufgabe auf unterschiedlichen Wegen löschen, …“
Hahaha, manche Aufgaben sollte man tatsächlich besser löschen!
Vielen Dank für den Hinweis, wird gleich korrigiert.
Schöne Grüße,
Martin
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
Hallo Mim,
ja, es ist immer schön, die Kollegen zu verblüffen 🙂
Schöne Grüße,
Martin
Hah, wieder was gelernt.
Hört das denn nie auf?
Ich hoffe nicht! 🙂
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
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
Das ist einer der besten Excel-Tipps, die ich je gelesen habe.
Phantastisch! DANKE!
Danke für das tolle Feedback!
Schöne Grüße,
Martin
Mit einer *.xlsb sollte es auch funktionieren!
… die Dateien werden auch noch merklich kleiner.
Ein wirklich ferner Artikel!
gruß
Michael
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
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
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
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
Hallo EuroCafe,
vielen Dank für die Ergänzungen. Die Spalte mit der Dateigröße lässt sich in Power Query ohne Problem einblenden. Ich hatte das schon mal im folgenden Artikel beschrieben:
https://www.tabellenexperte.de/ordnerstrukturen-mit-powerquery-in-excel-einlesen/
Schöne Grüße,
Martin
Kann man das auch mit Links machen wo hinten dann immer steh blablabla/page-2 und dann blablabla/page-3 usw.
z.b.
http://www.test.de/
dann
http://www.test.de/page-2
http://www.test.de/page-3 usw
Hallo Armin,
nein, mit externen Webseiten funktioniert das leider nicht. Man kann damit nur lokale Verzeichnisse auslesen.
Schöne Grüße,
Martin
Kann man in einer ähnlichen Formel auch die Ordnernamen eines Pfads einlesen?
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
Ich krieg’s nicht hin. Bekomme nur #NV oder #NAME?
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
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
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
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
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?
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
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
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
Hallo,
offenbar gibt es auch in Office 365 eine Größenbeschränkung. Bei mir endet die Dateiliste (Danke für den klasse Tipp!) bei 16.384 Dateien. Also bei 64 * 256. Ich vermute also, dass dies auch eine grundsätzliche Beschränkung ist.
Ja, das ist gut möglich. Ich würde mittlerweile ohnehin solche Dateilisten nur noch mit Power Query erstellen. Da umgeht man das Problem mit der Makro-Datei und eine Größenbeschränkung gibt es hier meines Wissens auch nicht.
Ordnerstrukturen mit PowerQuery in Excel einlesen
Schöne Grüße,
Martin
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
Martin, noch eine kleine Ergänzung zu meiner Frage 🙂
OHNE VBA und OHNE PowerQuery
Danke sehr!
Hallo boomcat22,
ohne VBA und ohne Power Query? Leider gar nicht…
Schöne Grüße,
Martin
Alles klar! Herzlichen Dank für schnelle Antwort!
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“)?
PS: PowerQuery geht mit Excel 2007 nicht 🙂
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
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
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