Abrufen der Liste der Dateinamen aus einem Ordner in Excel (mit und ohne VBA)

An meinem ersten Tag in meinem Job in einer kleinen Beratungsfirma war ich drei Tage lang für ein kurzes Projekt besetzt.

Die Arbeit war einfach.

Es gab viele Ordner auf dem Netzlaufwerk und jeder Ordner enthielt Hunderte von Dateien.

Ich musste diese drei Schritte befolgen:

1. Wählen Sie die Datei aus und kopieren Sie ihren Namen.
2. Fügen Sie diesen Namen in eine Zelle in Excel ein und drücken Sie die Eingabetaste.
3. Wechseln Sie zur nächsten Datei und wiederholen Sie Schritt 1 und 2.

Klingt einfach oder?

Es war - einfach und eine riesige Zeitverschwendung.

Was mich drei Tage gekostet hat, hätte ich in wenigen Minuten erledigen können, wenn ich die richtigen Techniken kannte.

In diesem Tutorial zeige ich Ihnen verschiedene Möglichkeiten, diesen gesamten Prozess superschnell und supereinfach zu gestalten (mit und ohne VBA).

Einschränkungen der in diesem Tutorial gezeigten Methoden: Mit den unten gezeigten Techniken können Sie nur die Namen der Dateien innerhalb des Hauptordners abrufen. Sie erhalten nicht die Namen der Dateien in den Unterordnern innerhalb des Hauptordners. So können Sie mit Power Query Namen von Dateien aus Ordnern und Unterordnern abrufen

Verwenden der FILES-Funktion zum Abrufen einer Liste von Dateinamen aus einem Ordner

Gehört von DATEIEN-Funktion Vor?

Machen Sie sich keine Sorgen, wenn Sie es nicht getan haben.

Es stammt aus der Kindheit von Excel-Tabellen (eine Formel der Version 4).

Obwohl diese Formel in den Arbeitsblattzellen nicht funktioniert, funktioniert sie dennoch in benannten Bereichen. Wir verwenden diese Tatsache, um die Liste der Dateinamen aus einem bestimmten Ordner zu erhalten.

Angenommen, Sie haben einen Ordner mit dem Namen - ‘Testordner‘ auf dem Desktop, und Sie möchten eine Liste der Dateinamen für alle Dateien in diesem Ordner abrufen.

Hier sind die Schritte, die Ihnen die Dateinamen aus diesem Ordner geben:

1. Geben Sie in Zelle A1 die vollständige Adresse des Ordners gefolgt von einem Sternchen (*) ein.
   • Wenn sich Ihr Ordner beispielsweise im Laufwerk C befindet, würde die Adresse wie folgt aussehen:
     C:\Benutzer\Sumit\Desktop\Testordner\*
   • Wenn Sie nicht sicher sind, wie Sie die Ordneradresse erhalten, verwenden Sie die folgende Methode:
     • • Erstellen Sie in dem Ordner, aus dem Sie die Dateinamen abrufen möchten, entweder eine neue Excel-Arbeitsmappe oder öffnen Sie eine vorhandene Arbeitsmappe im Ordner und verwenden Sie die folgende Formel in einer beliebigen Zelle. Diese Formel gibt Ihnen die Ordneradresse und fügt am Ende ein Sternchen (*) hinzu. Jetzt können Sie diese Adresse in eine beliebige Zelle (in diesem Beispiel A1) in der Arbeitsmappe kopieren und einfügen (als Wert einfügen), in der Sie die Dateinamen haben möchten.

         =ERSETZEN(ZELLE("Dateiname"),FIND("[",ZELLE("Dateiname")),LEN(ZELLE("Dateiname")),"*")

         [Wenn Sie eine neue Arbeitsmappe im Ordner erstellt haben, um die obige Formel zu verwenden und die Ordneradresse zu erhalten, möchten Sie sie möglicherweise löschen, damit sie nicht in der Liste der Dateien in diesem Ordner angezeigt wird]
2. Gehen Sie zur Registerkarte „Formeln“ und klicken Sie auf die Option „Namen definieren“.
3. Verwenden Sie im Dialogfeld Neuer Name die folgenden Details
   • Name: FileNameList (Sie können einen beliebigen Namen wählen)
   • Geltungsbereich: Arbeitsbuch
   • Bezieht sich auf: =DATEIEN(Blatt1!$A$1)
4. Um nun die Liste der Dateien zu erhalten, verwenden wir den benannten Bereich innerhalb einer INDEX-Funktion. Gehen Sie zu Zelle A3 (oder einer beliebigen Zelle, in der die Namensliste beginnen soll) und geben Sie die folgende Formel ein:

   =IFERROR(INDEX(FileNameList,ROW()-2),"")

5. Ziehen Sie diese nach unten und Sie erhalten eine Liste aller Dateinamen im Ordner

Möchten Sie Dateien mit einer bestimmten Erweiterung extrahieren?

Wenn Sie alle Dateien mit einer bestimmten Erweiterung erhalten möchten, ändern Sie einfach das Sternchen mit dieser Dateierweiterung. Wenn Sie beispielsweise nur Excel-Dateien verwenden möchten, können Sie *xls* anstelle von * verwenden.

Die Ordneradresse, die Sie verwenden müssen, wäre also C:\Benutzer\Sumit\Desktop\Testordner\*xls*

In ähnlicher Weise verwenden Sie für Word-Dokumentdateien *doc*

Wie funktioniert das?

Die FILES-Formel ruft die Namen aller Dateien mit der angegebenen Erweiterung im angegebenen Ordner ab.

In der INDEX-Formel haben wir die Dateinamen als Array angegeben und geben den 1., 2., 3. Dateinamen usw. mit der ROW-Funktion zurück.

Beachten Sie, dass ich verwendet habe REIHE()-2, da wir ab der dritten Reihe gestartet sind. ROW()-2 wäre also 1 für die erste Instanz, 2 für die zweite Instanz, wenn die Zeilennummer 4 ist, und so weiter und so weiter.

Video ansehen - Liste der Dateinamen aus einem Ordner in Excel abrufen

Verwenden von VBA Abrufen einer Liste aller Dateinamen aus einem Ordner

Nun muss ich sagen, dass die obige Methode etwas komplex ist (mit einer Reihe von Schritten).

Es ist jedoch viel besser, als dies manuell zu tun.

Aber wenn Sie mit der Verwendung von VBA vertraut sind (oder wenn Sie genau die Schritte ausführen können, die ich unten auflisten werde), können Sie eine benutzerdefinierte Funktion (UDF) erstellen, mit der Sie problemlos die Namen aller Dateien abrufen können.

Der Vorteil der Verwendung von a User Ddefiniert FFunktion (UDF) besteht darin, dass Sie die Funktion in einer persönlichen Makro-Arbeitsmappe speichern und problemlos wiederverwenden können, ohne die Schritte immer wieder wiederholen zu müssen. Sie können auch ein Add-In erstellen und diese Funktion mit anderen teilen.

Lassen Sie mich Ihnen nun zuerst den VBA-Code geben, der eine Funktion erstellt, um die Liste aller Dateinamen aus einem Ordner in Excel abzurufen.

Funktion GetFileNames(ByVal FolderPath As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Für jede MyFile in MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Ergebnis-Endfunktion

Der obige Code erstellt eine Funktion GetFileNames, die in den Arbeitsblättern verwendet werden kann (genau wie normale Funktionen).

Wo soll dieser Code abgelegt werden?

Führen Sie die folgenden Schritte aus, um diesen Code in den VB-Editor zu kopieren.

• Wechseln Sie zur Registerkarte Entwickler.
• Klicken Sie auf die Schaltfläche Visual Basic. Dies öffnet den VB-Editor.
• Klicken Sie im VB-Editor mit der rechten Maustaste auf eines der Objekte der Arbeitsmappe, in der Sie arbeiten, gehen Sie zu Einfügen und klicken Sie auf Modul. Wenn Sie den Projekt-Explorer nicht sehen, verwenden Sie die Tastenkombination Strg + R (halten Sie die Strg-Taste gedrückt und drücken Sie die Taste "R").
• Doppelklicken Sie auf das Modul-Objekt und kopieren Sie den obigen Code und fügen Sie ihn in das Modulcode-Fenster ein.

Wie verwende ich diese Funktion?

Im Folgenden sind die Schritte aufgeführt, um diese Funktion in einem Arbeitsblatt zu verwenden:

• Geben Sie in einer beliebigen Zelle die Ordneradresse des Ordners ein, aus dem Sie die Dateinamen auflisten möchten.
• Geben Sie in die Zelle, in der Sie die Liste haben möchten, die folgende Formel ein (ich gebe sie in Zelle A3 ein):

  =IFERROR(INDEX(GetFileNames($A$1),ROW()-2),"")

• Kopieren Sie die Formel und fügen Sie sie in die folgenden Zellen ein, um eine Liste aller Dateien zu erhalten.

Beachten Sie, dass ich den Ordnerspeicherort in eine Zelle eingegeben und diese Zelle dann im GetFileNames Formel. Sie können die Ordneradresse auch in der Formel wie unten gezeigt hartcodieren:

=IFERROR(INDEX(GetFileNames("C:\Users\Sumit\Desktop\Test Folder"),ROW()-2),"")

In der obigen Formel haben wir ROW()-2 verwendet und ab der dritten Reihe begonnen. Dadurch wurde sichergestellt, dass die Formel beim Kopieren in die darunter liegenden Zellen um 1 erhöht wird. Falls Sie die Formel in die erste Zeile einer Spalte eingeben, können Sie einfach ROW() verwenden.

Wie funktioniert diese Formel?

Die GetFileNames-Formel gibt ein Array zurück, das die Namen aller Dateien im Ordner enthält.

Die INDEX-Funktion wird verwendet, um einen Dateinamen pro Zelle aufzulisten, beginnend mit dem ersten.

Die IFERROR-Funktion wird verwendet, um anstelle von #REF ein Leerzeichen zurückzugeben! Fehler, der angezeigt wird, wenn eine Formel in eine Zelle kopiert wird, aber keine weiteren Dateinamen zum Auflisten vorhanden sind.

Verwenden von VBA Rufen Sie eine Liste aller Dateinamen mit einer bestimmten Erweiterung ab

Die obige Formel funktioniert hervorragend, wenn Sie eine Liste aller Dateinamen aus einem Ordner in Excel abrufen möchten.

Aber was ist, wenn Sie nur die Namen der Videodateien oder nur der Excel-Dateien oder nur die Dateinamen erhalten möchten, die ein bestimmtes Schlüsselwort enthalten.

In diesem Fall können Sie eine etwas andere Funktion verwenden.

Unten ist der Code, mit dem Sie alle Dateinamen mit einem bestimmten Schlüsselwort (oder einer bestimmten Erweiterung) abrufen können.

Funktion GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Für jede MyFile in MyFiles If InStr(1, MyFile.Name, FileExt) 0 Then Result(i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

Der obige Code erstellt eine Funktion ‘GetFileNamesbyExt‘, die in den Arbeitsblättern verwendet werden können (genau wie normale Funktionen).

Diese Funktion benötigt zwei Argumente - den Ordnerspeicherort und das Erweiterungsschlüsselwort. Es gibt ein Array von Dateinamen zurück, die der angegebenen Erweiterung entsprechen. Wenn keine Erweiterung oder kein Schlüsselwort angegeben wird, werden alle Dateinamen im angegebenen Ordner zurückgegeben.

Syntax: =GetFileNamesbyExt(“Ordnerspeicherort”,”Erweiterung”)

Wo soll dieser Code abgelegt werden?

Führen Sie die folgenden Schritte aus, um diesen Code in den VB-Editor zu kopieren.

• Wechseln Sie zur Registerkarte Entwickler.
• Klicken Sie auf die Schaltfläche Visual Basic. Dies öffnet den VB-Editor.
• Klicken Sie im VB-Editor mit der rechten Maustaste auf eines der Objekte der Arbeitsmappe, in der Sie arbeiten, gehen Sie zu Einfügen und klicken Sie auf Modul. Wenn Sie den Projekt-Explorer nicht sehen, verwenden Sie die Tastenkombination Strg + R (halten Sie die Strg-Taste gedrückt und drücken Sie die Taste "R").
• Doppelklicken Sie auf das Modul-Objekt und kopieren Sie den obigen Code und fügen Sie ihn in das Modulcode-Fenster ein.

Wie verwende ich diese Funktion?

Im Folgenden sind die Schritte aufgeführt, um diese Funktion in einem Arbeitsblatt zu verwenden:

• Geben Sie in einer beliebigen Zelle die Ordneradresse des Ordners ein, aus dem Sie die Dateinamen auflisten möchten. Das habe ich in Zelle A1 eingetragen.
• Geben Sie in einer Zelle die Erweiterung (oder das Schlüsselwort) ein, für die Sie alle Dateinamen haben möchten. Ich habe dies in Zelle B1 eingetragen.
• Geben Sie in der Zelle, in der Sie die Liste haben möchten, die folgende Formel ein (ich gebe sie in Zelle A3 ein):

  =IFERROR(INDEX(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")

• Kopieren Sie die Formel und fügen Sie sie in die folgenden Zellen ein, um eine Liste aller Dateien zu erhalten.

Und du? Alle Excel-Tricks, die Sie verwenden, um Ihnen das Leben zu erleichtern. Ich würde gerne von dir lernen. Teile es im Kommentarbereich!