Når vi bruker VBA i Excel, er det meste for å automatisere oppgavene våre.
Dette betyr også at vi mesteparten av tiden jobber med celler og områder, regneark, arbeidsbøker og andre objekter som er en del av Excel -applikasjonen.
Men VBA er mye kraftigere og kan også brukes til å jobbe med ting utenfor Excel.
I denne opplæringen vil jeg vise deg hvordan du bruker VBA FileSystemObject (FSO) til å arbeide med filer og mapper på systemet eller nettverksstasjonene.
Hva er VBA FileSystemObject (FSO)?
FileSystemObject (FSO) gir deg tilgang til filsystemet på datamaskinen din. Ved å bruke den kan du få tilgang til og endre filene/mappene/katalogene i datasystemet ditt.
Nedenfor er for eksempel noen av tingene du kan gjøre ved å bruke FileSystemObject i Excel VBA:
- Sjekk om det finnes en fil eller en mappe.
- Lag eller gi nytt navn til mapper/filer.
- Få en liste over alle filnavnene (eller undermappene) i en mappe.
- Kopier filer fra en mappe til en annen.
Jeg håper du får ideen.
Jeg vil dekke alle disse eksemplene ovenfor (pluss flere) senere i denne opplæringen.
Selv om noen av tingene nevnt ovenfor også kan gjøres ved hjelp av tradisjonelle VBA -funksjoner (for eksempel DIR -funksjonen) og metoder, ville det føre til lengre og mer kompliserte koder. FileSystemObject gjør det enkelt å jobbe med filer og mapper samtidig som koden holdes ren og kort.
Merk: FSO kan bare brukes i Excel 2000 og nyere versjoner.
Hva kan alle objekter få tilgang til gjennom FileSystemObject?
Som jeg nevnte ovenfor, kan du få tilgang til og endre filer og mapper ved hjelp av FileSystemObject i VBA.
Nedenfor er en tabell som viser de viktigste objektene du kan få tilgang til og endre ved hjelp av FSO:
| Gjenstand | Beskrivelse |
| Kjøre | Stasjonsobjekt lar deg få informasjon om stasjonen, for eksempel om den eksisterer eller ikke, banenavnet, stasjonstypen (flyttbar eller fast), størrelsen osv. |
| Mappe | Mappeobjekt lar deg lage eller endre mapper i systemet. For eksempel kan du opprette, slette, gi nytt navn, kopiere mapper ved hjelp av dette objektet. |
| Fil | File Object lar deg jobbe med filer i systemet ditt. For eksempel kan du opprette, åpne, kopiere, flytte og slette filer ved hjelp av dette objektet. |
| TextStream | TextStream -objektet lar deg lage eller lese tekstfiler. |
Hvert av objektene ovenfor har metoder som du kan bruke for å jobbe med disse.
For å gi deg et eksempel, hvis du vil slette en mappe, vil du bruke DeleteFolder -metoden for mappeobjektet. På samme måte, hvis du vil kopiere en fil, vil du bruke CopyFile -metoden for filobjektet.
Ikke bekymre deg hvis dette virker overveldende eller vanskelig å forstå. Du vil få en mye bedre forståelse når du går gjennom eksemplene jeg har dekket i denne opplæringen.
Bare for referanseformålet har jeg dekket alle FileSystemObject -metodene (for hvert objekt) på slutten av denne opplæringen.
Aktivering av FileSystemObject i Excel VBA
FileSystemObject er ikke tilgjengelig som standard i Excel VBA.
Siden vi har å gjøre med filer og mapper som er utenfor Excel -programmet, må vi først opprette en referanse til biblioteket som inneholder disse objektene (stasjoner, filer, mapper).
Nå er det to måter du kan begynne å bruke FileSystemObject i Excel VBA:
- Angi referansen til Microsoft Scripting Runtime Library (Scrrun.dll)
- Opprette et objekt for å referere til biblioteket fra selve koden
Mens begge disse metodene fungerer (og jeg viser deg hvordan du gjør dette neste), anbefaler jeg å bruke den første metoden.
Merk: Når du aktiverer FileSystemObject, har du tilgang til alle objektene i den. Dette inkluderer FileSystemObject, Drive, Files, Folders, etc. Jeg vil fokusere hovedsakelig på FileSystemObject i denne opplæringen.Angi referansen til Microsoft Scripting Runtime Library
Når du oppretter en referanse til Scripting Runtime Library, gir du Excel VBA tilgang til alle egenskapene og metodene for filer og mapper. Når dette er gjort, kan du referere til filer/mapper/stasjoner -objektet fra Excel VBA (akkurat som du kan referere cellene, regnearkene eller arbeidsbøkene).
Nedenfor er trinnene for å opprette en referanse til Microsoft Scripting Runtime Library:
- Klikk på Verktøy i VB Editor.
- Klikk på Referanser.
- I dialogboksen Referanser som åpnes, blar du gjennom de tilgjengelige referansene og sjekker alternativet ‘Microsoft Scripting Runtime’.
- Klikk OK.
Trinnene ovenfor lar deg nå referere til FSO -objektene fra Excel VBA.
Opprette en forekomst av FileSystemObject i koden
Når du har angitt referansen til Scripting FileSystemObject -biblioteket, må du opprette en forekomst av FSO -objektet i koden din.
Når dette er opprettet, kan du bruke det i VBA.
Nedenfor er koden som vil sette objektvariabelen MyFSO som et FileSystemObject -objekt:
Sub CreatingFSO () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub
I denne koden har jeg først deklarert variabelen MyFSO som et FileSystemObject -typeobjekt. Dette er bare mulig fordi jeg har opprettet en referanse til Microsoft Scripting Runtime Library. Hvis referansen ikke er opprettet, vil dette gi deg en feil (ettersom Excel ikke ville gjenkjenne hva FileSystemObject betyr).
I den andre linjen skjer det to ting:
- Det NYE søkeordet oppretter en forekomst av FileSystemObject. Dette betyr at nå kan jeg bruke alle metodene for FileSystemObject til å jobbe med filer og mapper. Hvis du ikke oppretter denne forekomsten, har du ikke tilgang til FSO -metodene.
- SET -søkeordet setter objektet MyFSO til denne nye forekomsten av FileSystemObject. Dette lar meg bruke dette objektet for å få tilgang til filer og mapper. For eksempel, hvis jeg trenger å opprette en mappe, kan jeg bruke MyFSO.CreateFolder -metoden.
Hvis du vil, kan du også kombinere de to utsagnene ovenfor til en som vist nedenfor:
Sub CreatingFSO () Dim MyFSO As New FileSystemObject End Sub
En stor fordel med å bruke denne metoden (som er å angi referansen til Microsoft Scripting Runtime Library) er at når du bruker FSO -objektene i koden din, vil du kunne bruke IntelliSense -funksjonen som viser metodene og egenskapene knyttet til et objekt (som vist nedenfor).
Dette er ikke mulig når du oppretter referansen fra koden (dekkes neste).
Opprette et objekt fra koden
En annen måte å opprette en referanse til FSO er ved å gjøre det fra koden. I denne metoden trenger du ikke opprette noen referanse (som gjort i den forrige metoden).
Når du skriver koden, kan du opprette et objekt fra koden og referere til Scripting.FileSystemObject.
Koden nedenfor oppretter et objekt FSO og gjør dette til en FileSystemObject -type.
Sub FSODemo () Dim FSO As Object Set FSO = CreateObject ("Scripting.FileSystemObject") End Sub
Selv om dette kan virke mer praktisk, er en stor ulempe ved å bruke denne metoden at den ikke viser IntelliSense når du jobber med objekter i FSO. For meg er dette et stort negativt, og jeg anbefaler alltid å bruke den tidligere metoden for å aktivere FSO (som er ved å angi referansen til 'Microsoft Scripting Runtime')
VBA FileSystemObject -eksempler
La oss nå dykke ned og se på noen praktiske eksempler på bruk av FileSystemObject i Excel.
Eksempel 1: Sjekk om det finnes en fil eller mappe
Følgende kode vil kontrollere om mappen med navnet "Test" eksisterer eller ikke (på det angitte stedet).
Hvis mappen eksisterer, er IF -betingelsen True, og den viser en melding - "Mappen finnes" i en meldingsboks. Og hvis den ikke eksisterer, viser den en melding - mappen eksisterer ikke.
Sub CheckFolderExist () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Da MsgBox "Mappen finnes" Annen MsgBox "Mappen eksisterer ikke" Slutt hvis slutt Under
På samme måte kan du også sjekke om det finnes en fil eller ikke.
Koden nedenfor sjekker om det er en fil med navnet Test.xlsx i den angitte mappen eller ikke.
Sub CheckFileExist () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists ("C: \ Users \ sumit \ Desktop \ Test \ Test.xlsx") Da MsgBox "Filen finnes" Ellers MsgBox "Filen finnes ikke "End If End Sub
Eksempel 2: Opprett en ny mappe på det angitte stedet
Koden nedenfor vil opprette en mappe med navnet 'Test' i C -stasjonen i systemet mitt (du må spesifisere banen på systemet der du vil opprette mappen).
Sub CreateFolder () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test") End Sub
Selv om denne koden fungerer bra, vil det vise en feil i tilfelle mappen allerede eksisterer.
Koden nedenfor sjekker om mappen allerede eksisterer og oppretter en mappe hvis den ikke gjør det. Hvis mappen allerede eksisterer, viser den en melding. For å sjekke om mappen finnes, har jeg brukt FolderExists metode av FSO.
Sub CreateFolder () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Da MsgBox "Mappen finnes allerede" Else MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test ") End If End Sub
Eksempel 3: Få en liste over alle filer i en mappe
Koden nedenfor viser navnene på alle filene i den angitte mappen.
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 Next MyFile End Sub
Denne koden er litt mer kompleks enn de vi allerede har sett.
Som jeg nevnte ovenfor i denne opplæringen, kan du bruke FileSystemObject så vel som alle andre objekter (for eksempel filer og mapper) når du refererer til 'Microsoft Scripting Runtime Library'.
I koden ovenfor bruker jeg tre objekter - FileSystemObject, File og Folder. Dette lar meg gå gjennom hver fil i den angitte mappen. Jeg bruker deretter navnegenskapen til å få listen over alle filnavn.
Vær oppmerksom på at jeg bruker Debug.Print for å få navnene på alle filene. Disse navnene vil bli oppført i det umiddelbare vinduet i VB Editor.
Eksempel 4: Få listen over alle undermapper i en mappe
Koden nedenfor gir navnene på alle undermappene i den angitte mappen. Logikken er nøyaktig den samme som dekket i eksemplet ovenfor. I stedet for filer, i denne koden, har vi brukt 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 Neste MySubFolder End Sub
Eksempel 5: Kopier en fil fra ett sted til et annet
Koden nedenfor vil kopiere filen fra 'Source' -mappen 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 koden ovenfor har jeg brukt to variabler - SourceFile og DestinationFolder.
Kildefil inneholder adressen til filen jeg vil kopiere, og variabelen DestinationFolder holder adressen til mappen jeg vil at filen skal kopieres til.
Vær oppmerksom på at det ikke er tilstrekkelig å oppgi mappenavnet når du kopierer en fil. Du må også angi filnavnet. Du kan bruke det samme filnavnet, eller du kan også endre det. I eksemplet ovenfor kopierte jeg filen og kalte den SampleFileCopy.xlsx
Eksempel 6: Kopier alle filer fra en mappe til en annen
Koden nedenfor vil kopiere alle filene fra kildemappen til destinasjonsmappen.
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
Koden ovenfor vil kopiere alle filene fra kildemappen til destinasjonsmappen.
Vær oppmerksom på at i MyFSO.CopyFile -metoden har jeg spesifisert egenskapen 'Overwritefiles' til å være usann (dette er sant som standard). Dette sikrer at hvis du allerede har filen i mappen, blir den ikke kopiert (og du vil se en feil). Hvis du fjerner ‘Overwritefiles’ eller setter dette til True, i tilfelle det er filer i målmappen med samme navn, vil disse bli overskrevet.
Profftips: Når du kopierer filer, er det alltid en sjanse for å overskrive filer. En god idé, i dette tilfellet, er å legge til tidsstempelet sammen med navnet. Dette vil sikre at navnene alltid er forskjellige, og du kan enkelt spore hvilke filer som ble kopiert på hvilket tidspunkt.Hvis du bare vil kopiere filene til en bestemt utvidelse, kan du gjøre det ved å bruke en IF Then -setning for å kontrollere om utvidelsen er xlsx eller ikke.
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 metodene du kan bruke for hvert objekt. Dette er bare for referanseformål og bekymrer deg ikke for mye. Bruken av noen av disse har blitt vist i eksemplene som er dekket ovenfor.
| FSO -metoder | For Object | Beskrivelse |
| DriveExists | Kjøre | Kontrollerer om stasjonen eksisterer eller ikke |
| GetDrive | Kjøre | Returnerer en forekomst av stasjonsobjektet basert på den angitte banen |
| GetDriveName | Kjøre | Gjenoppretter stasjonsnavnet |
| BuildPath | Fil mappe | Generer en bane fra en eksisterende bane og et navn |
| Kopier fil | Fil mappe | Kopierer en fil |
| GetAbsolutePathName | Fil mappe | Returner den kanoniske representasjonen av banen |
| GetBaseName | Fil mappe | Returner basenavnet fra en bane. For eksempel vil "D: \ TestFolder \ TestFile.xlsm" returnere TextFile.xlsm |
| GetTempName | Fil mappe | Generer navn som kan brukes til å navngi en midlertidig fil |
| CopyFolder | Mappe | Kopierer en mappe fra ett sted til et annet |
| Lag mappe | Mappe | Oppretter en ny mappe |
| Slett mappe | Mappe | Sletter den angitte mappen |
| FolderExists | Mappe | Kontrollerer om mappen eksisterer eller ikke |
| GetFolder | Mappe | Returnerer en forekomst av mappeobjektet basert på den angitte banen |
| GetParentFolderName | Mappe | Gjengir navnet på overordnet mappe basert på den angitte banen |
| GetSpecialFolder | Mappe | Få plassering av forskjellige systemmapper. |
| MoveFolder | Mappe | Flytter en mappe fra ett sted til et annet |
| Slett fil | Fil | Sletter en fil |
| Filen eksisterer | Fil | Kontrollerer om det finnes en fil eller ikke |
| GetExtensionName | Fil | Returnerer filtypen |
| GetFile | Fil | Returnerer forekomsten av et filobjekt basert på den angitte banen |
| GetFileName | Fil | Returnerer filnavnet |
| GetFileVersion | Fil | Returnerer filversjonen |
| MoveFile | Fil | Flytter en fil |
| CreateTextFile | Fil | Oppretter en tekstfil |
| GetStandardStream | Fil | Hent standard input, output eller error stream |
| OpenTextFile | Fil | Åpne en fil som en TextStream |
