Wanneer we VBA in Excel gebruiken, is het meeste 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 een stuk krachtiger en kan ook worden gebruikt om met dingen buiten Excel te werken.
In deze zelfstudie laat ik u zien hoe u VBA FileSystemObject (FSO) kunt gebruiken om met bestanden en mappen op uw systeem of netwerkstations te werken.
Wat is VBA FileSystemObject (FSO)?
Met FileSystemObject (FSO) hebt u toegang tot het bestandssysteem van uw computer. Hiermee kunt u de bestanden/mappen/mappen in uw computersysteem openen en wijzigen.
Hieronder staan bijvoorbeeld enkele dingen die u kunt doen door FileSystemObject in Excel VBA te gebruiken:
- Controleer of er een bestand of map bestaat.
- Mappen/bestanden maken of hernoemen.
- Krijg een lijst met alle bestandsnamen (of submapnamen) in een map.
- Kopieer bestanden van de ene map naar de andere.
Ik hoop dat je het idee snapt.
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 gecompliceerdere codes. FileSystemObject maakt het gemakkelijk om met bestanden en mappen te werken terwijl de code schoon en kort blijft.
Opmerking: FSO kan alleen worden gebruikt in Excel 2000 en latere versies.
Tot welke objecten heb je toegang via FileSystemObject?
Zoals ik hierboven al zei, kunt u bestanden en mappen openen en wijzigen met behulp van het FileSystemObject in VBA.
Hieronder vindt u een tabel met de belangrijkste objecten die u kunt openen en wijzigen met FSO:
| Object | Beschrijving |
| Drijfveer | Met Drive Object kunt u informatie krijgen over de schijf, zoals of deze bestaat of niet, de padnaam, het type schijf (verwijderbaar of vast), de grootte, enz. |
| Map | Met het mapobject kunt u mappen in uw systeem maken of wijzigen. U kunt met dit object bijvoorbeeld mappen maken, verwijderen, hernoemen en kopiëren. |
| Bestand | Met Bestandsobject kunt u werken met bestanden in uw systeem. Met dit object kunt u bijvoorbeeld bestanden maken, openen, kopiëren, verplaatsen en verwijderen. |
| TekstStream | Met het TextStream-object kunt u tekstbestanden maken of lezen. |
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 Folder-object. Evenzo, als u een bestand wilt kopiëren, gebruikt u de methode CopyFile van het File-object.
Maak je geen zorgen als dit overweldigend of moeilijk te begrijpen lijkt. U krijgt een veel beter begrip als u de voorbeelden doorloopt die ik in deze zelfstudie heb behandeld.
Alleen voor het referentiedoel heb ik alle FileSystemObject-methoden (voor elk object) aan het einde van deze tutorial behandeld.
FileSystemObject inschakelen in Excel VBA
FileSystemObject is niet standaard beschikbaar in Excel VBA.
Aangezien we te maken hebben met bestanden en mappen die zich buiten de Excel-toepassing bevinden, moeten we eerst een verwijzing maken naar de bibliotheek die deze objecten bevat (stations, bestanden, mappen).
Er zijn nu twee manieren waarop u FileSystemObject in Excel VBA kunt gaan gebruiken:
- De verwijzing naar de Microsoft Scripting Runtime Library (Scrrun.dll) instellen
- Een object maken om vanuit de code zelf naar de bibliotheek te verwijzen
Hoewel beide methoden werken (en ik zal je hierna laten zien hoe je dit moet doen), raad ik aan om de eerste methode te gebruiken.
Opmerking: wanneer u FileSystemObject inschakelt, hebt u toegang tot alle objecten erin. Dit omvat het FileSystemObject, Drive, Files, Folders, enz. In deze tutorial zal ik me voornamelijk concentreren op het FileSystemObject.De verwijzing naar de Microsoft Scripting Runtime Library instellen
Wanneer u een verwijzing naar de Scripting Runtime Library maakt, geeft u Excel VBA toegang tot alle eigenschappen en methoden van bestanden en mappen. Zodra dit is gebeurd, kunt u vanuit Excel VBA naar het object bestanden/mappen/stations verwijzen (net zoals u naar de cellen, werkbladen of werkmappen kunt verwijzen).
Hieronder vindt u de stappen om een verwijzing naar de Microsoft Scripting Runtime Library te maken:
- Klik in de VB-editor op Extra.
- Klik op Referenties.
- Blader in het dialoogvenster Referenties dat wordt geopend door de beschikbare referenties en vink de optie 'Microsoft Scripting Runtime' aan.
- Klik OK.
Met de bovenstaande stappen kunt u nu verwijzen naar de FSO-objecten vanuit Excel VBA.
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.
Zodra dit is gemaakt, kunt u het in VBA gebruiken.
Hieronder staat de code die de objectvariabele MyFSO instelt als een FileSystemObject-object:
Sub CreatingFSO() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub
In deze code heb ik eerst de variabele MyFSO gedeclareerd als een FileSystemObject-type object. Dit is alleen mogelijk omdat ik een verwijzing naar de Microsoft Scripting Runtime Library heb gemaakt. Als de verwijzing niet is gemaakt, krijgt u een foutmelding (omdat Excel niet zou herkennen wat FileSystemObject betekent).
In de tweede regel gebeuren er twee dingen:
- Het trefwoord NEW maakt een instantie van het FileSystemObject. Dit betekent dat ik nu alle methoden van FileSystemObject kan gebruiken om met bestanden en mappen te werken. Als u deze instantie niet maakt, heeft u geen toegang tot de methoden van FSO.
- Het sleutelwoord SET stelt het object MyFSO in op dit nieuwe exemplaar van FileSystemObject. Hierdoor kan ik dit object gebruiken om toegang te krijgen tot bestanden en mappen. Als ik bijvoorbeeld een map moet maken, kan ik de MyFSO.CreateFolder-methode gebruiken.
Als je wilt, kun je de bovenstaande twee uitspraken ook combineren tot één, zoals hieronder weergegeven:
Sub CreateFSO() Dim MyFSO As New FileSystemObject End Sub
Een groot voordeel van het gebruik van deze methode (die de verwijzing naar de Microsoft Scripting Runtime Library instelt) is dat wanneer u de FSO-objecten in uw code gebruikt, u de IntelliSense-functie kunt gebruiken die de methoden en eigenschappen toont die zijn gekoppeld aan een object (zoals hieronder weergegeven).
Dit is niet mogelijk wanneer u de referentie maakt vanuit de code (hierna beschreven).
Een object maken op basis van de code
Een andere manier om een verwijzing naar FSO te maken, is door dit vanuit de code te doen. Bij deze methode hoeft u geen referentie te maken (zoals bij de vorige methode).
Wanneer u de code schrijft, kunt u een object maken vanuit de code en verwijzen naar de Scripting.FileSystemObject.
De onderstaande code maakt een object-FSO en maakt dit vervolgens tot een FileSystemObject-type.
Sub FSODemo() Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") End Sub
Hoewel dit misschien handiger lijkt, is een groot nadeel van het gebruik van deze methode dat er geen IntelliSense wordt weergegeven wanneer u met objecten in FSO werkt. Voor mij is dit een enorm negatief punt en ik raad altijd aan om de vorige methode te gebruiken om FSO in te schakelen (dat is door de verwijzing naar de 'Microsoft Scripting Runtime' in te stellen)
Voorbeelden van VBA-bestandssysteemobject
Laten we nu een duik nemen en enkele praktische voorbeelden bekijken van het gebruik van FileSystemObject in Excel.
Voorbeeld 1: Controleer 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-voorwaarde True en wordt een bericht weergegeven - 'The Folder Exists' in een berichtvenster. En als het niet bestaat, wordt een bericht weergegeven - De map bestaat niet'.
Sub CheckFolderExist() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Dan MsgBox "The Folder Exists" Anders MsgBox "The Folder Bestaat niet" End If End Sub
Op dezelfde manier kunt u ook 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 FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists("C:\Users\sumit\Desktop\Test\Test.xlsx") Dan MsgBox "The File Exists" Anders MsgBox "The File Exist " Einde Als Einde Sub
Voorbeeld 2: Maak een nieuwe map op de opgegeven locatie
De onderstaande code zou een map maken met de naam 'Test' in de C-schijf van mijn systeem (u moet het pad op uw systeem specificeren waar u de map wilt maken).
Sub CreateFolder() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test") End Sub
Hoewel deze code prima werkt, zou er een fout worden weergegeven als de map al bestaat.
De onderstaande code controleert of de map al bestaat en maakt een map aan als dat niet het geval is. Als de map al bestaat, wordt er een bericht weergegeven. Om te controleren of de map bestaat, heb ik de FolderExists-methode van de FSO.
Sub CreateFolder() Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Dan MsgBox "De map bestaat al" Anders MyFSO.CreateFolder ("C:\Users\ sumit\Desktop\Test") End If End Sub
Voorbeeld 3: Krijg een lijst van alle bestanden in een map
De onderstaande code toont de namen van alle bestanden in de opgegeven map.
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") voor elk MyFile in MyFolder. Debug.Print MyFile.Name Volgende MyFile End Sub
Deze code is iets complexer dan degene die we al hebben gezien.
Zoals ik hierboven in deze zelfstudie al zei, kunt u, wanneer u verwijst naar de 'Microsoft Scripting Runtime Library', FileSystemObject gebruiken, evenals alle andere objecten (zoals bestanden en mappen).
In de bovenstaande code gebruik ik drie objecten - FileSystemObject, File en Folder. Hierdoor kan ik elk bestand in de opgegeven 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 worden weergegeven in het directe venster in de VB Editor.
Voorbeeld 4: Krijg de lijst met alle submappen in een map
De onderstaande code geeft de namen van alle submappen in de opgegeven map. De logica is precies hetzelfde als in het bovenstaande voorbeeld. In plaats van bestanden hebben we in deze code submappen gebruikt.
Sub GetSubFolderNames() Dim MyFSO As FileSystemObject Dim MyFile als bestand Dim MyFolder als map Dim MySubFolder als map Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop For Each MySub\Test") In MyFolder.SubFolders Debug.Print MySubFolder.Name Volgende MySubFolder End Sub
Voorbeeld 5: Kopieer een bestand van de ene plaats naar de andere
De onderstaande code kopieert het bestand uit de map 'Bron' en kopieert het naar de map 'Bestemming'.
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
In de bovenstaande code heb ik twee variabelen gebruikt: SourceFile en DestinationFolder.
Bronbestand bevat het adres van het bestand dat ik wil kopiëren en de variabele DestinationFolder 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 SampleFileCopy.xlsx genoemd
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 FileSystemObject Dim MyFile As File Dim SourceFolder As String Dim DestinationFolder As String Dim MyFolder As Folder Dim MySubFolder als map 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
De bovenstaande code kopieert alle bestanden van de bronmap naar de doelmap.
Merk op dat ik in de MyFSO.CopyFile-methode de eigenschap 'Overwritefiles' heb opgegeven als False (dit is standaard True). Dit zorgt ervoor dat als u het bestand al in de map heeft, het niet wordt gekopieerd (en u een foutmelding krijgt). Als u 'Overwritefiles' verwijdert of dit instelt op True, als er bestanden in de doelmap staan met dezelfde naam, worden deze overschreven.
Pro-tip: Bij het kopiëren van bestanden is er altijd een kans dat bestanden worden overschreven. In dit geval is het een goed idee om de tijdstempel samen met de naam toe te voegen. Dit zorgt ervoor dat de namen altijd anders zijn en u kunt eenvoudig volgen welke bestanden op welk moment zijn gekopieerd.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 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) voor elk MyFile in MyFolder.Files If MyFSO.GetExtensionName(MyFile) = "xlsx" Then MyFSO. (MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=False End If Next MyFile End Sub
FileSystemObject (FSO)-methoden
Dit zijn de methoden die u voor elk object kunt gebruiken. Dit is alleen voor referentiedoeleinden en maakt u er niet al te veel zorgen over. Het gebruik van sommige hiervan is aangetoond in de bovenstaande voorbeelden.
| FSO-methoden | Voor object | Beschrijving |
| Aandrijving bestaat | Drijfveer | Controleert of de schijf bestaat of niet |
| GetDrive | Drijfveer | Retourneert een instantie van het stationsobject op basis van het opgegeven pad |
| GetDriveName | Drijfveer | Voert de stationsnaam opnieuw uit |
| Bouwpad | Dossiermap | Genereer een pad van een bestaand pad en een naam |
| Kopieer bestand | Dossiermap | Kopieert een bestand |
| GetAbsolutePathName | Dossiermap | Retourneer de canonieke weergave van het pad |
| GetBaseName | Dossiermap | Retourneer de basisnaam van een pad. Bijvoorbeeld: "D:\TestFolder\TestFile.xlsm" zal TextFile.xlsm retourneren |
| GetTempName | Dossiermap | Genereer een naam die kan worden gebruikt om een tijdelijk bestand een naam te geven |
| Kopieer map | Map | Kopieert een map van de ene locatie naar de andere |
| Map aanmaken | Map | Maakt een nieuwe map aan |
| Verwijder map | Map | Verwijdert de opgegeven map |
| Map bestaat | Map | Controleert of de map bestaat of niet |
| Map ophalen | Map | Retourneert een instantie van het mapobject op basis van het opgegeven pad |
| GetParentFolderName | Map | Voert de naam van de bovenliggende map opnieuw uit op basis van het opgegeven pad |
| Speciale map ophalen | Map | Krijg de locatie van verschillende systeemmappen. |
| Map verplaatsen | Map | Verplaatst een map van de ene naar de andere locatie |
| Verwijder bestand | Bestand | Verwijdert een bestand |
| Bestand bestaat | Bestand | Controleert of een bestand bestaat of niet |
| GetExtensionName | Bestand | Retourneert de bestandsextensie |
| GetFile | Bestand | Retourneert de instantie van een bestandsobject op basis van het opgegeven pad |
| Bestandsnaam ophalen | Bestand | Geeft de bestandsnaam terug |
| GetFileVersie | Bestand | Retourneert de bestandsversie |
| VerplaatsBestand | Bestand | Verplaatst een bestand |
| Tekstbestand maken | Bestand | Creëert een tekstbestand |
| GetStandardStream | Bestand | De standaard invoer-, uitvoer- of foutstroom ophalen |
| OpenTextFile | Bestand | Een bestand openen als een TextStream |
