Wenn wir VBA in Excel verwenden, dient das meiste dazu, unsere Aufgaben zu automatisieren.
Dies bedeutet auch, dass wir die meiste Zeit mit Zellen und Bereichen, Arbeitsblättern, Arbeitsmappen und anderen Objekten arbeiten, die 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 mit VBA FileSystemObject (FSO) mit Dateien und Ordnern auf Ihrem System oder Netzlaufwerken 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.
Im Folgenden sind beispielsweise einige der Dinge aufgeführt, die Sie mit FileSystemObject in Excel VBA tun können:
- Überprüfen Sie, ob eine Datei oder ein Ordner vorhanden ist.
- Ordner/Dateien erstellen oder umbenennen.
- Rufen Sie eine Liste aller Dateinamen (oder Unterordnernamen) in einem Ordner ab.
- Kopieren Sie Dateien von einem Ordner in einen anderen.
Ich hoffe, du verstehst die Idee.
Ich werde all diese obigen Beispiele (und mehr) später in diesem Tutorial behandeln.
Während einige der oben genannten Dinge auch mit traditionellen VBA-Funktionen (wie der DIR-Funktion) und Methoden durchgeführt werden können, würde dies zu längeren und komplizierteren Codes führen. FileSystemObject macht es einfach, mit Dateien und Ordnern zu arbeiten, während der Code sauber und kurz gehalten wird.
Hinweis: FSO kann nur in Excel 2000 und späteren Versionen verwendet werden.
Auf welche Objekte können Sie über FileSystemObject zugreifen?
Wie oben erwähnt, können Sie mit dem FileSystemObject in VBA auf Dateien und Ordner zugreifen und diese ändern.
Nachfolgend finden Sie eine Tabelle mit den wichtigsten Objekten, auf die Sie mit FSO zugreifen und die Sie ändern können:
| Objekt | Beschreibung |
| Antrieb | Mit Drive Object können Sie Informationen über das Laufwerk abrufen, z. B. ob es existiert oder nicht, seinen Pfadnamen, Laufwerkstyp (entfernbar oder fest), seine Größe usw. |
| Ordner | Mit dem Ordnerobjekt können Sie Ordner in Ihrem System erstellen oder ändern. Mit diesem Objekt können Sie beispielsweise Ordner erstellen, löschen, umbenennen und kopieren. |
| Datei | File Object ermöglicht Ihnen, mit Dateien in Ihrem System zu arbeiten. Mit diesem Objekt können Sie beispielsweise Dateien erstellen, öffnen, kopieren, verschieben und löschen. |
| TextStream | Mit dem TextStream-Objekt können Sie Textdateien erstellen oder lesen. |
Jedes der oben genannten Objekte verfügt über Methoden, mit denen Sie damit arbeiten können.
Um Ihnen ein Beispiel zu geben: Wenn Sie einen Ordner löschen möchten, verwenden Sie die DeleteFolder-Methode des Folder-Objekts. Wenn Sie eine Datei kopieren möchten, verwenden Sie die Methode CopyFile des File-Objekts.
Machen Sie sich keine Sorgen, wenn dies überwältigend oder schwer verständlich erscheint. Sie werden ein viel besseres Verständnis bekommen, wenn Sie die Beispiele durchgehen, die ich in diesem Tutorial behandelt habe.
Nur zu Referenzzwecken habe ich am Ende dieses Tutorials alle FileSystemObject-Methoden (für jedes Objekt) behandelt.
Aktivieren von FileSystemObject in Excel VBA
FileSystemObject ist in Excel VBA standardmäßig nicht verfügbar.
Da es sich um Dateien und Ordner außerhalb der Excel-Anwendung handelt, müssen wir zunächst eine Referenz auf die Bibliothek erstellen, die diese Objekte (Laufwerke, Dateien, Ordner) enthält.
Jetzt gibt es zwei Möglichkeiten, FileSystemObject in Excel VBA zu verwenden:
- Setzen des Verweises auf die Microsoft Scripting Runtime Library (Scrrun.dll)
- Erstellen eines Objekts, um aus dem Code selbst auf die Bibliothek zu verweisen
Obwohl beide Methoden funktionieren (und ich zeige Ihnen als Nächstes, wie Sie dies tun), empfehle ich die Verwendung der ersten Methode.
Hinweis: Wenn Sie FileSystemObject aktivieren, können Sie auf alle darin enthaltenen Objekte zugreifen. Dies beinhaltet das FileSystemObject, Drive, Files, Folders, etc. Ich werde mich in diesem Tutorial hauptsächlich auf das FileSystemObject konzentrieren.Setzen des Verweises auf die Microsoft Scripting Runtime Library
Wenn Sie einen Verweis auf die Scripting Runtime Library erstellen, gewähren Sie Excel VBA den Zugriff auf alle Eigenschaften und Methoden von Dateien und Ordnern. Sobald dies erledigt ist, können Sie innerhalb der Excel-VBA auf das Objekt Dateien/Ordner/Laufwerke verweisen (genau wie Sie auf die Zellen, Arbeitsblätter oder Arbeitsmappen verweisen können).
Nachfolgend finden Sie die Schritte zum Erstellen eines Verweises auf die Microsoft Scripting Runtime Library:
- Klicken Sie im VB-Editor auf Tools.
- Klicken Sie auf Referenzen.
- Scrollen Sie im sich öffnenden Dialogfeld "Referenzen" durch die verfügbaren Referenzen und aktivieren Sie die Option "Microsoft Scripting Runtime".
- OK klicken.
Mit den obigen Schritten können Sie nun auf die FSO-Objekte von Excel VBA verweisen.
Erstellen einer Instanz von FileSystemObject im Code
Nachdem Sie den Verweis auf die Scripting FileSystemObject-Bibliothek festgelegt haben, müssen Sie in Ihrem Code eine Instanz des FSO-Objekts erstellen.
Sobald diese erstellt wurde, können Sie sie in VBA verwenden.
Unten ist der Code, der die Objektvariable MyFSO als FileSystemObject-Objekt festlegt:
Sub CreatingFSO() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub
In diesem Code habe ich zuerst 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 die Referenz nicht erstellt wird, erhalten Sie einen Fehler (da Excel nicht erkennen würde, was FileSystemObject bedeutet).
In der zweiten Zeile passieren zwei Dinge:
- 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 Sie diese Instanz nicht erstellen, können Sie nicht auf die Methoden von FSO zugreifen.
- Das Schlüsselwort SET setzt das Objekt MyFSO auf diese neue Instanz von FileSystemObject. Dadurch kann ich dieses Objekt verwenden, um auf Dateien und Ordner zuzugreifen. Wenn ich beispielsweise einen Ordner erstellen muss, kann ich die MyFSO.CreateFolder-Methode verwenden.
Wenn Sie möchten, können Sie die beiden obigen Aussagen auch wie unten gezeigt zu einer kombinieren:
Sub CreatingFSO() Dim MyFSO As New FileSystemObject End Sub
Ein großer Vorteil dieser Methode (die darin besteht, den Verweis auf die Microsoft Scripting Runtime Library zu setzen) besteht darin, dass Sie bei Verwendung der FSO-Objekte in Ihrem Code die IntelliSense-Funktion verwenden können, die die zugehörigen Methoden und Eigenschaften anzeigt ein Objekt (wie unten gezeigt).
Dies ist nicht möglich, wenn Sie die Referenz innerhalb des Codes erstellen (wird als nächstes behandelt).
Erstellen eines Objekts aus dem Code
Eine andere Möglichkeit, einen Verweis auf FSO zu erstellen, besteht darin, dies über den Code zu tun. Bei dieser Methode müssen Sie keine Referenz erstellen (wie in der vorherigen Methode).
Beim Schreiben des Codes können Sie ein Objekt aus dem Code erstellen und auf das Scripting.FileSystemObject verweisen.
Der folgende Code erstellt ein Objekt-FSO und macht es dann zu einem FileSystemObject-Typ.
Sub FSODemo() Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") End Sub
Dies mag zwar bequemer erscheinen, ein großer Nachteil dieser Methode besteht jedoch darin, dass kein IntelliSense angezeigt wird, wenn Sie mit Objekten in FSO arbeiten. Für mich ist dies ein großer Nachteil und ich empfehle immer, die vorherige Methode zum Aktivieren von FSO zu verwenden (d. h. durch Setzen des Verweises auf die "Microsoft Scripting Runtime").
VBA FileSystemObject-Beispiele
Lassen Sie uns nun eintauchen und uns einige praktische Beispiele für die Verwendung von FileSystemObject in Excel ansehen.
Beispiel 1: Prüfen, ob eine Datei oder ein Ordner vorhanden ist
Der folgende Code prüft, ob der Ordner mit dem Namen „Test“ existiert oder nicht (am angegebenen Ort).
Wenn der Ordner vorhanden ist, ist die IF-Bedingung True und es wird eine Meldung angezeigt – „Der Ordner existiert“ in einem Meldungsfeld. Und wenn es nicht existiert, wird eine Meldung angezeigt – Der Ordner existiert nicht.
Sub CheckFolderExist() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "Der Ordner existiert" Else MsgBox "Der Ordner existiert nicht" End If End Sub
Ebenso können Sie auch prüfen, ob eine Datei existiert oder nicht.
Der folgende Code prüft, ob sich im angegebenen Ordner eine Datei mit dem Namen Test.xlsx befindet oder nicht.
Sub CheckFileExist() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists("C:\Users\sumit\Desktop\Test\Test.xlsx") Then MsgBox "Die Datei existiert" Else MsgBox "Die Datei existiert nicht "Ende wenn Ende Sub
Beispiel 2: Erstellen Sie einen neuen Ordner am angegebenen Speicherort
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 FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test") End Sub
Dieser Code funktioniert zwar einwandfrei, würde jedoch einen Fehler anzeigen, falls der Ordner bereits vorhanden ist.
Der folgende Code überprü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.
Sub CreateFolder() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "Der Ordner existiert bereits" Else MyFSO.CreateFolder ("C:\Users\ sumit\Desktop\Test") End If End Sub
Beispiel 3: Abrufen einer Liste aller Dateien in einem Ordner
Der folgende Code würde die Namen aller Dateien im angegebenen Ordner anzeigen.
Sub GetFileNames() Dim MyFSO As FileSystemObject Dim MyFile As File Dim MyFolder As Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop\Test") For Each MyFile In MyFolder.Files Debug.Print MyFile.Name Next MyFile End Sub
Dieser Code ist etwas komplexer als die, die wir bereits gesehen haben.
Wie ich oben in diesem Tutorial erwähnt habe, können Sie beim Verweisen auf die "Microsoft Scripting Runtime Library" FileSystemObject sowie alle anderen Objekte (z. B. Dateien und Ordner) verwenden.
Im obigen Code verwende ich drei Objekte - FileSystemObject, File und Folder. Dadurch kann ich jede Datei im angegebenen Ordner durchgehen. Ich verwende dann die name-Eigenschaft, um die Liste aller Dateinamen zu erhalten.
Beachten Sie, dass ich Debug.Print verwende, um die Namen aller Dateien abzurufen. 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 an. Die Logik ist genau dieselbe wie im obigen Beispiel. Anstelle von Dateien haben wir in diesem Code Unterordner verwendet.
Sub GetSubFolderNames() Dim MyFSO As FileSystemObject Dim MyFile As File Dim MyFolder As Folder Dim MySubFolder As Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop\Test") For Each MySubFolder In MyFolder.SubFolders Debug.Print MySubFolder.Name Next MySubFolder End Sub
Beispiel 5: Kopieren einer Datei von einem Ort an einen anderen
Der folgende Code kopiert die Datei aus dem Ordner „Source“ und kopiert sie in den Ordner „Destination“.
Sub CopyFile() Dim MyFSO As FileSystemObject Dim SourceFile As String Dim DestinationFolder As String Set MyFSO = New Scripting.FileSystemObject SourceFile = "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.
Quelldatei enthält die Adresse der Datei, die ich kopieren möchte, und die Variable DestinationFolder enthält die Adresse des Ordners, in den die Datei kopiert werden soll.
Beachten Sie, dass es nicht ausreicht, den Zielordnernamen anzugeben, wenn Sie eine Datei kopieren. Außerdem müssen Sie den Dateinamen angeben. Sie können denselben Dateinamen verwenden oder auch ändern. Im obigen Beispiel habe ich die Datei kopiert und sie SampleFileCopy.xlsx genannt
Beispiel 6: Alle Dateien von einem Ordner in einen anderen kopieren
Der folgende Code kopiert alle Dateien aus dem Quellordner in den Zielordner.
Sub CopyAllFiles() Dim MyFSO As FileSystemObject Dim MyFile As File Dim SourceFolder As String Dim DestinationFolder As String Dim MyFolder As Folder Dim MySubFolder As Folder SourceFolder = "C:\Users\sumit\Desktop\Source" DestinationFolder = "C:\Users\ sumit\Desktop\Destination" Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder(SourceFolder) For Each MyFile In MyFolder.Files MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _ Destination:=DestinationFolder & "\ " & MyFile.Name, Overwritefiles:=False Next MyFile End Sub
Der obige Code kopiert alle Dateien aus dem Quellordner in den Zielordner.
Beachten Sie, dass ich in der MyFSO.CopyFile-Methode die Eigenschaft 'Overwritefiles' auf False festgelegt habe (dies ist standardmäßig True). Dies stellt sicher, dass die Datei nicht kopiert wird, falls Sie die Datei bereits im Ordner haben (und Sie sehen einen Fehler). Wenn Sie „Overwritefiles“ entfernen oder auf „True“ setzen, werden Dateien mit dem gleichen Namen im Zielordner überschrieben.
Profi-Tipp: Beim Kopieren von Dateien besteht immer die Möglichkeit, Dateien zu überschreiben. In diesem Fall empfiehlt es sich, den Zeitstempel zusammen mit dem Namen hinzuzufügen. Dadurch wird sichergestellt, dass die Namen immer unterschiedlich sind und Sie leicht nachvollziehen können, welche Dateien zu welchem Zeitpunkt kopiert wurden.Wenn Sie nur die Dateien einer bestimmten Erweiterung kopieren möchten, können Sie dies tun, indem Sie eine IF Then-Anweisung verwenden, um zu überprüfen, ob die Erweiterung xlsx ist oder nicht.
Sub CopyExcelFilesOnly() Dim MyFSO As FileSystemObject Dim MyFile As File Dim SourceFolder As String Dim DestinationFolder As String Dim MyFolder As Folder Dim MySubFolder As Folder SourceFolder = "C:\Users\sumit\Desktop\Source" DestinationFolder = "C:\Users\ sumit\Desktop\Destination" Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder(SourceFolder) For Each MyFile In MyFolder.Files Wenn MyFSO.GetExtensionName(MyFile) = "xlsx" Then MyFSO.CopyFile Source:=MyFSO.GetFile (MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=False End If Next MyFile End Sub
FileSystemObject (FSO)-Methoden
Hier sind die Methoden, die Sie für jedes Objekt verwenden können. Dies ist nur zu Referenzzwecken und macht sich nicht zu viele Sorgen. Die Verwendung einiger davon wurde in den oben behandelten Beispielen gezeigt.
| BFS-Methoden | Für Objekt | Beschreibung |
| DriveExists | Antrieb | Prüft, ob das Laufwerk existiert oder nicht |
| GetDrive | Antrieb | Gibt eine Instanz des Antriebsobjekts basierend auf dem angegebenen Pfad zurück |
| GetDriveName | Antrieb | Führt den Laufwerksnamen erneut aus |
| BuildPath | Aktenordner | Generieren Sie einen Pfad aus einem vorhandenen Pfad und einem Namen |
| Datei kopieren | Aktenordner | Kopiert eine Datei |
| GetAbsolutePathName | Aktenordner | Gibt die kanonische Darstellung des Pfades zurück |
| GetBaseName | Aktenordner | Gibt den Basisnamen aus einem Pfad zurück. Beispiel: „D:\TestFolder\TestFile.xlsm“ gibt TextFile.xlsm zurück |
| GetTempName | Aktenordner | Generieren Sie einen Namen, der zum Benennen einer temporären Datei verwendet werden kann |
| Ordner kopieren | Ordner | Kopiert einen Ordner von einem Ort zum anderen |
| Ordner erstellen | Ordner | Erstellt einen neuen Ordner |
| Lösche Ordner | Ordner | Löscht den angegebenen Ordner |
| Ordner existiert | Ordner | Überprüft, ob der Ordner existiert oder nicht |
| GetFolder | Ordner | Gibt eine Instanz des Ordnerobjekts basierend auf dem angegebenen Pfad zurück |
| GetParentFolderName | Ordner | Gibt den Namen des übergeordneten Ordners basierend auf dem angegebenen Pfad erneut aus |
| GetSpecialFolder | Ordner | Rufen Sie den Speicherort verschiedener Systemordner ab. |
| Ordner verschieben | Ordner | Verschiebt einen Ordner von einem Ort an einen anderen |
| Datei löschen | Datei | Löscht eine Datei |
| Datei existiert | Datei | Prüft, ob eine Datei existiert oder nicht |
| GetExtensionName | Datei | Gibt die Dateierweiterung zurück |
| Eine Datei bekommen | Datei | Gibt die Instanz eines Dateiobjekts basierend auf dem angegebenen Pfad zurück |
| GetFileName | Datei | Gibt den Dateinamen zurück |
| GetFileVersion | Datei | Gibt die Dateiversion zurück |
| Datei bewegen | Datei | Verschiebt eine Datei |
| Textdatei erstellen | Datei | Erstellt eine Textdatei |
| GetStandardStream | Datei | Rufen Sie den Standardeingabe-, Ausgabe- oder Fehlerstrom ab |
| OpenTextFile | Datei | Öffnen Sie eine Datei als TextStream |
