VBA heeft enkele handige functies die uw automatisering in Excel naar een hoger niveau kunnen tillen.
Een dergelijke functie is de VBA DIR-functie.
Hoewel het op zichzelf misschien een eenvoudige functie lijkt die één specifiek ding doet.
Maar als je het combineert met enkele andere nuttige elementen van de VBA-codeertaal, kun je krachtige dingen maken (die later in deze tutorial in de voorbeelden worden behandeld).
Wat doet de VBA Dir-functie?
Gebruik de VBA DIR-functie wanneer u de naam van het bestand of een map wilt krijgen, met behulp van hun padnaam.
Om u een voorbeeld te geven, als u een Excel-bestand in een map hebt, kunt u de VBA DIR-functie gebruiken om de naam van dat Excel-bestand (of een ander type bestand) te krijgen.
Wat als ik de namen van alle Excel-bestanden in de map wil krijgen (of alle bestanden - of het nu een Excel-bestand is of niet)?
Dat kan jij ook!
Wanneer u de DIR-functie eenmaal gebruikt, wordt de eerste bestandsnaam in een map. Als u nu ook de namen van de tweede, derde en vierde bestanden wilt hebben, kunt u de DIR-functie opnieuw gebruiken (later behandeld als voorbeeld).
Dir geeft de eerste bestandsnaam terug die overeenkomt met de padnaam. Om extra bestandsnamen te krijgen die overeenkomen met padnaam, bel Dir weer zonder argumenten. Als er geen bestandsnamen meer overeenkomen, Dir retourneert een tekenreeks met lengte nul (""). Behandeld in voorbeeld 3 en 4 verderop in deze tutorial.Syntaxis van VBA DIR-functie
Dir [ (padnaam [, attributen])]
- padnaam: Dit is een optioneel argument. Dit kan de bestandsnaam, mapnaam of mapnaam zijn. Als padnaam niet wordt gevonden, retourneert de VBA DIR-functie een tekenreeks met lengte nul ("")
- attributen: Dit is een optioneel argument. U kunt dit argument gebruiken om enkele kenmerken op te geven en de DIR-functie retourneert de bestandsnamen op basis van die kenmerken. Als u bijvoorbeeld een lijst wilt met alle verborgen bestanden of alleen-lezen bestanden (samen met bestanden zonder attributen), moet u dat in dit argument specificeren.
Attributen die beschikbaar zijn voor gebruik in de VBA DIR-functie (u kunt een of meer van deze gebruiken):
Constante | Waarde | Beschrijving |
vbNormaal | 0 | (Standaard) Specificeert bestanden zonder attributen. |
vbAlleen-lezen | 1 | Specificeert alleen-lezen bestanden naast bestanden zonder attributen. |
vbVerborgen | 2 | Specificeert verborgen bestanden naast bestanden zonder attributen. |
VbSysteem | 4 | Specificeert systeembestanden naast bestanden zonder attributen. Niet beschikbaar op de Macintosh. |
vbVolume | 8 | Specificeert volumelabel; als een ander kenmerk is opgegeven, wordt vbVolume genegeerd. Niet beschikbaar op de Macintosh. |
vbDirectory | 16 | Specificeert mappen of mappen naast bestanden zonder attributen. |
vbAlias | 64 | De opgegeven bestandsnaam is een alias. Alleen beschikbaar op de Macintosh. |
Jokertekens gebruiken met de DIR-functie
Als u met Windows werkt, kunt u ook de jokertekens gebruiken in de DIR-functie.
Merk op dat u deze niet kunt gebruiken wanneer u met VBA in Macintosh werkt.
Het gebruik van jokertekens kan handig zijn wanneer:
- U wilt de bestandsnamen van een bepaald bestandstype krijgen (zoals .XLSX of .PPTX)
- Wanneer u een specifiek achtervoegsel/voorvoegsel in bestandsnamen hebt en u wilt de namen van deze bestanden/mappen/mappen krijgen. Als u bijvoorbeeld de namen wilt van alle bestanden met het prefix2021-2022 erin, kunt u dat doen met jokertekens.
Er zijn drie jokertekens in Excel:
- * (sterretje) - Het vertegenwoordigt een willekeurig aantal tekens. Bijvoorbeeld, 2019* zou u de namen geven van alle bestanden met het voorvoegsel2021-2022 erin.
- ? (vraagteken) - Het vertegenwoordigt één enkel teken. Bijvoorbeeld 2021-2022? zou u de namen geven van alle bestanden die beginnen met 2021-2022 en nog een teken in de naam hebben (zoals2021-2022A, 2021-2022B, 2021-2022C, enzovoort)
Opmerking: er is nog een jokerteken - tilde (~). Omdat het niet veel wordt gebruikt, heb ik de uitleg overgeslagen. Bij interesse kun je er hier meer over lezen.
VBA DIR-functie - voorbeelden
Laten we nu een duik nemen en enkele voorbeelden bekijken van het gebruik van de VBA DIR-functie.
Voorbeeld 1 - De bestandsnaam uit het pad halen
Als je het pad van een bestand hebt, kun je de DIR-functie gebruiken om de naam van het bestand eruit te halen.
De onderstaande code retourneert bijvoorbeeld de naam van het bestand en toont het in een berichtvenster.
Sub GetFileNames() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\Excel File A.xlsx") MsgBox FileName End Sub
De bovenstaande code gebruikt een variabele 'FileName' om de bestandsnaam op te slaan die wordt geretourneerd door de DIR-functie. Vervolgens wordt een berichtvenster gebruikt om de bestandsnaam weer te geven (zoals hieronder weergegeven).
En wat gebeurt er als het bestand niet bestaat?
In dat geval zou de DIR-functie een lege string teruggeven.
De onderstaande code gebruikt een If Then Else-instructie om te controleren of het bestand bestaat of niet. Als het bestand niet bestaat, wordt een berichtvenster weergegeven met de tekst "Bestand bestaat niet", anders wordt de bestandsnaam weergegeven.
Sub CheckFileExistence() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\Excel File A.xlsx") If FileName "" Then MsgBox FileName Else MsgBox "File bestaat niet" End If End Sub
Voorbeeld 2 - Controleer of een directory bestaat of niet (en maak aan als deze niet bestaat)
Onderstaande code controleert of de map ‘Test’ bestaat of niet.
Een berichtvenster wordt gebruikt om een bericht weer te geven voor het geval de map wel of niet bestaat.
Sub CheckDirectory() Dim PathName As String Dim CheckDir As String PathName = "C:\Users\sumit\Desktop\Test" CheckDir = Dir(PathName, vbDirectory) If CheckDir "" Dan MsgBox CheckDir & " bestaat" Else MsgBox "De map bestaat niet" End If End Sub
U kunt deze code verder verfijnen om te controleren of de map bestaat of niet, en als dat niet het geval is, kunt u VBA gebruiken om die map te maken.
Hieronder staat de code die de . gebruikt MkDir-functie om een map te maken voor het geval deze niet bestaat.
Sub CreateDirectory() Dim PathName As String Dim CheckDir As String PathName = "C:\Users\sumit\Desktop\Test" CheckDir = Dir(PathName, vbDirectory) If CheckDir "" Dan MsgBox CheckDir & " folder bestaat" Else MkDir PathName MsgBox "Er is een map gemaakt met de naam" & CheckDir End If End Sub
Voorbeeld 3 - Haal de namen op van alle bestanden en mappen in een map
Als u een lijst wilt krijgen van alle bestands- en mapnamen in een map, kunt u de DIR-functie gebruiken.
De onderstaande code geeft een overzicht van alle bestanden en mapnamen in de map Test (die zich op het volgende pad bevindt - C:\Users\sumit\Desktop\Test\).
Ik gebruik Debug.Print om de namen in het venster Direct weer te geven. U kunt dit ook gebruiken om de namen in een berichtvenster of in een kolom in Excel weer te geven.
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
De Do While-lus in de bovenstaande code gaat door totdat alle bestanden en mappen in het opgegeven pad zijn afgedekt. Als er geen bestanden/mappen meer zijn om te dekken, wordt Bestandsnaam een null-tekenreeks en stopt de lus.
Voorbeeld 4 - Krijg de namen van alle bestanden in een map
U kunt de onderstaande code gebruiken om de namen van alle bestanden in een map/directory te krijgen (en niet de namen van de submappen).
Sub GetAllFileNames() Dim FileName As String FileName = Dir("C:\Users\sumit\Desktop\Test\") Do While FileName "" Debug.Print Bestandsnaam Bestandsnaam = Dir() Loop End Sub
Deze code is net als de code gebruikt in voorbeeld 3, met één klein verschil.
In deze code heb ik niet gespecificeerd vbDirectory in de DIR-functie. Wanneer u vbDirectory opgeeft, krijgt u de namen van alle bestanden en mappen.
Als u vbDirectory niet opgeeft, geeft de DIR-functie u alleen de namen van de bestanden.
Opmerking: Als u de namen van alle bestanden in de hoofdmap en de submappen wilt, kunt u de DIR-functie niet gebruiken (omdat deze niet recursief is). Om dit te doen, kunt u Power Query gebruiken (geen codering nodig) of het bestandssysteemobject in VBA gebruiken (met recursie).
Voorbeeld 5 - Haal de namen op van alle submappen in een map
De onderstaande code geeft u de namen van alle submappen in de opgegeven map.
Het gebruikt de GetAtr-functie in VBA, waarmee we kunnen controleren of de naam die wordt geretourneerd door de DIR-functie de naam is van een bestand of een map/directory.
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(PadName & FileName) = vbDirectory Dan Debug.Print FileName End If FileName = Dir() Loop End Sub
Nogmaals, ik gebruik Debug.Print om de namen in het directe venster te krijgen. U kunt deze in een berichtvenster of in Excel krijgen (door de code dienovereenkomstig aan te passen).
Voorbeeld 6 - Haal het eerste Excel-bestand uit een map
Met de DIR-functie kunt u de bestandsextensie of een achtervoegsel/voorvoegsel specificeren dat u wilt in de bestandsnaam die wordt geretourneerd.
De onderstaande code geeft de naam weer van het eerste Excel-bestand in de map Test.
Sub GetFirstExcelFileName() Dim FileName As String Dim PathName As String PathName = "C:\Users\sumit\Desktop\Test\" FileName = Dir(PathName & "*.xls*") MsgBox FileName End Sub
Merk op dat ik *.xls* heb gebruikt (sterretje aan beide kanten). Dit zorgt ervoor dat alle versies van Excel-bestanden worden gecontroleerd (.xls, xlsx, .xlsm, .xlsb).
Voorbeeld 7 - Namen van alle Excel-bestanden in een map ophalen
Gebruik de onderstaande code om de namen van alle Excel-bestanden in de map Test te krijgen.
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
Terwijl de DIR-functie alleen de naam van het eerste Excel-bestand retourneert, omdat we het opnieuw in de lus aanroepen, doorloopt het alle bestanden en geeft het ons de namen van alle Excel-bestanden.
Ik hoop dat je deze tutorial en de voorbeelden nuttig vond.
Laat me je mening weten in de comments.