VBA har noen nyttige funksjoner som kan ta automatiseringen i Excel til neste nivå.
En slik funksjon er VBA DIR -funksjon.
Selv om det i seg selv kan virke som en enkel funksjon som gjør en bestemt ting.
Men når du kombinerer det med noen andre nyttige elementer i VBA -kodingsspråket, kan du lage kraftige ting (dekket i eksemplene senere i denne opplæringen).
Hva gjør VBA Dir -funksjonen?
Bruk VBA DIR -funksjonen når du vil få navnet på filen eller en mappe ved å bruke banenavnet.
For å gi deg et eksempel, hvis du har en Excel -fil i en mappe, kan du bruke VBA DIR -funksjonen til å få navnet på den Excel -filen (eller annen filtype).
Hva om jeg vil få navnene på alle Excel -filene i mappen (eller alle filene - enten det er Excel -fil eller ikke)?
Det kan du også!
Når du bruker DIR -funksjonen en gang, returnerer den første filnavn i en mappe. Hvis du også vil få navnene på den andre, tredje, fjerde filen, kan du bruke DIR -funksjonen igjen (dekket senere som et eksempel).
Dir returnerer det første filnavnet som samsvarer med banenavnet. For å få flere filnavn som matcher banenavnet, ring Dir igjen uten argumenter. Når ikke flere filnavn samsvarer, Dir returnerer en streng med null lengde (“”). Dekket i eksempel 3 og 4 senere i denne opplæringen.Syntaks for VBA DIR -funksjon
Dir [(banenavn [, attributter])]
- banenavn: Dette er et valgfritt argument. Dette kan være filnavnet, mappenavnet eller katalognavnet. Hvis banenavn ikke blir funnet, returnerer VBA DIR-funksjonen en streng med null lengde (“”)
- attributter: Dette er et valgfritt argument. Du kan bruke dette argumentet til å spesifisere noen attributter, og DIR -funksjonen returnerer filnavnene basert på disse attributtene. For eksempel, hvis du vil ha en liste over alle skjulte filer eller skrivebeskyttede filer (sammen med filer uten attributter), må du spesifisere det i dette argumentet.
Attributter tilgjengelig for bruk i VBA DIR -funksjonen (du kan bruke en eller flere av disse):
| Konstant | Verdi | Beskrivelse |
| vbNormal | 0 | (Standard) Angir filer uten attributter. |
| vbReadOnly | 1 | Spesifiserer skrivebeskyttede filer i tillegg til filer uten attributter. |
| vbHidden | 2 | Spesifiserer skjulte filer i tillegg til filer uten attributter. |
| VbSystem | 4 | Spesifiserer systemfiler i tillegg til filer uten attributter. Ikke tilgjengelig på Macintosh. |
| vbVolume | 8 | Spesifiserer volumetikett; hvis noen andre attributter er spesifisert, ignoreres vbVolume. Ikke tilgjengelig på Macintosh. |
| vbDirectory | 16 | Spesifiserer kataloger eller mapper i tillegg til filer uten attributter. |
| vbAlias | 64 | Spesifisert filnavn er et alias. Bare tilgjengelig på Macintosh. |
Bruke jokertegn med DIR -funksjon
Hvis du jobber med Windows, kan du også bruke jokertegnene i DIR -funksjonen.
Vær oppmerksom på at du ikke kan bruke disse når du arbeider med VBA i Macintosh.
Å bruke jokertegn kan være nyttig når:
- Du vil få filnavnene til en bestemt filtype (for eksempel .XLSX eller .PPTX)
- Når du har et bestemt suffiks/prefiks i filnavn og du vil få navnene på disse filene/mappene/katalogene. For eksempel, hvis du vil ha navnene på alle filene med prefiks2021-2022 i den, kan du gjøre det ved hjelp av jokertegn.
Det er tre jokertegn i Excel:
- * (stjerne) - Det representerer et hvilket som helst antall tegn. For eksempel, 2019* vil gi deg navnene på alle filene med prefikset2021-2022 i den.
- ? (spørsmålstegn) - Det representerer en enkelt karakter. For eksempel 2021-2022? vil gi deg navnene på alle filene som starter med2021-2022 og har et tegn til i navnet (for eksempel2021-2022A, 2021-2022B, 2021-2022C, og så videre)
Merk: Det er enda et jokertegn - tilde (~). Siden det ikke er brukt mye, har jeg hoppet over forklaringen. Du kan lese mer om det her hvis du er interessert.
VBA DIR -funksjon - eksempler
La oss nå dykke ned og se noen eksempler på bruk av VBA DIR -funksjonen.
Eksempel 1 - Få filnavnet fra banen
Når du har banen til en fil, kan du bruke DIR -funksjonen til å hente navnet på filen fra den.
For eksempel returnerer koden nedenfor navnet på filen og viser den i en meldingsboks.
Sub GetFileNames () Dim FileName As String FileName = Dir ("C: \ Users \ sumit \ Desktop \ Test \ Excel File A.xlsx") MsgBox FileName End Sub
Koden ovenfor bruker en variabel ‘Filnavn’ for å lagre filnavnet som returneres av DIR -funksjonen. Den bruker deretter en meldingsboks for å vise filnavnet (som vist nedenfor).
Og hva skjer når filen ikke finnes?
I så fall returnerer DIR -funksjonen en tom streng.
Koden nedenfor bruker en If Then Else -setning for å kontrollere om filen finnes eller ikke. Hvis filen ikke eksisterer, viser den en meldingsboks med teksten "Filen eksisterer ikke", ellers viser den filnavnet.
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 Does Not Exist" End If End Under
Eksempel 2 - Sjekk om det finnes en katalog eller ikke (og opprett hvis den ikke gjør det)
Koden nedenfor sjekker om mappen "Test" eksisterer eller ikke.
En meldingsboks brukes til å vise en melding i tilfelle mappen eksisterer eller ikke finnes.
Sub CheckDirectory () Dim PathName As String Dim CheckDir As String PathName = "C: \ Users \ sumit \ Desktop \ Test" CheckDir = Dir (PathName, vbDirectory) If CheckDir "" Then MsgBox CheckDir & "exist" Else MsgBox "The directory eksisterer ikke "End If End Sub
Du kan avgrense denne koden ytterligere for å sjekke om mappen eksisterer eller ikke, og hvis den ikke gjør det, kan du bruke VBA til å lage den mappen.
Nedenfor er koden som bruker MkDir -funksjon å lage en mappe i tilfelle den ikke eksisterer.
Sub CreateDirectory () Dim PathName As String Dim CheckDir As String PathName = "C: \ Users \ sumit \ Desktop \ Test" CheckDir = Dir (PathName, vbDirectory) If CheckDir "" Then MsgBox CheckDir & "folder exist" Else MkDir PathName MsgBox "En mappe er opprettet med navnet" og CheckDir End If End Sub
Eksempel 3 - Få navnene på alle filer og mapper i en katalog
Hvis du vil få en liste over alle fil- og mappenavn i en katalog, kan du bruke DIR -funksjonen.
Koden nedenfor viser alle filer og mappenavn i Test -mappen (som ligger på følgende bane - C: \ Users \ sumit \ Desktop \ Test \).
Jeg bruker Debug.Print for å vise navnene i vinduet Umiddelbart. Du kan også bruke dette til å liste opp navnene i en meldingsboks eller i en kolonne i Excel.
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
Do While -løkken i koden ovenfor fortsetter til alle filene og mappene i den gitte banen er dekket. Når det ikke er flere filer/mapper å dekke, blir FileName en null -streng og sløyfen stopper.
Eksempel 4 - Få navnene på alle filer i en mappe
Du kan bruke koden nedenfor for å få navnene på alle filene i en mappe/katalog (og ikke navnene på undermappene).
Sub GetAllFileNames () Dim FileName As String FileName = Dir ("C: \ Users \ sumit \ Desktop \ Test \") Do While FileName "" Debug.Print FileName FileName = Dir () Loop End Sub
Denne koden er akkurat som koden som ble brukt i eksempel 3, med en liten forskjell.
I denne koden har jeg ikke spesifisert vbDirectory i DIR -funksjonen. Når du angir vbDirectory, vil den gi deg navnene på alle filene samt mapper.
Når du ikke spesifiserer vbDirectory, gir DIR -funksjonen deg bare navnene på filene.
Merk: Hvis du vil få navnene på alle filene i hovedmappen og undermappene, kan du ikke bruke DIR-funksjonen (siden den ikke er rekursiv). For å gjøre dette kan du enten bruke Power Query (ingen koding nødvendig) eller bruke filsystemobjektet i VBA (med rekursjon).
Eksempel 5 - Få navnene på alle undermappene i en mappe
Koden nedenfor vil gi deg navnene på alle undermappene i den angitte mappen.
Den bruker GetAtr -funksjon i VBA, som lar oss sjekke om navnet som returneres av DIR -funksjonen er navnet på en fil eller en mappe/katalog.
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 (PathName & FileName) = vbDirectory Then Debug.Print FileName End If FileName = Dir () Loop End Sub
Igjen, jeg bruker Debug.Print for å få navnene i det umiddelbare vinduet. Du kan få disse i en meldingsboks eller i Excel (ved å endre koden tilsvarende).
Eksempel 6 - Få den første Excel -filen fra en mappe
Med DIR -funksjonen kan du angi filtypen eller et hvilket som helst suffiks/prefiks du vil ha i filnavnet som returneres.
Koden nedenfor viser navnet på den første Excel -filen i Test -mappen.
Sub GetFirstExcelFileName () Dim FileName As String Dim PathName As String PathName = "C: \ Users \ sumit \ Desktop \ Test \" FileName = Dir (PathName & "*.xls*") MsgBox FileName End Sub
Vær oppmerksom på at jeg har brukt * .xls * (stjerne på begge sider). Dette vil sikre at alle versjoner av Excel -filer er merket (.xls, xlsx, .xlsm, .xlsb).
Eksempel 7 - Få navn på alle Excel -filer i en mappe
Bruk koden nedenfor for å få navnene på alle Excel -filene i Test -mappen.
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
Mens DIR -funksjonen bare returnerer navnet på den første Excel -filen, siden vi kaller den igjen i sløyfen, går den gjennom alle filene og gir oss navnene på alle Excel -filene.
Håper du fant denne opplæringen og eksemplene nyttige.
Gi meg beskjed om tankene dine i kommentarfeltet.
