Når vi bruger VBA i Excel, er det meste at automatisere vores opgaver.
Dette betyder også, at vi for det meste arbejder med celler og områder, regneark, projektmapper og andre objekter, der er en del af Excel -applikationen.
Men VBA er meget mere kraftfuld og kan også bruges til at arbejde med ting uden for Excel.
I denne vejledning viser jeg dig, hvordan du bruger VBA FileSystemObject (FSO) til at arbejde med filer og mapper på dit system eller netværksdrev.
Hvad er VBA FileSystemObject (FSO)?
FileSystemObject (FSO) giver dig adgang til filsystemet på din computer. Ved hjælp af det kan du få adgang til og ændre filerne/mapperne/mapperne i dit computersystem.
Nedenfor er for eksempel nogle af de ting, du kan gøre ved at bruge FileSystemObject i Excel VBA:
- Kontroller, om der findes en fil eller en mappe.
- Opret eller omdøb mapper/filer.
- Få en liste over alle filnavnene (eller undermappens navne) i en mappe.
- Kopier filer fra en mappe til en anden.
Jeg håber du får ideen.
Jeg vil dække alle ovenstående eksempler (plus flere) senere i denne vejledning.
Selvom nogle af de ovennævnte ting også kan udføres ved hjælp af traditionelle VBA -funktioner (såsom DIR -funktionen) og metoder, ville det føre til længere og mere komplicerede koder. FileSystemObject gør det let at arbejde med filer og mapper, samtidig med at koden holdes ren og kort.
Bemærk: FSO kan kun bruges i Excel 2000 og nyere versioner.
Hvad kan alle objekter få adgang til via FileSystemObject?
Som jeg nævnte ovenfor, kan du få adgang til og ændre filer og mapper ved hjælp af FileSystemObject i VBA.
Nedenfor er en tabel, der viser de vigtigste objekter, som du kan få adgang til og ændre ved hjælp af FSO:
| Objekt | Beskrivelse |
| Køre | Drive Object giver dig mulighed for at få oplysninger om drevet, f.eks. Om det eksisterer eller ej, det er stinavn, drevtype (aftagelig eller fast), dens størrelse osv. |
| Folder | Mappeobjekt giver dig mulighed for at oprette eller ændre mapper i dit system. For eksempel kan du oprette, slette, omdøbe, kopiere mapper ved hjælp af dette objekt. |
| Fil | File Object giver dig mulighed for at arbejde med filer i dit system. For eksempel kan du oprette, åbne, kopiere, flytte og slette filer ved hjælp af dette objekt. |
| TextStream | TextStream -objekt giver dig mulighed for at oprette eller læse tekstfiler. |
Hver af de ovennævnte objekter har metoder, som du kan bruge til at arbejde med disse.
For at give dig et eksempel, hvis du vil slette en mappe, vil du bruge metoden DeleteFolder for mappeobjektet. På samme måde, hvis du vil kopiere en fil, vil du bruge CopyFile -metoden for filobjektet.
Bare rolig, hvis dette virker overvældende eller svært at forstå. Du får en meget bedre forståelse, når du går igennem eksemplerne, som jeg har dækket i denne vejledning.
Bare til referenceformål har jeg dækket alle FileSystemObject -metoderne (for hvert objekt) i slutningen af denne vejledning.
Aktivering af FileSystemObject i Excel VBA
FileSystemObject er ikke tilgængelig som standard i Excel VBA.
Da vi har at gøre med filer og mapper, der er uden for Excel -applikationen, skal vi først oprette en reference til biblioteket, der indeholder disse objekter (drev, filer, mapper).
Nu er der to måder, du kan begynde at bruge FileSystemObject i Excel VBA:
- Indstilling af referencen til Microsoft Scripting Runtime Library (Scrrun.dll)
- Oprettelse af et objekt til henvisning til biblioteket ud fra selve koden
Selvom begge disse metoder virker (og jeg viser dig, hvordan du gør det næste), anbefaler jeg at bruge den første metode.
Bemærk: Når du aktiverer FileSystemObject, har du adgang til alle objekterne i den. Dette inkluderer FileSystemObject, Drive, Files, Folders osv. Jeg fokuserer hovedsageligt på FileSystemObject i denne vejledning.Indstilling af referencen til Microsoft Scripting Runtime Library
Når du opretter en reference til Scripting Runtime Library, giver du Excel VBA adgang til alle egenskaber og metoder til filer og mapper. Når dette er gjort, kan du henvise til filer/mapper/drev -objektet indefra Excel VBA (ligesom du kan henvise til cellerne, regnearkene eller projektmapperne).
Nedenfor er trinene til at oprette en reference til Microsoft Scripting Runtime Library:
- Klik på Værktøjer i VB Editor.
- Klik på Referencer.
- I dialogboksen Referencer, der åbnes, skal du rulle gennem de tilgængelige referencer og kontrollere indstillingen 'Microsoft Scripting Runtime'.
- Klik på OK.
Ovenstående trin giver dig nu mulighed for at henvise til FSO -objekterne fra Excel VBA.
Oprettelse af en forekomst af FileSystemObject i koden
Når du har angivet referencen til Scripting FileSystemObject -biblioteket, skal du oprette en forekomst af FSO -objektet i din kode.
Når dette er oprettet, kan du bruge det i VBA.
Nedenfor er koden, der sætter objektvariablen MyFSO som et FileSystemObject -objekt:
Sub CreatingFSO () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub
I denne kode har jeg først erklæret variablen MyFSO som et FileSystemObject -typeobjekt. Dette er kun muligt, fordi jeg har oprettet en reference til Microsoft Scripting Runtime Library. Hvis referencen ikke er oprettet, giver dette dig en fejl (da Excel ikke ville genkende, hvad FileSystemObject betyder).
I den anden linje sker der to ting:
- Det NYE søgeord opretter en forekomst af FileSystemObject. Det betyder, at jeg nu kan bruge alle metoderne i FileSystemObject til at arbejde med filer og mapper. Hvis du ikke opretter denne forekomst, har du ikke adgang til FSO's metoder.
- SET -søgeordet sætter objektet MyFSO til denne nye forekomst af FileSystemObject. Dette giver mig mulighed for at bruge dette objekt til at få adgang til filer og mapper. For eksempel, hvis jeg skal oprette en mappe, kan jeg bruge MyFSO.CreateFolder -metoden.
Hvis du vil, kan du også kombinere de ovenstående to udsagn til et som vist herunder:
Sub CreatingFSO () Dim MyFSO As New FileSystemObject End Sub
En stor fordel ved at bruge denne metode (det vil sige at angive referencen til Microsoft Scripting Runtime Library) er, at når du bruger FSO -objekterne i din kode, vil du kunne bruge IntelliSense -funktionen, der viser de metoder og egenskaber, der er forbundet med et objekt (som vist nedenfor).
Dette er ikke muligt, når du opretter referencen inde fra koden (dækkes derefter).
Oprettelse af et objekt fra koden
En anden måde at oprette en reference til FSO på er ved at gøre det ud fra koden. I denne metode behøver du ikke oprette nogen reference (som det blev gjort i den tidligere metode).
Når du skriver koden, kan du oprette et objekt inde fra koden og henvise til Scripting.FileSystemObject.
Nedenstående kode opretter et objekt FSO og gør derefter dette til en FileSystemObject -type.
Sub FSODemo () Dim FSO som objektsæt FSO = CreateObject ("Scripting.FileSystemObject") End Sub
Selvom dette kan virke mere bekvemt, er en stor ulempe ved at bruge denne metode, at den ikke viser en IntelliSense, når du arbejder med objekter i FSO. For mig er dette et enormt negativt, og jeg anbefaler altid at bruge den tidligere metode til at aktivere FSO (hvilket er ved at angive referencen til 'Microsoft Scripting Runtime')
VBA -filsystemsystemobjekter
Lad os nu dykke ned og se på nogle praktiske eksempler på at bruge FileSystemObject i Excel.
Eksempel 1: Kontroller, om der findes en fil eller mappe
Følgende kode vil kontrollere, om mappen med navnet 'Test' findes eller ej (på det angivne sted).
Hvis mappen findes, er IF -betingelsen sand, og der vises en meddelelse - 'Mappen findes' i en meddelelsesboks. Og hvis den ikke findes, viser den en besked - mappen findes ikke ’.
Sub CheckFolderExist () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Så MsgBox "Mappen findes" Ellers MsgBox "Mappen eksisterer ikke" End if End Sub
På samme måde kan du også kontrollere, om der findes en fil eller ej.
Nedenstående kode kontrollerer, om der er en fil med navnet Test.xlsx i den angivne mappe eller ej.
Sub CheckFileExist () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists ("C: \ Users \ sumit \ Desktop \ Test \ Test.xlsx") Så MsgBox "Filen findes" Ellers MsgBox "Filen findes ikke "Afslut hvis slut Sub
Eksempel 2: Opret en ny mappe på den angivne placering
Nedenstående kode ville oprette en mappe med navnet 'Test' i mit systems C -drev (du bliver nødt til at angive stien på dit system, hvor du vil oprette mappen).
Sub CreateFolder () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test") End Sub
Selvom denne kode fungerer fint, viser den en fejl, hvis mappen allerede findes.
Nedenstående kode kontrollerer, om mappen allerede findes, og opretter en mappe, hvis den ikke gør det. Hvis mappen allerede findes, viser den en meddelelse. For at kontrollere, om mappen findes, har jeg brugt FolderExists metode af FSO.
Sub CreateFolder () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Så MsgBox "Mappen findes allerede" Else MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test ") End If End Sub
Eksempel 3: Få en liste over alle filer i en mappe
Nedenstående kode viser navnene på alle filerne i den angivne mappe.
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 hver MyFile i MyFolder.Files Debug.Print MyFile.Name Næste MyFile End Sub
Denne kode er lidt mere kompleks end dem, vi allerede har set.
Som jeg nævnte ovenfor i denne vejledning, når du refererer til 'Microsoft Scripting Runtime Library', kan du bruge FileSystemObject såvel som alle andre objekter (f.eks. Filer og mapper).
I ovenstående kode bruger jeg tre objekter - FileSystemObject, File og Folder. Dette giver mig mulighed for at gå igennem hver fil i den angivne mappe. Jeg bruger derefter egenskaben navn til at få listen over alle filnavne.
Bemærk, at jeg bruger Debug.Print til at hente navnene på alle filerne. Disse navne vil blive angivet i det umiddelbare vindue i VB Editor.
Eksempel 4: Få listen over alle undermapper i en mappe
Nedenstående kode giver navnene på alle undermapperne i den angivne mappe. Logikken er nøjagtig den samme som dækket i ovenstående eksempel. I stedet for filer, i denne kode, har vi brugt undermapper.
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 hver MySubF I MyFolder.SubFolders Debug.Print MySubFolder.Name Næste MySubFolder End Sub
Eksempel 5: Kopier en fil fra et sted til et andet
Nedenstående kode vil kopiere filen fra mappen 'Kilde' og kopiere den til 'Destination' -mappen.
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
I ovenstående kode har jeg brugt to variabler - SourceFile og DestinationFolder.
Kildefil indeholder adressen på den fil, jeg vil kopiere, og variablen DestinationFolder gemmer adressen til den mappe, jeg vil have filen til at blive kopieret til.
Bemærk, at det ikke er tilstrækkeligt at angive destinationsmappens navn, når du kopierer en fil. Du skal også angive filnavnet. Du kan bruge det samme filnavn eller kan også ændre det. I ovenstående eksempel kopierede jeg filen og gav den navnet SampleFileCopy.xlsx
Eksempel 6: Kopier alle filer fra en mappe til en anden
Nedenstående kode vil kopiere alle filerne fra kildemappen til destinationsmappen.
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 Every MyFile In MyFolder.Files MyFSO.CopyFile Source: = MyFSO.GetFile (MyFile), _ Destination: = DestinationFolder &" \ "& MyFile.Name, Overwritefiles: = False Next MyFile End Sub
Ovenstående kode vil kopiere alle filerne fra kildemappen til destinationsmappen.
Bemærk, at i MyFSO.CopyFile -metoden har jeg angivet egenskaben 'Overwritefiles' til at være falsk (dette er sandt som standard). Dette sikrer, at hvis du allerede har filen i mappen, er den ikke kopieret (og du får vist en fejl). Hvis du fjerner 'Overwritefiles' eller indstiller dette til True, hvis der er filer i destinationsmappen med samme navn, ville disse blive overskrevet.
Pro tip: Når du kopierer filer, er der altid en chance for at overskrive filer. En god idé i dette tilfælde er at tilføje tidsstemplet sammen med navnet. Dette vil sikre, at navnene altid er forskellige, og du kan nemt spore, hvilke filer der blev kopieret på hvilket tidspunkt.Hvis du kun vil kopiere filerne i en bestemt udvidelse, kan du gøre det ved at bruge en IF Then -erklæring til at kontrollere, om udvidelsen er xlsx eller ej.
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 Every MyFile In MyFolder.Files If MyFSO.GetExtensionName (MyFile) =" xlsx "Then MyFSO.CopyFile Source: = MyFSO.Gil (MyFile), _ Destination: = DestinationFolder & "\" & MyFile.Name, Overwritefiles: = False End If Next MyFile End Sub
FileSystemObject (FSO) metoder
Her er de metoder, du kan bruge til hvert objekt. Dette er kun til referenceformål og bekymrer dig ikke for meget om det. Brugen af nogle af disse er blevet vist i eksemplerne dækket ovenfor.
| FSO -metoder | Til Object | Beskrivelse |
| DriveExists | Køre | Kontrollerer, om drevet findes eller ej |
| GetDrive | Køre | Returnerer en forekomst af drevobjektet baseret på den angivne sti |
| GetDriveName | Køre | Genudsætter drevets navn |
| BuildPath | Fil og mappe | Generer en sti fra en eksisterende sti og et navn |
| CopyFile | Fil og mappe | Kopierer en fil |
| GetAbsolutePathName | Fil og mappe | Returner den kanoniske fremstilling af stien |
| GetBaseName | Fil og mappe | Returner basenavnet fra en sti. For eksempel returnerer "D: \ TestFolder \ TestFile.xlsm" TextFile.xlsm |
| GetTempName | Fil og mappe | Generer navn, der kan bruges til at navngive en midlertidig fil |
| CopyFolder | Folder | Kopierer en mappe fra et sted til et andet |
| Opret mappe | Folder | Opretter en ny mappe |
| Slet mappe | Folder | Sletter den angivne mappe |
| FolderExists | Folder | Kontrollerer, om mappen findes eller ej |
| GetFolder | Folder | Returnerer en forekomst af mappeobjektet baseret på den angivne sti |
| GetParentFolderName | Folder | Gengiver navnet på den overordnede mappe baseret på den angivne sti |
| GetSpecialFolder | Folder | Få placeringen af forskellige systemmapper. |
| MoveFolder | Folder | Flytter en mappe fra et sted til et andet |
| Slet fil | Fil | Sletter en fil |
| FileExists | Fil | Kontrollerer, om der findes en fil eller ej |
| GetExtensionName | Fil | Returnerer filtypen |
| GetFile | Fil | Returnerer forekomsten af et filobjekt baseret på den angivne sti |
| GetFileName | Fil | Returnerer filnavnet |
| GetFileVersion | Fil | Returnerer filversionen |
| MoveFile | Fil | Flytter en fil |
| CreateTextFile | Fil | Opretter en tekstfil |
| GetStandardStream | Fil | Hent standard input, output eller fejlstrøm |
| OpenTextFile | Fil | Åbn en fil som en TextStream |
