Abrufen einer Liste von Dateinamen aus einem Ordner mit Excel (mit und ohne VBA)

An meinem ersten Tag bei einem kleinen Beratungsunternehmen wurde mir ein dreitägiges Kurzzeitprojekt zugeteilt.Die Arbeit ist einfach.

Auf einem Netzlaufwerk gibt es viele Ordner mit jeweils Hunderten von Dateien.

Diese drei Schritte musste ich befolgen:

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

Klingt einfach, oder?

Es ist einfach und sehr zeitaufwändig.

Was ich in drei Tagen geschafft habe, kann ich in Minuten erledigen, wenn ich die richtige Technik kenne.

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

Einschränkungen der in diesem Tutorial gezeigten Methoden:Mit der unten gezeigten Technik können Sie nur die Namen der Dateien in Ihrem Home-Ordner abrufen.Sie erhalten nicht die Namen von Dateien in Unterordnern im Hauptordner.Hier ist eine Möglichkeit, mit Power Query Dateinamen aus Ordnern und Unterordnern abzurufen

Verwenden Sie die FILES-Funktion, um eine Liste mit Dateinamen aus einem Ordner abzurufen

habe davon gehörtFILES-Funktion?

Wenn nicht, keine Sorge.

Es stammt aus einer Excel-Tabelle aus der Kindheit (Formeln der Version 4).

Diese Formel funktioniert zwar nicht mit Arbeitsblattzellen, aber dennoch mit benannten Bereichen.Wir werden diese Tatsache verwenden, um eine Liste mit Dateinamen aus dem angegebenen Ordner zu erhalten.

Angenommen, Sie haben auf Ihrem Desktop eine Datei mit dem Namen " Testordner “ und Sie möchten eine Liste der Dateinamen für alle Dateien in diesem Ordner erhalten.

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

1. Geben Sie in Zelle A1 die vollständige Ordneradresse gefolgt von einem Sternchen (*) ein.
   • Wenn sich Ihr Ordner beispielsweise auf Laufwerk C befindet, lautet die Adresse etwa so
     C:\Benutzer\IHR NAME\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 den Dateinamen abrufen möchten, 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 mit einem Sternchen (*) am Ende.Sie können diese Adresse jetzt kopieren (als Wert einfügen) und in eine beliebige Zelle in der Arbeitsmappe (A1 in diesem Beispiel) einfügen, in der Sie den Dateinamen haben möchten.

         =REPLACE(CELL("Dateiname"),FIND("[",CELL("Dateiname")),LEN(CELL("Dateiname")),"*")

         [Wenn Sie eine neue Arbeitsmappe in einem Ordner erstellt haben, um die obige Formel zu verwenden und die Ordneradresse zu erhalten, müssen Sie sie möglicherweise löschen, damit sie nicht in der Liste der Dateien in diesem Ordner enthalten ist.]

2. Gehen Sie auf die 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 eine Liste von Dateien zu erhalten, verwenden wir einen benannten Bereich in der INDEX-Funktion.Gehen Sie zu Zelle A3 (oder mit welcher Zelle auch immer die Namensliste beginnen soll) und geben Sie die folgende Formel ein:

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

5. Ziehen Sie es 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 die Excel-Datei möchten, können Sie *xls* anstelle von * verwenden

Die Ordneradresse, die Sie verwenden müssen, ist alsoC:UsersSumitDesktopTest-Ordner*xls*

Ebenso 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 geben wir die Dateinamen als Array an und verwenden die ROW-Funktion, um den ersten, zweiten, dritten usw. Dateinamen zurückzugeben.

Beachten Sie, dass ich verwendet habeREIHE()-2, da wir in der dritten Zeile beginnen.Wenn also die Zeilennummer 4 ist, ist ROW()-2 beim ersten Mal 1, beim zweiten Mal 2 und so weiter.

Rufen Sie mit VBA eine Liste aller Dateinamen aus einem Ordner ab

Nun muss ich sagen, dass die obige Methode etwas kompliziert ist (es gibt viele Schritte).

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

Wenn Sie jedoch mit der Verwendung von VBA vertraut sind (oder wenn Sie die genauen Schritte befolgen, die ich unten aufführe), können Sie eine benutzerdefinierte Funktion (UDF) erstellen, die die Namen aller Dateien problemlos abrufen kann.

Vorteile der Verwendung von benutzerdefinierten Funktionen (UDFs)是DuKönnen將FunktionSpeichern Sie in persönlichen Makro-Arbeitsmappen und verwenden Sie sie einfach wieder, ohne diese Schritte immer wieder wiederholen zu müssen.Sie können auch Add-Ons erstellen und diese Funktionalität mit anderen teilen.

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

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 For Each MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

Der obige Code erstellt eine Funktion GetFileNames, die im Arbeitsblatt verwendet werden kann (genau wie eine normale Funktion).

Wo ist dieser Code einzufügen?

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

Wie verwende ich diese Funktion?

Hier sind die Schritte, um diese Funktion in einem Arbeitsblatt zu verwenden:

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

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

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

Beachten Sie, dass ich den Speicherort des Ordners in eine Zelle und dann in eingegeben habeGetFileNamesDiese Zelle wird in einer Formel verwendet.Sie können die Ordneradresse auch wie folgt in der Formel fest codieren:

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

In der obigen Formel haben wir ROW()-2 verwendet und beginnen mit der dritten Zeile.Dadurch wird sichergestellt, dass, wenn ich die Formel in die Zelle darunter kopiere, sie um 1 erhöht wird.Wenn Sie die Formel in die erste Zeile der 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 ein Leerzeichen anstelle von #REF zurückzugeben!Fehler, der angezeigt wird, wenn eine Formel in eine Zelle kopiert wird, aber keine weiteren Dateinamen zum Auflisten vorhanden sind.

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

Die obige Formel ist nützlich, wenn Sie eine Liste aller Dateinamen aus einem Ordner in Excel erhalten möchten.

Was ist jedoch, wenn Sie nur den Namen einer Videodatei oder nur einer Excel-Datei oder nur einer Datei mit einem bestimmten Schlüsselwort erhalten möchten?

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

Mit dem folgenden Code können Sie alle Dateinamen abrufen, die ein bestimmtes Schlüsselwort (oder eine bestimmte Erweiterung) enthalten.

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 For Each MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Ergebnis beibehalten (1 bis i - 1) GetFileNamesbyExt = Ergebnis Endfunktion

Der obige Code erstellt eine Funktion, die im Arbeitsblatt verwendet werden kann. GetFileNamesbyExt " (genau wie normale Funktionen).

Diese Funktion benötigt zwei Parameter – den Speicherort des Ordners 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 ist, werden alle Dateinamen im angegebenen Ordner zurückgegeben.

Syntax: =GetFileNamesbyExt("Ordnerspeicherort","Erweiterung")

Wo ist dieser Code einzufügen?

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

• Gehen Sie zum Entwickler-Tab.
• Klicken Sie auf die Visual Basic-Schaltfläche.Dies öffnet den VB-Editor.
• Klicken Sie im VB-Editor mit der rechten Maustaste auf ein beliebiges Objekt in der Arbeitsmappe, an 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 „R“).
• Doppelklicken Sie auf das Modulobjekt, kopieren Sie den obigen Code und fügen Sie ihn in das Modulcodefenster ein.

Wie verwende ich diese Funktion?

Hier sind die Schritte, um diese Funktion in einem Arbeitsblatt zu verwenden:

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

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

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

Wie wäre es mit dir?Alle Excel-Tricks, mit denen Sie Ihr Leben vereinfachen.Ich würde gerne von Ihnen lernen.Teile es im Kommentarbereich!