VBA FileSystemObjectin (FSO) käyttäminen Excelissä – Helppo yleiskatsaus ja esimerkkejä

Kun käytämme VBA:ta Excelissä, suurimmaksi osaksi automatisoimme työtehtäviämme.

Tämähän tarkoittaa myös sitä, että työskentelemme suurimman osan ajasta soluilla ja riveillä, laskentataulukoilla, työkirjoilla ja muilla objekteilla, jotka ovat osa excel-ohjelmaa.

Mutta VBA on paljon tehokkaampi, ja sitä voidaan käyttää myös Excelin ulkopuolisten asioiden kanssa työskentelyyn.

Tässä opetusohjelmassa näytän, miten voit käyttää VBA FileSystemObject (FSO) -ohjelmaa työskennellessäsi järjestelmässäsi tai verkkolevyilläsi olevien tiedostojen ja kansioiden kanssa.

Mikä on VBA FileSystemObject (FSO)?

FileSystemObjectin (FSO) avulla voit käyttää tietokoneen tiedostojärjestelmää. Sen avulla voit käyttää ja muokata tietokonejärjestelmän tiedostoja/kansioita/hakemistoja.

Alla on esimerkiksi joitakin asioita, joita voit tehdä käyttämällä FileSystemObjectia Excel VBA:ssa:

• Tarkista, onko tiedosto tai kansio olemassa.
• Luo kansioita/tiedostoja tai nimeä niitä uudelleen.
• Luettelo kaikista kansion tiedostojen nimistä (tai alikansioiden nimistä).
• Kopioi tiedostoja kansiosta toiseen.

Toivottavasti ymmärrät idean.

Käsittelen kaikkia näitä edellä mainittuja esimerkkejä (ja lisää) myöhemmin tässä opetusohjelmassa.

Joskin edellä mainituista asioista voidaan tehdä myös perinteisillä VBA-funktioilla (kuten DIR-funktiolla) ja -menetelmillä, se johtaisi pidempään ja monimutkaisempaan koodiin. FileSystemObject helpottaa tiedostojen ja kansioiden kanssa työskentelyä pitäen koodin siistinä ja lyhyenä.

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

Mitä kaikkia objekteja voit käyttää FileSystemObjectin kautta?

Kuten edellä mainitsin, voit käyttää ja muokata tiedostoja ja kansioita FileSystemObjectin avulla VBA:ssa.

Alhaalla on taulukko, josta näet tärkeimmät objektit, joita voit käyttää ja muokata FSO:n avulla:

Kullakin edellä mainituista objekteista on metodeja, joita voit käyttää niiden kanssa työskentelyyn.

Esimerkiksi, jos haluat poistaa kansion, käytät Folder-objektin DeleteFolder-metodia. Vastaavasti, jos haluat kopioida tiedoston, käytät File-olion CopyFile-metodia.

Ei hätää, jos tämä tuntuu ylivoimaiselta tai vaikeasti ymmärrettävältä. Saat paljon paremman ymmärryksen, kun käyt läpi esimerkit, joita olen käsitellyt tässä opetusohjelmassa.

Viitteeksi olen käsitellyt kaikki FileSystemObjectin metodit (jokaisen objektin osalta) tämän opetusohjelman lopussa.

FileSystemObjectin ottaminen käyttöön Excel VBA:ssa

FileSystemObject ei ole oletusarvoisesti käytettävissä Excel VBA:ssa.

Koska olemme tekemisissä Excel-sovelluksen ulkopuolisten tiedostojen ja kansioiden kanssa, meidän on ensin luotava viittaus kirjastoon, joka pitää sisällään näitä objekteja (asemia, tiedostoja, kansioita).

Nyt on kaksi tapaa aloittaa FileSystemObjectin käyttö Excel VBA:ssa:

1. Viittauksen asettaminen Microsoft Scripting Runtime Library -kirjastoon (Scrrun.dll)
2. Objektin luominen viittaamaan kirjastoon itse koodista

Vaikka nämä molemmat menetelmät toimivat (ja näytän seuraavaksi, miten tämä tehdään), suosittelen käyttämään ensimmäistä menetelmää.

Viittauksen asettaminen Microsoft Scripting Runtime Library -kirjastoon

Kun luot viittauksen Scripting Runtime Library -kirjastoon, sallit Excel VBA:lle pääsyn kaikkiin tiedostojen ja kansioiden ominaisuuksiin ja metodeihin. Kun tämä on tehty, voit viitata tiedostojen/kansioiden/asemien objektiin Excel VBA:ssa (aivan kuten voit viitata soluihin, työarkkeihin tai työkirjoihin).

Alhaalla luetellaan vaiheet, joiden avulla voit luoda viittauksen Microsoft Scripting Runtime Library -kirjastoon:

1. Klikkaa VB-editorissa Työkalut.
2. Klikkaa Viitteet.
3. Klikkaa avautuvassa Viitteet-valintaikkunassa selaa käytettävissä olevia viitteitä ja valitse vaihtoehto ’Microsoft Scripting Runtime’.
4. Klikkaa OK.

Yllä olevien vaiheiden avulla voisit nyt viitata FSO-objekteihin Excel VBA:sta.

FileSystemObjectin instanssin luominen koodissa

Kun olet asettanut viittauksen Scripting FileSystemObject -kirjastoon, sinun on luotava FSO-objektin instanssi koodissasi.

Kun tämä on luotu, voit käyttää sitä VBA:ssa.

Alhaalla on koodi, joka asettaa objektimuuttujan MyFSO FileSystemObject-olioksi:

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

Tässä koodissa olen ensin ilmoittanut muuttujan MyFSO FileSystemObject-tyyppiseksi objektiksi. Tämä on mahdollista vain siksi, että olen luonut viittauksen Microsoft Scripting Runtime Library -kirjastoon. Jos viittausta ei ole luotu, tämä antaa virheilmoituksen (koska Excel ei tunnistaisi, mitä FileSystemObject tarkoittaa).

Kakkosrivillä tapahtuu kaksi asiaa:

1. Uusi avainsana luo FileSystemObjectin instanssin. Tämä tarkoittaa, että nyt voin käyttää kaikkia FileSystemObjectin metodeja työskennellessäni tiedostojen ja kansioiden kanssa. Jos et luo tätä instanssia, et voi käyttää FSO:n metodeja.
2. SET-avainsana asettaa objektin MyFSO tähän uuteen FileSystemObjectin instanssiin. Näin voin käyttää tätä objektia tiedostojen ja kansioiden käyttämiseen. Jos minun on esimerkiksi luotava kansio, voin käyttää MyFSO.CreateFolder-metodia.

Jos haluat, voit myös yhdistää kaksi edellä mainittua lauseketta yhdeksi, kuten alla on esitetty:

Sub CreatingFSO()Dim MyFSO As New FileSystemObjectEnd Sub

Tämän metodin käyttämisen (eli viittauksen asettamisen Microsoftin komentosarjakirjastoon (Microsoft Scripting Runtime Library) asettamisen) suuri hyöty on se, että käyttäessäsi FSO-objekteja ohjelmakoodissasi pystyt käyttämään IntelliSense-toimintoa, joka näyttää objektiin liitetyt metodit ja ominaisuudet, jotka näkyvät objektin yhteydessä (kuten on näytetty alla).

Tämä ei ole mahdollista, kun viittaus luodaan koodin sisältä (käsitellään seuraavaksi).

Objektien luominen koodista

Toinen tapa luoda viittaus FSO-objektiin on tehdä se koodista. Tässä menetelmässä sinun ei tarvitse luoda mitään viittausta (kuten edellisessä menetelmässä tehtiin).

Koodia kirjoittaessasi voit luoda objektin koodin sisältä ja viitata Scripting.FileSystemObject.

Alhaalla oleva koodi luo objektin FSO ja tekee tästä sitten FileSystemObject-tyypin.

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

Vaikka tämä saattaa tuntua kätevämmältä, tämän menetelmän käyttämisen suuri haittapuoli on se, että se ei näyttäisi IntelliSenseä työskenneltäessäsi FSO:n objektien kanssa. Minulle tämä on valtava miinus, ja suosittelen aina käyttämään edellistä menetelmää FSO:n ottamiseksi käyttöön (eli asettamalla viittaus ”Microsoft Scripting Runtime”:iin)

VBA FileSystemObject Esimerkkejä

Sukelletaan nyt sisään ja katsotaan muutamia käytännön esimerkkejä FileSystemObjectin käytöstä Excelissä.

Esimerkki 1: Tarkista, onko tiedosto tai kansio olemassa

Seuraavalla koodilla tarkistetaan, onko kansio nimeltä ’Testi’ olemassa vai ei (määritetyssä sijainnissa).

Jos kansio on olemassa, IF-ehto on True ja se näyttää viestiruudussa viestin – ’Kansio on olemassa’. Ja jos sitä ei ole olemassa, se näyttää viestin – Kansiota ei ole olemassa’.

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

Samoin voit myös tarkistaa, onko tiedosto olemassa vai ei.

Alhaalla oleva koodi tarkistaa, onko määritetyssä kansiossa tiedosto nimeltä Test.xlsx vai ei.

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

Esimerkki 2: Uuden kansion luominen määritettyyn paikkaan

Alhaalla oleva koodi loisi kansion nimellä ’Test’ järjestelmäni C-asemaan (sinun on määritettävä järjestelmässäsi polku, johon haluat luoda kansion).

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

Vaikka tämä koodi toimii hyvin, se näyttäisi virheilmoituksen, jos kansio on jo olemassa.

Alhaalla oleva koodi tarkistaa, onko kansio jo olemassa, ja luo kansion, jos ei ole. Jos kansio on jo olemassa, se näyttää viestin. Tarkistaakseni, onko kansio olemassa, olen käyttänyt FSO:n FolderExists-metodia.

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

Esimerkki 3: Luettelon hakeminen kaikista kansiossa olevista tiedostoista

Alhaalla oleva koodi näyttäisi kaikkien määritetyssä kansiossa olevien tiedostojen nimet.

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

Tämä koodi on hiukan monimutkaisempi kuin aiemmin näkemämme.

Kuten mainitsin edellä tässä opetusohjelmassa, kun viittaat ’Microsoft Scripting Runtime Library’-kirjastoon, voit käyttää FileSystemObjectia sekä kaikkia muita objekteja (kuten tiedostoja ja kansioita).

Yllä olevassa koodissa käytän kolmea objektia – FileSystemObject, File ja Folder. Näin voin käydä läpi jokaisen tiedoston määritetyssä kansiossa. Sen jälkeen käytän name-ominaisuutta saadakseni luettelon kaikkien tiedostojen nimistä.

Huomaa, että käytän Debug.Print-ominaisuutta saadakseni kaikkien tiedostojen nimet. Nämä nimet luetellaan VB-editorin välittömässä ikkunassa.

Esimerkki 4: Luettelon saaminen kaikista kansion alikansioista

Alla oleva koodi antaa määritetyn kansion kaikkien alikansioiden nimet. Logiikka on täsmälleen sama kuin yllä olevassa esimerkissä käsitelty. Tiedostojen sijaan tässä koodissa on käytetty alikansioita.

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

Esimerkki 5: Tiedoston kopioiminen paikasta toiseen

Alhaalla oleva koodi kopioi tiedoston ’Source’-kansiosta ja kopioi sen ’Destination’-kansioon.

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

Yllä olevassa koodissa olen käyttänyt kahta muuttujaa – SourceFile ja DestinationFolder.

Source File pitää sisällään sen tiedoston osoitteen, jonka haluan kopioida, ja DestinationFolder-muuttuja pitää sisällään sen kansion osoitteen, johon tiedosto halutaan kopioida.

Huomaa, että ei riitä, että annat kohdekansion nimen, kun kopioit tiedostoa. Sinun on määritettävä myös tiedoston nimi. Voit käyttää samaa tiedostonimeä tai voit myös muuttaa sitä. Yllä olevassa esimerkissä kopioin tiedoston ja nimesin sen SampleFileCopy.xlsx

Esimerkki 6: Kaikkien tiedostojen kopioiminen yhdestä kansiosta toiseen

Alla oleva koodi kopioi kaikki tiedostot lähdekansiosta kohdekansioon.

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

Yllä oleva koodi kopioi kaikki tiedostot lähdekansiosta kohdekansioon.

Huomaa, että MyFSO.CopyFile-metodissa olen määrittänyt ’Overwritefiles’-ominaisuuden arvoksi False (oletusarvoisesti tämä on True). Näin varmistetaan, että jos tiedosto on jo kansiossa, sitä ei kopioida (ja saat virheilmoituksen). Jos poistat ’Overwritefiles’-ominaisuuden tai asetat sen arvoksi True, jos kohdekansiossa on samannimisiä tiedostoja, ne ylikirjoitetaan.

Jos haluat kopioida vain tietyn laajennuksen tiedostot, voit tehdä sen käyttämällä IF Then -lauseketta, jolla tarkistat, onko laajennus xlsx vai ei.

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) -metodit

Tässä luetellaan metodit, joita voit käyttää kullekin objektille. Tämä on vain viitetarkoituksessa, eikä siitä kannata huolehtia liikaa. Joidenkin näistä käyttö on esitetty edellä käsitellyissä esimerkeissä.

.

.

. objekti määritetyn polun perusteella

Olet ehkä mieltynyt myös seuraaviin Excel-oppaisiin:

• Hae luettelo tiedostojen nimistä kansioista & Alikansiot (Power Queryn avulla).
• Tiedostonimien luettelon hakeminen kansioista Excelissä (VBA:lla ja ilman VBA:ta).
• Excel VBA:n tietotyyppien ymmärtäminen (muuttujat ja vakiot).
• Käyttäjän määrittelemän funktion (UDF) luominen Excel VBA:ssa.