Verwenden von VBA FileSystemObject (FSO) in Excel – Einfacher Überblick & Beispiele

Wenn wir VBA in Excel verwenden, dann meistens, um unsere Aufgaben zu automatisieren.

Das bedeutet auch, dass wir die meiste Zeit mit Zellen und Bereichen, Arbeitsblättern, Arbeitsmappen und anderen Objekten arbeiten, die ein Teil der Excel Anwendung sind.

Aber VBA ist viel leistungsfähiger und kann auch verwendet werden, um mit Dingen außerhalb von Excel zu arbeiten.

In diesem Tutorial zeige ich Ihnen, wie Sie VBA FileSystemObject (FSO) verwenden, um mit Dateien und Ordnern auf Ihrem System oder Netzlaufwerken zu arbeiten.

Was ist VBA FileSystemObject (FSO)?

FileSystemObject (FSO) ermöglicht Ihnen den Zugriff auf das Dateisystem Ihres Computers. Damit können Sie auf die Dateien/Ordner/Verzeichnisse in Ihrem Computersystem zugreifen und diese ändern.

Zum Beispiel können Sie mithilfe von FileSystemObject in Excel VBA Folgendes tun:

• Prüfen, ob eine Datei oder ein Ordner vorhanden ist.
• Ordner/Dateien erstellen oder umbenennen.
• Eine Liste aller Dateinamen (oder Unterordnernamen) in einem Ordner abrufen.
• Kopieren von Dateien von einem Ordner in einen anderen.

Ich hoffe, Sie haben die Idee verstanden.

Ich werde all diese oben genannten Beispiele (und mehr) später in diesem Tutorial behandeln.

Einige der oben genannten Dinge können zwar auch mit traditionellen VBA-Funktionen (wie der DIR-Funktion) und -Methoden durchgeführt werden, aber das würde zu längerem und komplizierterem Code führen. FileSystemObject macht es einfach, mit Dateien und Ordnern zu arbeiten und dabei den Code sauber und kurz zu halten.

Note: FSO can only be used in Excel 2000 and later versions.

Auf welche Objekte können Sie über FileSystemObject zugreifen?

Wie bereits erwähnt, können Sie mit dem FileSystemObject in VBA auf Dateien und Ordner zugreifen und diese verändern.

Nachfolgend finden Sie eine Tabelle mit den wichtigsten Objekten, auf die Sie mit FSO zugreifen und sie verändern können:

Jedes der oben genannten Objekte verfügt über Methoden, die Sie verwenden können, um mit ihnen zu arbeiten.

Wenn Sie beispielsweise einen Ordner löschen möchten, verwenden Sie die Methode DeleteFolder des Objekts Folder. Wenn Sie eine Datei kopieren wollen, verwenden Sie die Methode CopyFile des File-Objekts.

Sorgen Sie sich nicht, wenn dies überwältigend oder schwer zu verstehen ist. Sie werden es viel besser verstehen, wenn Sie die Beispiele durchgehen, die ich in diesem Tutorium behandelt habe.

Zu Referenzzwecken habe ich alle FileSystemObject-Methoden (für jedes Objekt) am Ende dieses Tutoriums behandelt.

Aktivieren von FileSystemObject in Excel VBA

FileSystemObject ist in Excel VBA standardmäßig nicht verfügbar.

Da wir es mit Dateien und Ordnern zu tun haben, die sich außerhalb der Excel-Anwendung befinden, müssen wir zunächst einen Verweis auf die Bibliothek erstellen, die diese Objekte (Laufwerke, Dateien, Ordner) enthält.

Nun gibt es zwei Möglichkeiten, FileSystemObject in Excel VBA zu verwenden:

1. Setzen der Referenz auf die Microsoft Scripting Runtime Library (Scrrun.dll)
2. Erstellen eines Objekts, um auf die Bibliothek aus dem Code selbst zu verweisen

Obwohl beide Methoden funktionieren (und ich zeige Ihnen im Folgenden, wie Sie dies tun), empfehle ich die erste Methode.

Setzen des Verweises auf die Microsoft Scripting Runtime Library

Wenn Sie einen Verweis auf die Scripting Runtime Library erstellen, erlauben Sie Excel VBA den Zugriff auf alle Eigenschaften und Methoden von Dateien und Ordnern. Sobald dies geschehen ist, können Sie aus Excel VBA heraus auf das Objekt Dateien/Ordner/Laufwerke verweisen (genauso wie Sie auf Zellen, Arbeitsblätter oder Arbeitsmappen verweisen können).

Nachfolgend finden Sie die Schritte zum Erstellen eines Verweises auf die Microsoft Scripting Runtime Library:

1. Klicken Sie im VB-Editor auf Extras.
2. Klicken Sie auf Referenzen.
3. Blättern Sie im Dialogfeld Referenzen, das sich öffnet, durch die verfügbaren Referenzen und markieren Sie die Option ‚Microsoft Scripting Runtime‘.
4. Klicken Sie auf OK.

Mit den obigen Schritten können Sie nun von Excel VBA aus auf die FSO-Objekte verweisen.

Erstellen einer Instanz von FileSystemObject im Code

Nachdem Sie den Verweis auf die Scripting FileSystemObject-Bibliothek gesetzt haben, müssen Sie eine Instanz des FSO-Objekts in Ihrem Code erstellen.

Sobald diese erstellt ist, können Sie sie in VBA verwenden.

Nachfolgend finden Sie den Code, der die Objektvariable MyFSO als FileSystemObject-Objekt einrichtet:

Sub CreatingFSO()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectEnd Sub

In diesem Code habe ich zunächst die Variable MyFSO als Objekt vom Typ FileSystemObject deklariert. Dies ist nur möglich, weil ich einen Verweis auf die Microsoft Scripting Runtime Library erstellt habe. Wenn der Verweis nicht erstellt wird, führt dies zu einem Fehler (da Excel nicht erkennt, was FileSystemObject bedeutet).

In der zweiten Zeile geschehen zwei Dinge:

1. Das Schlüsselwort NEW erstellt eine Instanz des FileSystemObject. Das bedeutet, dass ich jetzt alle Methoden von FileSystemObject verwenden kann, um mit Dateien und Ordnern zu arbeiten. Wenn du diese Instanz nicht erstellst, kannst du nicht auf die Methoden von FSO zugreifen.
2. Das Schlüsselwort SET setzt das Objekt MyFSO auf diese neue Instanz von FileSystemObject. Dadurch kann ich dieses Objekt für den Zugriff auf Dateien und Ordner verwenden. Wenn ich zum Beispiel einen Ordner erstellen muss, kann ich die Methode MyFSO.CreateFolder verwenden.

Wenn Sie möchten, können Sie die beiden oben genannten Anweisungen auch zu einer einzigen kombinieren, wie unten gezeigt:

Sub CreatingFSO()Dim MyFSO As New FileSystemObjectEnd Sub

Ein großer Vorteil der Verwendung dieser Methode (die darin besteht, den Verweis auf die Microsoft Scripting Runtime Library festzulegen) ist, dass Sie bei der Verwendung der FSO-Objekte in Ihrem Code die IntelliSense-Funktion verwenden können, die die mit einem Objekt verbundenen Methoden und Eigenschaften anzeigt (wie unten gezeigt).

Dies ist nicht möglich, wenn Sie den Verweis aus dem Code heraus erstellen (wird im Folgenden behandelt).

Erstellen eines Objekts aus dem Code heraus

Eine weitere Möglichkeit, einen Verweis auf FSO zu erstellen, besteht darin, dies aus dem Code heraus zu tun. Bei dieser Methode müssen Sie keinen Verweis erstellen (wie bei der vorherigen Methode).

Wenn Sie den Code schreiben, können Sie ein Objekt im Code erstellen und auf das Scripting.FileSystemObject verweisen.

Der untenstehende Code erstellt ein Objekt FSO und macht dieses dann zu einem FileSystemObject-Typ.

Sub FSODemo()Dim FSO As ObjectSet FSO = CreateObject("Scripting.FileSystemObject")End Sub

Dies mag zwar bequemer erscheinen, aber ein großer Nachteil dieser Methode ist, dass kein IntelliSense angezeigt wird, wenn Sie mit Objekten in FSO arbeiten. Für mich ist das ein großer Nachteil, und ich empfehle immer, die vorherige Methode zur Aktivierung von FSO zu verwenden (d.h. durch Einstellen des Verweises auf die ‚Microsoft Scripting Runtime‘)

VBA FileSystemObject Beispiele

Nun lassen Sie uns eintauchen und einen Blick auf einige praktische Beispiele für die Verwendung von FileSystemObject in Excel werfen.

Beispiel 1: Prüfen, ob eine Datei oder ein Ordner existiert

Der folgende Code prüft, ob der Ordner mit dem Namen ‚Test‘ existiert oder nicht (am angegebenen Speicherort).

Wenn der Ordner existiert, ist die WENN-Bedingung wahr und es wird eine Meldung angezeigt – ‚Der Ordner existiert‘ in einem Meldungsfeld. Und wenn er nicht existiert, wird die Meldung ‚Der Ordner existiert nicht‘ angezeigt.

Sub CheckFolderExist()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectIf MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "The Folder Exists"Else MsgBox "The Folder Does Not Exist"End IfEnd Sub

Auf ähnliche Weise können Sie auch prüfen, ob eine Datei existiert oder nicht.

Der folgende Code prüft, ob eine Datei mit dem Namen Test.xlsx in dem angegebenen Ordner vorhanden ist oder nicht.

Sub CheckFileExist()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectIf MyFSO.FileExists("C:\Users\sumit\Desktop\Test\Test.xlsx") Then MsgBox "The File Exists"Else MsgBox "The File Does Not Exist"End IfEnd Sub

Beispiel 2: Erstellen eines neuen Ordners am angegebenen Ort

Der folgende Code würde einen Ordner mit dem Namen ‚Test‘ im Laufwerk C meines Systems erstellen (Sie müssen den Pfad auf Ihrem System angeben, in dem Sie den Ordner erstellen möchten).

Sub CreateFolder()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectMyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test")End Sub

Dieser Code funktioniert zwar gut, würde aber einen Fehler anzeigen, wenn der Ordner bereits existiert.

Der folgende Code prüft, ob der Ordner bereits existiert, und erstellt einen Ordner, wenn dies nicht der Fall ist. Falls der Ordner bereits existiert, wird eine Meldung angezeigt. Um zu prüfen, ob der Ordner existiert, habe ich die FolderExists-Methode des BFS verwendet.

Sub CreateFolder()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectIf MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "The Folder Already Exist"Else MyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test")End IfEnd Sub

Beispiel 3: Eine Liste aller Dateien in einem Ordner abrufen

Der folgende Code zeigt die Namen aller Dateien im angegebenen Ordner an.

Sub GetFileNames()Dim MyFSO As FileSystemObjectDim MyFile As FileDim MyFolder As FolderSet MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop\Test")For Each MyFile In MyFolder.Files Debug.Print MyFile.NameNext MyFileEnd Sub

Dieser Code ist ein wenig komplexer als die, die wir bereits gesehen haben.

Wie ich bereits oben in diesem Tutorial erwähnt habe, können Sie, wenn Sie auf die „Microsoft Scripting Runtime Library“ verweisen, sowohl FileSystemObject als auch alle anderen Objekte (wie Dateien und Ordner) verwenden.

Im obigen Code verwende ich drei Objekte – FileSystemObject, File und Folder. Damit kann ich jede Datei im angegebenen Ordner durchgehen. Dann verwende ich die Eigenschaft name, um die Liste aller Dateinamen zu erhalten.

Beachten Sie, dass ich Debug.Print verwende, um die Namen aller Dateien zu erhalten. Diese Namen werden im unmittelbaren Fenster des VB-Editors aufgelistet.

Beispiel 4: Abrufen der Liste aller Unterordner in einem Ordner

Der folgende Code gibt die Namen aller Unterordner im angegebenen Ordner aus. Die Logik ist genau dieselbe wie im obigen Beispiel. Anstelle von Dateien werden in diesem Code Unterordner verwendet.

Sub GetSubFolderNames()Dim MyFSO As FileSystemObjectDim MyFile As FileDim MyFolder As FolderDim MySubFolder As FolderSet MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop\Test")For Each MySubFolder In MyFolder.SubFolders Debug.Print MySubFolder.NameNext MySubFolderEnd Sub

Beispiel 5: Kopieren einer Datei von einem Ort zu einem anderen

Der folgende Code kopiert die Datei aus dem „Quell“-Ordner in den „Ziel“-Ordner.

Sub CopyFile()Dim MyFSO As FileSystemObjectDim SourceFile As StringDim DestinationFolder As StringSet MyFSO = New Scripting.FileSystemObjectSourceFile = "C:\Users\sumit\Desktop\Source\SampleFile.xlsx"DestinationFolder = "C:\Users\sumit\Desktop\Destination"MyFSO.CopyFile Source:=SourceFile, Destination:=DestinationFolder & "\SampleFileCopy.xlsx"End Sub

Im obigen Code habe ich zwei Variablen verwendet – SourceFile und DestinationFolder.

Die Variable Quelldatei enthält die Adresse der zu kopierenden Datei und die Variable Zielordner enthält die Adresse des Ordners, in den die Datei kopiert werden soll.

Beachten Sie, dass es nicht ausreicht, den Namen des Zielordners anzugeben, wenn Sie eine Datei kopieren. Sie müssen auch den Dateinamen angeben. Sie können denselben Dateinamen verwenden oder ihn auch ändern. Im obigen Beispiel habe ich die Datei kopiert und sie SampleFileCopy.xlsx genannt

Beispiel 6: Kopieren aller Dateien von einem Ordner in einen anderen

Der folgende Code kopiert alle Dateien aus dem Quellordner in den Zielordner.

Sub CopyAllFiles()Dim MyFSO As FileSystemObjectDim MyFile As FileDim SourceFolder As StringDim DestinationFolder As StringDim MyFolder As FolderDim MySubFolder As FolderSourceFolder = "C:\Users\sumit\Desktop\Source"DestinationFolder = "C:\Users\sumit\Desktop\Destination"Set MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder(SourceFolder)For Each MyFile In MyFolder.Files MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=FalseNext MyFileEnd Sub

Der obige Code kopiert alle Dateien aus dem Quellordner in den Zielordner.

Beachten Sie, dass ich in der Methode MyFSO.CopyFile die Eigenschaft „Overwritefiles“ auf False gesetzt habe (standardmäßig ist sie True). Dadurch wird sichergestellt, dass die Datei nicht kopiert wird, falls sie sich bereits im Ordner befindet (und ein Fehler angezeigt wird). Wenn Sie „Dateien überschreiben“ entfernen oder auf „Wahr“ setzen, werden Dateien mit demselben Namen im Zielordner überschrieben.

Wenn Sie nur die Dateien einer bestimmten Erweiterung kopieren möchten, können Sie dies mit einer IF Then-Anweisung tun, um zu prüfen, ob die Erweiterung xlsx ist oder nicht.

Sub CopyExcelFilesOnly()Dim MyFSO As FileSystemObjectDim MyFile As FileDim SourceFolder As StringDim DestinationFolder As StringDim MyFolder As FolderDim MySubFolder As FolderSourceFolder = "C:\Users\sumit\Desktop\Source"DestinationFolder = "C:\Users\sumit\Desktop\Destination"Set MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder(SourceFolder)For Each MyFile In MyFolder.Files If MyFSO.GetExtensionName(MyFile) = "xlsx" Then MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=False End IfNext MyFileEnd Sub

FileSystemObject (FSO) Methoden

Hier sind die Methoden, die Sie für jedes Objekt verwenden können. Dies ist nur als Referenz gedacht und macht nicht zu viel Mühe. Die Verwendung einiger dieser Methoden wurde in den oben behandelten Beispielen gezeigt.

Die folgenden Excel-Tutorials könnten Ihnen auch gefallen:

• Eine Liste von Dateinamen aus Ordnern abrufen & Unterordner (mit Power Query).
• Abrufen einer Liste von Dateinamen aus einem Ordner in Excel (mit und ohne VBA).
• Verstehen von Excel VBA-Datentypen (Variablen und Konstanten).
• Erstellen einer benutzerdefinierten Funktion (UDF) in Excel VBA.