Brug af VBA FileSystemObject (FSO) i Excel – nem oversigt og eksempler

Når vi bruger VBA i Excel, er det for det meste for at automatisere vores opgaver.

Det betyder også, at vi for det meste arbejder med celler og intervaller, regneark, arbejdsbøger og andre objekter, der er en del af Excel-programmet.

Men VBA er meget mere kraftfuldt 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 mulighed for at få adgang til din computers filsystem. Ved hjælp af det kan du få adgang til og ændre filerne/mapperne/katalogerne i dit computersystem.

Nedenfor er der f.eks. nogle af de ting, du kan gøre ved hjælp af FileSystemObject i Excel VBA:

• Kontroller, om en fil eller en mappe findes.
• Opret eller omdøb mapper/filer.
• Få en liste over alle filnavne (eller undermappernavne) i en mappe.
• Kopier filer fra en mappe til en anden.

Jeg håber, du forstår det hele.

Jeg vil dække alle disse ovenstående eksempler (plus flere) senere i denne vejledning.

Selv om nogle af de ovennævnte ting også kan gøres ved hjælp af traditionelle VBA-funktioner (f.eks. funktionen DIR) og metoder, ville det føre til længere og mere komplicerede koder. FileSystemObject gør det nemt at arbejde med filer og mapper, samtidig med at koden holdes ren og kort.

Note: FSO can only be used in Excel 2000 and later versions.

Hvilke alle objekter kan du 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:

Hvert 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, skal du bruge DeleteFolder-metoden for Folder-objektet. Tilsvarende, hvis du vil kopiere en fil, skal du bruge CopyFile-metoden for File-objektet.

Du skal ikke være bekymret, hvis dette virker overvældende eller svært at forstå. Du vil få en meget bedre forståelse, når du gennemgår de eksempler, som jeg har behandlet i denne tutorial.

Kun til referenceformål har jeg behandlet alle FileSystemObject-metoderne (for hvert objekt) i slutningen af denne tutorial.

Aktivering af FileSystemObject i Excel VBA

FileSystemObject er ikke tilgængelig som standard i Excel VBA.

Da vi har med filer og mapper at gøre, der ligger uden for Excel-programmet, skal vi først oprette en henvisning til det bibliotek, der indeholder disse objekter (drev, filer, mapper).

Nu er der to måder, hvorpå du kan begynde at bruge FileSystemObject i Excel VBA:

1. Sæt henvisningen til Microsoft Scripting Runtime Library (Scrrun.dll)
2. Opret et objekt til at henvise til biblioteket fra selve koden

Selv om begge disse metoder fungerer (og jeg viser dig, hvordan du gør det næste gang), anbefaler jeg at bruge den første metode.

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 for filer og mapper. Når dette er gjort, kan du henvise til filerne/mapperne/drev-objektet fra Excel VBA (ligesom du kan henvise til celler, regneark eller arbejdsmapper).

Nedenfor er trinene til at oprette en reference til Microsoft Scripting Runtime Library:

1. I VB-editoren skal du klikke på Værktøjer.
2. Klik på Referencer.
3. I den dialogboks Referencer, der åbnes, skal du rulle gennem de tilgængelige referencer og markere indstillingen ‘Microsoft Scripting Runtime’.
4. Klik på OK.

Med ovenstående trin kan du nu henvise til FSO-objekterne fra Excel VBA.

Skabelse af en instans af FileSystemObject i koden

Når du har indstillet referencen til biblioteket Scripting FileSystemObject, skal du oprette en instans af FSO-objektet i din kode.

Når dette er oprettet, kan du bruge det i VBA.

Nedenfor er den kode, der indstiller objektvariablen MyFSO som et FileSystemObject-objekt:

Sub CreatingFSO()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectEnd Sub

I denne kode har jeg først erklæret variablen MyFSO som et objekt af typen FileSystemObject. Dette er kun muligt, fordi jeg har oprettet en henvisning til Microsoft Scripting Runtime Library. Hvis referencen ikke er oprettet, vil dette give en fejl (da Excel ikke ville genkende, hvad FileSystemObject betyder).

I den anden linje sker der to ting:

1. Nøgleordet NEW opretter en instans 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 instans, vil du ikke kunne få adgang til metoderne i FSO.
2. Nøgleordet SET indstiller objektet MyFSO til denne nye instans af FileSystemObject. Dette giver mig mulighed for at bruge dette objekt til at få adgang til filer og mapper. Hvis jeg f.eks. skal oprette en mappe, kan jeg bruge MyFSO.CreateFolder-metoden.

Hvis du ønsker det, kan du også kombinere de to ovenstående udsagn til ét som vist nedenfor:

Sub CreatingFSO()Dim MyFSO As New FileSystemObjectEnd Sub

En stor fordel ved at bruge denne metode (som er at indstille referencen til Microsoft Scripting Runtime Library) er, at når du bruger FSO-objekter i din kode, vil du kunne bruge IntelliSense-funktionen, der viser de metoder og egenskaber, der er knyttet til et objekt (som vist nedenfor).

Dette er ikke muligt, når du opretter referencen inde fra koden (behandles næste gang).

Oprettelse af et objekt fra koden

En anden måde at oprette en reference til FSO på er ved at gøre det fra koden. I denne metode behøver du ikke at oprette en reference (som i den foregående metode).

Når du skriver koden, kan du oprette et objekt inde fra koden og henvise til Scripting.FileSystemObject.

Den nedenstående kode opretter et objekt FSO og gør det derefter til en FileSystemObject-type.

Sub FSODemo()Dim FSO As ObjectSet FSO = CreateObject("Scripting.FileSystemObject")End Sub

Mens dette kan virke mere praktisk, er en stor ulempe ved at bruge denne metode, at der ikke vil blive vist en IntelliSense, når du arbejder med objekter i FSO. For mig er dette et stort minus, og jeg anbefaler altid at bruge den tidligere metode til at aktivere FSO (som er ved at indstille referencen til ‘Microsoft Scripting Runtime’)

VBA FileSystemObject Eksempler

Nu skal vi dykke ned og se på nogle praktiske eksempler på at bruge FileSystemObject i Excel.

Eksempel 1: Kontroller, om en fil eller mappe findes

Den følgende kode kontrollerer, om mappen med navnet ‘Test’ findes eller ej (på den angivne placering).

Hvis mappen findes, er IF-betingelsen sand, og der vises en meddelelse – ‘Mappen findes’ i en meddelelsesboks. Og hvis den ikke findes, vises en meddelelse – ‘Mappen findes ikke’.

Sub CheckFolderExist()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectIf MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "The Folder Exists"Else MsgBox "The Folder Does Not Exist"End IfEnd Sub

Sådan kan du også kontrollere, om en fil findes eller ej.

Den nedenstående kode kontrollerer, om der findes en fil med navnet Test.xlsx i den angivne mappe eller ej.

Sub CheckFileExist()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectIf MyFSO.FileExists("C:\Users\sumit\Desktop\Test\Test.xlsx") Then MsgBox "The File Exists"Else MsgBox "The File Does Not Exist"End IfEnd Sub

Eksempel 2: Opret en ny mappe på det angivne sted

Med nedenstående kode oprettes der en mappe med navnet “Test” i C-drevet på mit system (du skal angive stien på dit system, hvor du vil oprette mappen).

Sub CreateFolder()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectMyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test")End Sub

Mens denne kode fungerer fint, ville den vise en fejl, hvis mappen allerede eksisterer.

Den nedenstående kode kontrollerer, om mappen allerede eksisterer, 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 FSO’s FolderExists-metode.

Sub CreateFolder()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectIf MyFSO.FolderExists("C:\Users\sumit\Desktop\Test") Then MsgBox "The Folder Already Exist"Else MyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test")End IfEnd Sub

Eksempel 3: Hent en liste over alle filer i en mappe

Nedenstående kode vil vise navnene på alle filer i den angivne mappe.

Sub GetFileNames()Dim MyFSO As FileSystemObjectDim MyFile As FileDim MyFolder As FolderSet MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop\Test")For Each MyFile In MyFolder.Files Debug.Print MyFile.NameNext MyFileEnd Sub

Denne kode er lidt mere kompleks end de koder, vi allerede har set.

Som jeg nævnte ovenfor i denne tutorial, kan du, når du refererer til ‘Microsoft Scripting Runtime Library’, bruge FileSystemObject samt 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 gennemgå hver enkelt fil i den angivne mappe. Jeg bruger derefter name-egenskaben til at få en liste over alle filnavne.

Bemærk, at jeg bruger Debug.Print til at få navnene på alle filerne. Disse navne vil blive vist i det umiddelbare vindue i VB-editoren.

Eksempel 4: Få listen over alle undermapper i en mappe

Med nedenstående kode får du navnene på alle undermapper i den angivne mappe. Logikken er nøjagtig den samme som i ovenstående eksempel. I stedet for filer har vi i denne kode brugt undermapper.

Sub GetSubFolderNames()Dim MyFSO As FileSystemObjectDim MyFile As FileDim MyFolder As FolderDim MySubFolder As FolderSet MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder("C:\Users\sumit\Desktop\Test")For Each MySubFolder In MyFolder.SubFolders Debug.Print MySubFolder.NameNext MySubFolderEnd Sub

Eksempel 5: Kopiering af en fil fra et sted til et andet

Med nedenstående kode kopieres filen fra mappen “Source” og kopieres til mappen “Destination”.

Sub CopyFile()Dim MyFSO As FileSystemObjectDim SourceFile As StringDim DestinationFolder As StringSet MyFSO = New Scripting.FileSystemObjectSourceFile = "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.

Source File indeholder adressen på den fil, jeg vil kopiere, og variablen DestinationFolder indeholder adressen til den mappe, jeg vil have filen kopieret til.

Bemærk, at det ikke er tilstrækkeligt at angive navnet på destinationsmappen, 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 navngav den SampleFileCopy.xlsx

Eksempel 6: Kopier alle filer fra en mappe til en anden

Med nedenstående kode kopieres alle filer fra mappen Kilde til destinationsmappen.

Sub CopyAllFiles()Dim MyFSO As FileSystemObjectDim MyFile As FileDim SourceFolder As StringDim DestinationFolder As StringDim MyFolder As FolderDim MySubFolder As FolderSourceFolder = "C:\Users\sumit\Desktop\Source"DestinationFolder = "C:\Users\sumit\Desktop\Destination"Set MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder(SourceFolder)For Each MyFile In MyFolder.Files MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=FalseNext MyFileEnd Sub

Overstående kode kopierer alle filerne fra mappen Kilde til destinationsmappen.

Bemærk, at jeg i metoden MyFSO.CopyFile har angivet egenskaben ‘Overwritefiles’ til at være False (denne er True som standard). Dette sikrer, at hvis du allerede har filen i mappen, bliver den ikke kopieret (og du vil se en fejl). Hvis du fjerner ‘Overwritefiles’ eller indstiller den til True, vil disse blive overskrevet, hvis der er filer i destinationsmappen med samme navn.

Hvis du kun vil kopiere filer med 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 FileSystemObjectDim MyFile As FileDim SourceFolder As StringDim DestinationFolder As StringDim MyFolder As FolderDim MySubFolder As FolderSourceFolder = "C:\Users\sumit\Desktop\Source"DestinationFolder = "C:\Users\sumit\Desktop\Destination"Set MyFSO = New Scripting.FileSystemObjectSet MyFolder = MyFSO.GetFolder(SourceFolder)For Each MyFile In MyFolder.Files If MyFSO.GetExtensionName(MyFile) = "xlsx" Then MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _ Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=False End IfNext MyFileEnd Sub

FileSystemObject (FSO) Metoder

Her er de metoder, du kan bruge for hvert objekt. Dette er blot til referenceformål og bekymrer ikke for meget om det. Brugen af nogle af disse er blevet vist i de eksempler, der er dækket ovenfor.

Du kan også lide følgende Excel-vejledninger:

• Få en liste over filnavne fra mapper & Undermapper (ved hjælp af Power Query).
• Få en liste over filnavne fra en mappe i Excel (med og uden VBA).
• Forståelse af Excel VBA-datatyper (variabler og konstanter).
• Skabelse af en brugerdefineret funktion (UDF) i Excel VBA.