VBA hat einige nützliche Funktionen, die Ihre Automatisierung in Excel auf die nächste Stufe heben können.
Eine solche Funktion ist die VBA DIR-Funktion.
Obwohl es für sich genommen wie eine einfache Funktion erscheinen mag, die eine bestimmte Sache tut.
Aber wenn Sie es mit einigen anderen nützlichen Elementen der VBA-Codierungssprache kombinieren, können Sie leistungsstarke Dinge erstellen (die in den Beispielen weiter unten in diesem Tutorial behandelt werden).
Was macht die VBA Dir-Funktion?
Verwenden Sie die VBA DIR-Funktion, wenn Sie den Namen der Datei oder eines Ordners mithilfe ihres Pfadnamens abrufen möchten.
Um Ihnen ein Beispiel zu geben: Wenn Sie eine Excel-Datei in einem Ordner haben, können Sie die VBA-DIR-Funktion verwenden, um den Namen dieser Excel-Datei (oder eines anderen Dateityps) abzurufen.
Was ist, wenn ich die Namen aller Excel-Dateien im Ordner (oder aller Dateien - sei es Excel-Datei oder nicht) erhalten möchte?
Das kannst du auch!
Wenn Sie die DIR-Funktion einmal verwenden, gibt sie die erster Dateiname in einem Ordner. Wenn Sie nun auch die Namen der zweiten, dritten, vierten Datei erhalten möchten, können Sie erneut die DIR-Funktion verwenden (wird später als Beispiel behandelt).
Dir gibt den ersten Dateinamen zurück, der dem Pfadnamen entspricht. Um zusätzliche Dateinamen zu erhalten, die mit dem Pfadnamen übereinstimmen, rufen Sie Dir wieder ohne Argumente. Wenn keine Dateinamen mehr übereinstimmen, Dir gibt eine Zeichenfolge der Länge Null zurück (""). Wird später in Beispiel 3 und 4 in diesem Tutorial behandelt.Syntax der VBA DIR-Funktion
Dir [ (Pfadname [ ,Attribute ] ) ]
- Pfadname: Dies ist ein optionales Argument. Dies kann der Dateiname, der Ordnername oder der Verzeichnisname sein. Wenn der Pfadname nicht gefunden wird, gibt die VBA-DIR-Funktion eine Zeichenfolge der Länge null ("") zurück.
- Attribute: Dies ist ein optionales Argument. Sie können dieses Argument verwenden, um einige Attribute anzugeben, und die DIR-Funktion gibt die Dateinamen basierend auf diesen Attributen zurück. Wenn Sie beispielsweise eine Liste aller versteckten Dateien oder schreibgeschützten Dateien (zusammen mit Dateien ohne Attribute) wünschen, müssen Sie dies in diesem Argument angeben.
Verfügbare Attribute in der VBA DIR-Funktion (Sie können eines oder mehrere davon verwenden):
| Konstante | Wert | Beschreibung |
| vbNormal | 0 | (Standard) Gibt Dateien ohne Attribute an. |
| vbReadOnly | 1 | Gibt zusätzlich zu Dateien ohne Attribute schreibgeschützte Dateien an. |
| vbVersteckt | 2 | Gibt versteckte Dateien zusätzlich zu Dateien ohne Attribute an. |
| VbSystem | 4 | Gibt Systemdateien zusätzlich zu Dateien ohne Attribute an. Auf dem Macintosh nicht verfügbar. |
| vbVolume | 8 | Gibt Datenträgerbezeichnung an; Wenn ein anderes Attribut angegeben ist, wird vbVolume ignoriert. Auf dem Macintosh nicht verfügbar. |
| vbVerzeichnis | 16 | Gibt neben Dateien ohne Attribute auch Verzeichnisse oder Ordner an. |
| vbAlias | 64 | Der angegebene Dateiname ist ein Alias. Nur auf dem Macintosh verfügbar. |
Verwenden von Platzhalterzeichen mit der DIR-Funktion
Wenn Sie mit Windows arbeiten, können Sie auch die Platzhalterzeichen in der DIR-Funktion verwenden.
Beachten Sie, dass Sie diese nicht verwenden können, wenn Sie mit VBA in Macintosh arbeiten.
Die Verwendung von Platzhaltern kann in folgenden Fällen nützlich sein:
- Sie möchten die Dateinamen eines bestimmten Dateityps (wie .XLSX oder .PPTX) abrufen
- Wenn Sie ein bestimmtes Suffix/Präfix in Dateinamen haben und die Namen dieser Dateien/Ordner/Verzeichnisse erhalten möchten. Wenn Sie beispielsweise die Namen aller Dateien mit dem Präfix2021-2022 darin haben möchten, können Sie dies mit Platzhalterzeichen tun.
In Excel gibt es drei Platzhalterzeichen:
- * (Sternchen) - Es steht für eine beliebige Anzahl von Zeichen. Beispielsweise, 2019* würde Ihnen die Namen aller Dateien mit dem Präfix2021-2022 geben.
- ? (Fragezeichen) - Es repräsentiert ein einzelnes Zeichen. Zum Beispiel 2021-2022? würde Ihnen die Namen aller Dateien geben, die mit 2021-2022 beginnen und ein weiteres Zeichen im Namen haben (wie 2021-2022A,2021-2022B,2021-2022C usw.)
Hinweis: Es gibt noch ein Platzhalterzeichen - Tilde (~). Da es nicht oft verwendet wird, habe ich seine Erklärung übersprungen. Hier können Sie bei Interesse mehr darüber lesen.
VBA DIR-Funktion - Beispiele
Lassen Sie uns nun eintauchen und einige Beispiele für die Verwendung der VBA DIR-Funktion sehen.
Beispiel 1 - Abrufen des Dateinamens aus seinem Pfad
Wenn Sie den Pfad einer Datei haben, können Sie die DIR-Funktion verwenden, um den Namen der Datei daraus abzurufen.
Der folgende Code gibt beispielsweise den Namen der Datei zurück und zeigt ihn in einem Meldungsfeld an.
Sub GetFileNames() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\Excel File A.xlsx") MsgBox FileName End Sub
Der obige Code verwendet eine Variable „FileName“, um den Dateinamen zu speichern, der von der DIR-Funktion zurückgegeben wird. Es verwendet dann ein Meldungsfeld, um den Dateinamen anzuzeigen (wie unten gezeigt).
Und was passiert, wenn die Datei nicht existiert?
In diesem Fall würde die DIR-Funktion eine leere Zeichenfolge zurückgeben.
Der folgende Code verwendet eine If Then Else-Anweisung, um zu überprüfen, ob die Datei vorhanden ist oder nicht. Wenn die Datei nicht existiert, wird ein Meldungsfeld mit dem Text „Datei existiert nicht“ angezeigt, andernfalls wird der Dateiname angezeigt.
Sub CheckFileExistence() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\Excel File A.xlsx") If FileName "" Then MsgBox FileName Else MsgBox "Datei existiert nicht" End If End Sub
Beispiel 2 - Überprüfen Sie, ob ein Verzeichnis vorhanden ist oder nicht (und erstellen Sie es, wenn dies nicht der Fall ist)
Der folgende Code prüft, ob der Ordner „Test“ existiert oder nicht.
Ein Nachrichtenfeld wird verwendet, um eine Nachricht anzuzeigen, falls der Ordner existiert oder nicht existiert.
Sub CheckDirectory() Dim PathName As String Dim CheckDir As String PathName = "C:\Users\sumit\Desktop\Test" CheckDir = Dir(PathName, vbDirectory) If CheckDir "" Then MsgBox CheckDir & "existiert" Else MsgBox "The directory existiert nicht" End If End Sub
Sie können diesen Code weiter verfeinern, um zu überprüfen, ob der Ordner vorhanden ist oder nicht, und wenn nicht, können Sie diesen Ordner mit VBA erstellen.
Unten ist der Code, der die verwendet MkDir-Funktion um einen Ordner zu erstellen, falls er nicht existiert.
Sub CreateDirectory() Dim PathName As String Dim CheckDir As String PathName = "C:\Users\sumit\Desktop\Test" CheckDir = Dir(PathName, vbDirectory) If CheckDir "" Then MsgBox CheckDir & "Ordner existiert" Else MkDir PathName MsgBox "Ein Ordner mit dem Namen wurde erstellt" & CheckDir End If End Sub
Beispiel 3 – Abrufen der Namen aller Dateien und Ordner in einem Verzeichnis
Wenn Sie eine Liste aller Datei- und Ordnernamen in einem Verzeichnis erhalten möchten, können Sie die DIR-Funktion verwenden.
Der folgende Code listet alle Dateien und Ordnernamen im Testordner auf (der sich im folgenden Pfad befindet - C:\Users\sumit\Desktop\Test\).
Ich verwende Debug.Print, um die Namen im Direktfenster anzuzeigen. Sie können dies auch verwenden, um die Namen in einem Meldungsfeld oder in einer Spalte in Excel aufzulisten.
Sub GetAllFile&FolderNames() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\", vbDirectory) Do While FileName "" Debug.Print FileName FileName = Dir() Loop End Sub
Die Do While-Schleife im obigen Code wird fortgesetzt, bis alle Dateien und Ordner im angegebenen Pfad abgedeckt sind. Wenn keine Dateien/Ordner mehr abgedeckt werden müssen, wird FileName zu einem Null-String und die Schleife stoppt.
Beispiel 4 – Abrufen der Namen aller Dateien in einem Ordner
Sie können den folgenden Code verwenden, um die Namen aller Dateien in einem Ordner/Verzeichnis zu erhalten (und nicht die Namen der Unterordner).
Sub GetAllFileNames() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\") Do While FileName "" Debug.Print FileName FileName = Dir() Loop End Sub
Dieser Code ist genau wie der in Beispiel 3 verwendete Code, mit einem kleinen Unterschied.
In diesem Code habe ich nicht angegeben vbVerzeichnis in der DIR-Funktion. Wenn Sie vbDirectory angeben, werden Ihnen die Namen aller Dateien sowie Ordner angezeigt.
Wenn Sie vbDirectory nicht angeben, gibt Ihnen die DIR-Funktion nur die Namen der Dateien an.
Notiz: Wenn Sie die Namen aller Dateien im Hauptordner und in den Unterordnern erhalten möchten, können Sie die DIR-Funktion nicht verwenden (da sie nicht rekursiv ist). Dazu können Sie entweder Power Query (keine Codierung erforderlich) oder das Dateisystemobjekt in VBA (mit Rekursion) verwenden.
Beispiel 5 – Rufen Sie die Namen aller Unterordner in einem Ordner ab
Der folgende Code gibt Ihnen die Namen aller Unterordner innerhalb des angegebenen Ordners.
Es verwendet die GetAtr-Funktion in VBA, wodurch wir überprüfen können, ob der von der DIR-Funktion zurückgegebene Name der Name einer Datei oder eines Ordners/Verzeichnisses ist.
Sub GetSubFolderNames() Dim FileName As String Dim PathName As String PathName = "C:\Users\sumit\Desktop\Test\" FileName = Dir(PathName, vbDirectory) Do While FileName "" If GetAttr(PathName & FileName) = vbDirectory Then Debug.Print FileName End If FileName = Dir() Loop End Sub
Auch hier verwende ich Debug.Print, um die Namen im unmittelbaren Fenster abzurufen. Diese erhalten Sie in einem Meldungsfeld oder in Excel (indem Sie den Code entsprechend ändern).
Beispiel 6 – Holen Sie sich die erste Excel-Datei aus einem Ordner
Mit der DIR-Funktion können Sie die Dateierweiterung oder ein beliebiges Suffix/Präfix im zurückgegebenen Dateinamen angeben.
Der folgende Code würde den Namen der ersten Excel-Datei im Testordner anzeigen.
Sub GetFirstExcelFileName() Dim FileName As String Dim PathName As String PathName = "C:\Users\sumit\Desktop\Test\" FileName = Dir(PathName & "*.xls*") MsgBox FileName End Sub
Beachten Sie, dass ich *.xls* (Sternchen auf beiden Seiten) verwendet habe. Dadurch wird sichergestellt, dass alle Versionen von Excel-Dateien überprüft werden (.xls, xlsx, .xlsm, .xlsb).
Beispiel 7 – Namen aller Excel-Dateien in einem Ordner abrufen
Verwenden Sie den folgenden Code, um die Namen aller Excel-Dateien im Testordner abzurufen.
Sub GetAllFileNames() Dim FolderName As String Dim FileName As String FolderName = "C:\Users\sumit\Desktop\Test\" FileName = Dir(FolderName & "*.xls*") Do While FileName "" Debug.Print FileName FileName = Dir() Loop End Sub
Während die DIR-Funktion nur den Namen der ersten Excel-Datei zurückgibt, geht sie, da wir sie in der Schleife erneut aufrufen, alle Dateien durch und gibt uns die Namen aller Excel-Dateien.
Ich hoffe, Sie fanden dieses Tutorial und die Beispiele nützlich.
Teilen Sie mir Ihre Meinung im Kommentarbereich mit.
