VBA FileSystemObject (FSO) gebruiken in Excel – Eenvoudig overzicht & voorbeelden

Wanneer we VBA in Excel gebruiken, is dat meestal om onze taken te automatiseren.

Dit betekent ook dat we meestal werken met cellen en bereiken, werkbladen, werkmappen en andere objecten die deel uitmaken van de Excel-toepassing.

Maar VBA is veel krachtiger en kan ook worden gebruikt om te werken met dingen buiten Excel.

In deze tutorial laat ik u zien hoe u VBA FileSystemObject (FSO) kunt gebruiken om te werken met bestanden en mappen op uw systeem of netwerkstations.

Wat is VBA FileSystemObject (FSO)?

FileSystemObject (FSO) geeft u toegang tot het bestandssysteem van uw computer. Met behulp van het, kunt u toegang krijgen tot en wijzigen van de bestanden / mappen / mappen in uw computersysteem.

Bijvoorbeeld, hieronder zijn enkele van de dingen die je kunt doen met behulp van FileSystemObject in Excel VBA:

• Controleer of een bestand of een map bestaat.
• Maken of hernoemen van mappen / bestanden.
• Ontvang een lijst van alle bestandsnamen (of sub-map namen) in een map.
• Kopieer bestanden van de ene map naar de andere.

Ik hoop dat u het idee krijgt.

Ik zal al deze bovenstaande voorbeelden (plus meer) later in deze tutorial behandelen.

Hoewel sommige van de hierboven genoemde dingen ook kunnen worden gedaan met behulp van traditionele VBA-functies (zoals de DIR-functie) en methoden, zou dat leiden tot langere en meer gecompliceerde codes. FileSystemObject maakt het gemakkelijk om met bestanden en mappen te werken, terwijl de code schoon en kort blijft.

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

Welke Objecten kunt u allemaal benaderen met FileSystemObject?

Zoals gezegd, kunt u in VBA bestanden en mappen openen en wijzigen met behulp van het FileSystemObject.

Hieronder vindt u een tabel met de belangrijkste objecten die u met FSO kunt benaderen en wijzigen:

Elk van de bovenstaande objecten heeft methoden die u kunt gebruiken om hiermee te werken.

Om u een voorbeeld te geven, als u een map wilt verwijderen, gebruikt u de methode DeleteFolder van het object Folder. Wilt u een bestand kopiëren, dan gebruikt u de methode CopyFile van het object File.

Maakt u zich geen zorgen als dit overweldigend of moeilijk te begrijpen lijkt. U zult het veel beter begrijpen als u de voorbeelden doorloopt die ik in deze tutorial heb behandeld.

Alleen voor de referentie heb ik alle FileSystemObject methoden (voor elk object) aan het eind van deze tutorial behandeld.

Inschakelen FileSystemObject in Excel VBA

FileSystemObject is standaard niet beschikbaar in Excel VBA.

Omdat we te maken hebben met bestanden en mappen die buiten de Excel-applicatie staan, moeten we eerst een verwijzing maken naar de bibliotheek die deze objecten (stations, bestanden, mappen) bevat.

Nu zijn er twee manieren waarop u FileSystemObject in Excel VBA kunt gaan gebruiken:

1. De verwijzing naar de Microsoft Scripting Runtime Library (Scrrun.dll)
2. Een object maken om vanuit de code zelf naar de bibliotheek te verwijzen

Weliswaar werken beide methoden (en ik zal je hierna laten zien hoe je dit moet doen), maar ik raad je aan de eerste methode te gebruiken.

De verwijzing naar de Microsoft Scripting Runtime Library instellen

Wanneer je een verwijzing maakt naar de Scripting Runtime Library, geef je Excel VBA toegang tot alle eigenschappen en methoden van bestanden en mappen. Zodra dit is gedaan, kunt u verwijzen naar de bestanden / mappen / stations object vanuit Excel VBA (net zoals je kunt verwijzen naar de cellen, werkbladen of werkmappen).

Hieronder volgen de stappen om een verwijzing naar de Microsoft Scripting Runtime Library te maken:

1. In de VB-editor, klik op Tools.
2. Klik op References.
3. In het dialoogvenster References dat wordt geopend, bladert u door de beschikbare referenties en vinkt u de optie ‘Microsoft Scripting Runtime’ aan.
4. Klik op OK.

Met de bovenstaande stappen kunt u nu vanuit Excel VBA naar de FSO-objecten verwijzen.

Een instantie van FileSystemObject in de code maken

Nadat u de verwijzing naar de Scripting FileSystemObject bibliotheek hebt ingesteld, moet u een instantie van het FSO-object in uw code maken.

Als dit eenmaal is aangemaakt, kunt u het in VBA gebruiken.

Hieronder staat de code waarmee u de objectvariabele MyFSO instelt als een FileSystemObject-object:

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

In deze code heb ik eerst de variabele MyFSO gedeclareerd als een object van het type FileSystemObject. Dit is alleen mogelijk omdat ik een verwijzing naar de Microsoft Scripting Runtime Library heb gemaakt. Als de verwijzing niet is gemaakt, geeft dit een foutmelding (omdat Excel niet zou herkennen wat FileSystemObject betekent).

In de tweede regel gebeuren twee dingen:

1. Het NEW keyword creëert een instantie van het FileSystemObject. Dit betekent dat ik nu alle methoden van FileSystemObject kan gebruiken om met bestanden en mappen te werken. Als je deze instantie niet maakt, heb je geen toegang tot de methoden van FSO.
2. Het SET keyword stelt het object MyFSO in op deze nieuwe instantie van FileSystemObject. Hierdoor kan ik dit object gebruiken voor toegang tot bestanden en mappen. Als ik bijvoorbeeld een map moet maken, kan ik de methode MyFSO.CreateFolder gebruiken.

Als u wilt, kunt u de twee bovenstaande verklaringen ook combineren tot een, zoals hieronder wordt getoond:

Sub CreatingFSO()Dim MyFSO As New FileSystemObjectEnd Sub

Een groot voordeel van het gebruik van deze methode (namelijk het instellen van de verwijzing naar de Microsoft Scripting Runtime Library) is dat wanneer u de FSO-objecten in uw code gebruikt, u de IntelliSense-functie kunt gebruiken die de methoden en eigenschappen toont die bij een object horen (zoals hieronder getoond).

Dit is niet mogelijk wanneer u de verwijzing vanuit de code maakt (komt hierna aan de orde).

Een object vanuit de code maken

Een andere manier om een verwijzing naar FSO te maken is door dit vanuit de code te doen. In deze methode hoeft u geen verwijzing te maken (zoals in de vorige methode).

Wanneer u de code schrijft, kunt u een object maken vanuit de code en verwijzen naar het Scripting.FileSystemObject.

De onderstaande code maakt een object FSO en maakt dit vervolgens tot een FileSystemObject type.

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

Hoewel dit handiger lijkt, is een groot nadeel van het gebruik van deze methode dat het geen IntelliSense zou tonen wanneer je met objecten in FSO werkt. Voor mij is dit een groot nadeel en ik adviseer altijd om de vorige methode te gebruiken om FSO in te schakelen (dat is door de verwijzing naar de ‘Microsoft Scripting Runtime’ in te stellen)

VBA FileSystemObject Voorbeelden

Nu duiken we er in en kijken naar een aantal praktische voorbeelden van het gebruik van FileSystemObject in Excel.

Voorbeeld 1: Controleren of een bestand of map bestaat

De volgende code controleert of de map met de naam ‘Test’ bestaat of niet (op de opgegeven locatie).

Als de map bestaat, is de IF-conditie Waar en verschijnt er een bericht – ‘De map bestaat’ in een berichtvak. En als de map niet bestaat, verschijnt het bericht ‘De map bestaat niet’.

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

Ook kunt u controleren of een bestand bestaat of niet.

De onderstaande code controleert of er een bestand met de naam Test.xlsx in de opgegeven map is of niet.

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

Voorbeeld 2: Maak een nieuwe map op de opgegeven plaats

Onderstaande code maakt een map met de naam ‘Test’ op de C-schijf van mijn systeem (u moet het pad op uw systeem opgeven waar u de map wilt maken).

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

Deze code werkt prima, maar geeft een foutmelding als de map al bestaat.

Onderstaande code controleert of de map al bestaat en maakt een map aan als dat niet het geval is. In het geval dat de map al bestaat, wordt er een bericht getoond. Om te controleren of de map bestaat, heb ik de methode FolderExists van de FSO gebruikt.

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

Voorbeeld 3: Verkrijg een lijst van alle bestanden in een map

Onderstaande code toont de namen van alle bestanden in de gespecificeerde map.

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

Deze code is iets complexer dan de code die we eerder hebben gezien.

Zoals ik hierboven in deze tutorial heb vermeld, kunt u, wanneer u naar de ‘Microsoft Scripting Runtime Library’ verwijst, zowel FileSystemObject als alle andere objecten (zoals Bestanden en Mappen) gebruiken.

In de bovenstaande code gebruik ik drie objecten – FileSystemObject, Bestand en Map. Hiermee kan ik elk bestand in de gespecificeerde map doorlopen. Vervolgens gebruik ik de eigenschap name om de lijst met alle bestandsnamen te krijgen.

Merk op dat ik Debug.Print gebruik om de namen van alle bestanden te krijgen. Deze namen zullen worden vermeld in het onmiddellijke venster in de VB-editor.

Voorbeeld 4: Verkrijg de lijst van alle submappen in een map

De onderstaande code geeft de namen van alle submappen in de gespecificeerde map. De logica is precies dezelfde als in het bovenstaande voorbeeld. In plaats van bestanden, hebben we in deze code sub-mappen gebruikt.

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

Voorbeeld 5: Kopieer een bestand van de ene plaats naar de andere

Onderstaande code kopieert het bestand van de map ‘Bron’ en kopieert het naar de map ‘Bestemming’.

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

In bovenstaande code heb ik twee variabelen gebruikt – BronBestand en BestemmingsMap.

Bronbestand bevat het adres van het bestand dat ik wil kopiëren en de DestinationFolder-variabele bevat het adres van de map waarnaar ik het bestand wil kopiëren.

Merk op dat het niet voldoende is om de naam van de doelmap op te geven wanneer u een bestand kopieert. U moet ook de bestandsnaam opgeven. U kunt dezelfde bestandsnaam gebruiken of deze ook wijzigen. In het bovenstaande voorbeeld heb ik het bestand gekopieerd en het de naam SampleFileCopy.xlsx gegeven

Voorbeeld 6: Kopieer alle bestanden van de ene map naar de andere

De onderstaande code kopieert alle bestanden van de bronmap naar de doelmap.

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

De bovenstaande code kopieert alle bestanden van de bronmap naar de doelmap.

Merk op dat in de methode MyFSO.CopyFile, ik de eigenschap ‘Overwritefiles’ heb gespecificeerd op False (dit is standaard True). Dit zorgt ervoor dat in het geval je het bestand al in de map hebt, het niet gekopieerd wordt (en je een fout ziet). Als je ‘Overwritefiles’ verwijdert of op True zet, worden bestanden met dezelfde naam in de doelmap overschreven.

Als u alleen de bestanden van een bepaalde extensie wilt kopiëren, kunt u dat doen door een IF Then statement te gebruiken om te controleren of de extensie xlsx is of niet.

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) Methods

Hier zijn de methodes die u voor elk object kunt gebruiken. Dit is alleen voor referentie doeleinden en maak je er niet te veel zorgen over. Het gebruik van sommige van deze is getoond in de voorbeelden hierboven behandeld.

Je vindt de volgende Excel Tutorials misschien ook leuk:

• Een lijst met bestandsnamen uit mappen ophalen & Sub-mappen (met Power Query).
• Verkrijg de lijst met bestandsnamen van een map in Excel (met en zonder VBA).
• Inzicht in Excel VBA Data Types (Variabelen en Constanten).
• Het maken van een door de gebruiker gedefinieerde functie (UDF) in Excel VBA.