Kun käytämme VBA: ta Excelissä, suurin osa siitä on tehtävien automatisointi.
Tämä tarkoittaa myös sitä, että useimmiten työskentelemme solujen ja alueiden, laskentataulukoiden, työkirjojen ja muiden Excel -sovellukseen kuuluvien objektien kanssa.
Mutta VBA on paljon tehokkaampi ja sitä voidaan käyttää myös Excelin ulkopuolisten tavaroiden käsittelyyn.
Tässä opetusohjelmassa näytän sinulle, kuinka voit käyttää VBA FileSystemObjectia (FSO) järjestelmän tai verkkoasemien tiedostojen ja kansioiden kanssa työskentelemiseen.
Mikä on VBA FileSystemObject (FSO)?
FileSystemObject (FSO) antaa sinun käyttää tietokoneen tiedostojärjestelmää. Sen avulla voit käyttää ja muokata tietokoneesi tiedostoja/kansioita/hakemistoja.
Esimerkiksi alla on joitain asioita, joita voit tehdä käyttämällä FileSystemObjectia Excel VBA: ssa:
- Tarkista, onko tiedosto tai kansio olemassa.
- Luo tai nimeä kansioita/tiedostoja.
- Hanki luettelo kaikista kansion tiedostojen nimistä (tai alikansioiden nimistä).
- Kopioi tiedostot kansiosta toiseen.
Toivottavasti saat idean.
Käsittelen kaikkia näitä yllä olevia esimerkkejä (ja enemmän) myöhemmin tässä opetusohjelmassa.
Vaikka jotkut edellä mainituista asioista voidaan tehdä myös käyttämällä perinteisiä VBA -toimintoja (kuten DIR -toiminto) ja menetelmiä, se johtaisi pidempiin ja monimutkaisempiin koodeihin. FileSystemObject helpottaa tiedostojen ja kansioiden käsittelyä pitäen koodin puhtaana ja lyhyenä.
Huomautus: FSO: ta voidaan käyttää vain Excel 2000: ssa ja uudemmissa versioissa.
Mitä kaikkia kohteita voit käyttää FileSystemObjectin kautta?
Kuten edellä mainitsin, voit käyttää ja muokata tiedostoja ja kansioita käyttämällä VBA: n FileSystemObject -tiedostoa.
Alla on taulukko, joka näyttää tärkeimmät objektit, joita voit käyttää ja muokata FSO: n avulla:
| Esine | Kuvaus |
| Ajaa | Drive Objectin avulla voit saada tietoja asemasta, kuten onko se olemassa vai ei, sen polun nimi, aseman tyyppi (irrotettava tai kiinteä), sen koko jne. |
| Kansio | Kansio -objektin avulla voit luoda tai muokata kansioita järjestelmässäsi. Voit esimerkiksi luoda, poistaa, nimetä uudelleen ja kopioida kansioita tämän objektin avulla. |
| Tiedosto | Tiedostoobjektin avulla voit työskennellä järjestelmän tiedostojen kanssa. Voit esimerkiksi luoda, avata, kopioida, siirtää ja poistaa tiedostoja tämän objektin avulla. |
| TextStream | TextStream -objektin avulla voit luoda tai lukea tekstitiedostoja. |
Jokaisella yllä olevista objekteista on menetelmiä, joiden avulla voit työskennellä näiden kanssa.
Esimerkiksi, jos haluat poistaa kansion, käytä kansio -objektin DeleteFolder -menetelmää. Samoin, jos haluat kopioida tiedoston, käytät File -objektin CopyFile -menetelmää.
Älä huoli, jos tämä tuntuu ylivoimaiselta tai vaikealta ymmärtää. Saat paljon paremman käsityksen, kun käyt läpi tässä opetusohjelmassa käsitellyt esimerkit.
Olen vain viitteellisessä tarkoituksessa käsitellyt kaikki FileSystemObject -menetelmät (kullekin objektille) tämän opetusohjelman lopussa.
FileSystemObjectin ottaminen käyttöön Excel VBA: ssa
FileSystemObject ei ole oletusarvoisesti käytettävissä Excel VBA: ssa.
Koska käsittelemme tiedostoja ja kansioita, jotka ovat Excel -sovelluksen ulkopuolella, meidän on ensin luotava viittaus kirjastoon, joka sisältää nämä objektit (asemat, tiedostot, kansiot).
Nyt on kaksi tapaa aloittaa FileSystemObjectin käyttö Excel VBA: ssa:
- Viittauksen asettaminen Microsoft Scripting Runtime Library -kirjastoon (Scrrun.dll)
- Luodaan kirjastoon viittaava objekti itse koodista
Vaikka molemmat menetelmät toimivat (ja näytän sinulle, miten tämä tehdään seuraavaksi), suosittelen ensimmäisen menetelmän käyttöä.
Huomautus: Kun otat FileSystemObject -toiminnon käyttöön, voit käyttää kaikkia sen kohteita. Tämä sisältää FileSystemObject, Drive, Files, Folders jne. Aion keskittyä pääasiassa FileSystemObjectiin tässä opetusohjelmassa.Viittauksen asettaminen Microsoft Scripting Runtime Library -kirjastoon
Kun luot viittauksen komentosarjojen ajonaikaiseen kirjastoon, sallit Excel VBA: n käyttää kaikkia tiedostojen ja kansioiden ominaisuuksia ja menetelmiä. Kun tämä on tehty, voit viitata tiedostoihin/kansioihin/asemiin Excel VBA: sta (aivan kuten voit viitata soluihin, laskentataulukoihin tai työkirjoihin).
Alla on ohjeet Microsoft Scripting Runtime Libraryn viittauksen luomiseen:
- Napsauta VB -editorissa Työkalut.
- Napsauta Viitteet.
- Selaa avautuvassa Viitteet -valintaikkunassa käytettävissä olevia viitteitä ja valitse ”Microsoft Scripting Runtime” -vaihtoehto.
- Napsauta OK.
Yllä olevien vaiheiden avulla voit nyt viitata Excel VBA: n FSO -objekteihin.
FileSystemObject -ilmentymän luominen koodiin
Kun olet asettanut viittauksen Scripting FileSystemObject -kirjastoon, sinun on luotava koodiin FSO -objektin esiintymä.
Kun tämä on luotu, voit käyttää sitä VBA: ssa.
Alla on koodi, joka asettaa objektimuuttujan MyFSO FileSystemObject -objektiksi:
Sub CreatingFSO () Dim MyFSO FileSystemObject Set MyFSO = Uusi FileSystemObject End 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 viitettä ei luoda, tämä antaa sinulle virheen (koska Excel ei tunnista, mitä FileSystemObject tarkoittaa).
Toisella rivillä tapahtuu kaksi asiaa:
- UUSI avainsana luo ilmentymän FileSystemObject. Tämä tarkoittaa, että nyt voin käyttää kaikkia FileSystemObject -menetelmiä työskennelläksesi tiedostojen ja kansioiden kanssa. Jos et luo tätä ilmentymää, et voi käyttää FSO -menetelmiä.
- SET -avainsana asettaa objektin MyFSO tähän uuteen FileSystemObject -esiintymään. Tämän avulla voin käyttää tätä objektia tiedostojen ja kansioiden käyttämiseen. Jos minun on esimerkiksi luotava kansio, voin käyttää MyFSO.CreateFolder -menetelmää.
Halutessasi voit myös yhdistää yllä olevat kaksi lausetta yhdeksi alla esitetyllä tavalla:
Sub CreatingFSO () Dim MyFSO uutena FileSystemObject End Sub
Tämän menetelmän (eli viittauksen asettaminen Microsoft Scripting Runtime Library -kirjastoon) suureksi hyödyksi on se, että kun käytät koodissasi olevia FSO -objekteja, voit käyttää IntelliSense -ominaisuutta, joka näyttää esine (kuten alla).
Tämä ei ole mahdollista, kun luot viittauksen koodista (käsitellään seuraavassa).
Objektin luominen koodista
Toinen tapa luoda viittaus FSO: han on tehdä se koodista. Tässä menetelmässä sinun ei tarvitse luoda mitään viitettä (kuten edellisessä menetelmässä).
Kun kirjoitat koodia, voit luoda objektin koodista ja katso Scripting.FileSystemObject.
Alla oleva koodi luo objektin FSO ja tekee siitä sitten FileSystemObject -tyypin.
Sub FSODemo () Dim FSO objektijoukkona FSO = CreateObject ("Scripting.FileSystemObject") Loppuosa
Vaikka tämä saattaa tuntua helpommalta, tämän menetelmän käytön suuri haittapuoli on se, että se ei näytä IntelliSenseä, kun työskentelet objektien kanssa FSO: ssa. Minulle tämä on valtava negatiivinen asia ja suosittelen aina käyttämään edellistä FSO: n käyttöönottomenetelmää (joka on asettamalla viittaus "Microsoft Scripting Runtime")
VBA FileSystemObject Esimerkkejä
Nyt sukellamme ja katsomme joitain käytännön esimerkkejä FileSystemObjectin käytöstä Excelissä.
Esimerkki 1: Tarkista, onko tiedosto tai kansio olemassa
Seuraava koodi tarkistaa, onko kansio nimeltä "Test" olemassa vai ei (määritetyssä paikassa).
Jos kansio on olemassa, IF -ehto on True ja se näyttää viestin - "Kansio on olemassa" viesti -ruudussa. Ja jos sitä ei ole, se näyttää viestin - Kansio ei ole olemassa ”.
Sub CheckFolderExist () Dim MyFSO nimellä FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Sitten MsgBox "Kansio on olemassa" Muu MsgBox "Kansio ei ole olemassa" Lopeta jos loppu Sub
Samoin voit myös tarkistaa, onko tiedosto olemassa vai ei.
Alla oleva koodi tarkistaa, onko määritetyssä kansiossa tiedostoa nimeltä Test.xlsx vai ei.
Sub CheckFileExist () Dim MyFSO nimellä FileSystemObject Set MyFSO = New FileSystemObject Jos MyFSO.FileExists ("C: \ Users \ sumit \ Desktop \ Test \ Test.xlsx") Sitten MsgBox "Tiedosto on olemassa" Muu MsgBox "Tiedostoa ei ole olemassa "End If End Sub
Esimerkki 2: Luo uusi kansio määritettyyn sijaintiin
Alla oleva koodi luo järjestelmän kansioon, jonka nimi on "Test" (sinun on määritettävä järjestelmäsi polku, johon haluat luoda kansion).
Sub CreateFolder () Dim MyFSO FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test") Loppuosa
Vaikka tämä koodi toimii hyvin, se näyttää virheen, jos kansio on jo olemassa.
Alla oleva koodi tarkistaa, onko kansio jo olemassa, ja luo kansion, jos sitä ei ole. Jos kansio on jo olemassa, se näyttää viestin. Olen tarkistanut kansion olemassaolon käyttämällä FolderExists -menetelmä FSO: sta.
Sub CreateFolder () Dim MyFSO FileSystemObject Set MyFSO = New FileSystemObject Jos MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Sitten MsgBox "Kansio on jo olemassa" Else MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test ") End If End Sub
Esimerkki 3: Hanki luettelo kaikista kansion tiedostoista
Alla oleva koodi näyttää kaikkien määritetyn kansion tiedostojen nimet.
Sub GetFileNames () Dim MyFSO FileSystemObject Dim MyFile tiedostona Dim MyFolder as Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder ("C: \ Users \ sumit \ Desktop \ Test") jokaiselle MyFolder.Files -tiedostolle Virheenkorjaus Tulosta MyFile.Name Seuraava MyFile End Sub
Tämä koodi on hieman monimutkaisempi kuin ne, jotka olemme jo nähneet.
Kuten edellä tässä opetusohjelmassa mainitsin, 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. Tämän avulla voin käydä läpi kaikki tiedostot määritetyssä kansiossa. Käytän sitten name -ominaisuutta saadaksesi luettelon kaikista tiedostonimistä.
Huomaa, että käytän Debug.Print -tiedostoa kaikkien tiedostojen nimien hakemiseen. Nämä nimet näkyvät VB Editorin välittömässä ikkunassa.
Esimerkki 4: Hanki luettelo kansion kaikista alikansioista
Alla oleva koodi antaa määritetyn kansion kaikkien alikansioiden nimet. Logiikka on täsmälleen sama kuin yllä olevassa esimerkissä. Tiedostojen sijaan tässä koodissa olemme käyttäneet alikansioita.
Sub GetSubFolderNames () Dim MyFSO FileSystemObject Dim MyFile Tiedostona Dim MyFolder kuin kansio Dim MySubFolder as Folder Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder ("C: \ Users \ sumit \ Desktop \ Test") In MyFolder.SubFolders Debug.Print MySubFolder.Name Seuraava MySubFolder End Sub
Esimerkki 5: Kopioi tiedosto paikasta toiseen
Alla oleva koodi kopioi tiedoston "Lähde" -kansiosta ja kopioi sen "Kohde" -kansioon.
Sub CopyFile () Dim MyFSO FileSystemObject Dim SourceFile merkkijonona Dim DestinationFolder kuin merkkijono Aseta MyFSO = New Scripting.FileSystemObject SourceFile = "C: \ Users \ sumit \ Desktop \ Source \ SampleFile.xlsx" DestinationFolder = "C: \ Users \ sumit \ Desktop \ Destination "MyFSO.CopyFile Lähde: = SourceFile, Destination: = DestinationFolder &" \ SampleFileCopy.xlsx "Loppuosa
Yllä olevassa koodissa olen käyttänyt kahta muuttujaa - SourceFile ja DestinationFolder.
Lähdetiedosto sisältää kopioitavan tiedoston osoitteen ja DestinationFolder -muuttuja pitää sen kansion osoitteen, johon haluan kopioida tiedoston.
Huomaa, että kohdekansion nimen antaminen ei riitä, kun kopioit tiedostoa. Sinun on myös määritettävä tiedoston nimi. Voit käyttää samaa tiedostonimeä tai myös muuttaa sitä. Yllä olevassa esimerkissä kopioin tiedoston ja annoin sille nimen SampleFileCopy.xlsx
Esimerkki 6: Kopioi kaikki tiedostot kansiosta toiseen
Alla oleva koodi kopioi kaikki lähdekansion tiedostot kohdekansioon.
Sub CopyAllFiles () Dim MyFSO FileSystemObject Dim MyFile Tiedosto Dim SourceFolder merkkijonona Dim DestinationFolder As String Dim MyFolder kuin kansio Dim MySubFolder as Folder SourceFolder = "C: \ Users \ sumit \ Desktop \ Source" DestinationFolder = "C: \ Users \ sumit \ Desktop \ Destination "Aseta MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder (SourceFolder) Jokaiselle MyFile In MyFolder.Files MyFSO.CopyFile Lähde: = MyFSO.GetFile (MyFile), _ Destination: =" Destination: " "& MyFile.Name, Overwritefiles: = False Seuraava MyFile End Sub
Yllä oleva koodi kopioi kaikki lähdekansion tiedostot kohdekansioon.
Huomaa, että MyFSO.CopyFile -menetelmässä olen määrittänyt Overwritefiles -ominaisuuden arvoksi False (tämä on oletusarvoisesti True). Tämä varmistaa, että jos tiedosto on jo kansiossa, sitä ei kopioida (ja näet virheen). Jos poistat Overwritefiles -tiedoston tai asetat sen arvoksi True, jos kohdekansiossa on tiedostoja, joilla on sama nimi, ne korvataan.
Pro -vinkki: Kun kopioit tiedostoja, on aina mahdollisuus korvata tiedostot. Hyvä idea tässä tapauksessa on lisätä aikaleima nimen kanssa. Tämä varmistaa, että nimet ovat aina erilaisia ja voit helposti seurata, mitkä tiedostot kopioitiin milloin.Jos haluat kopioida vain tietyn laajennuksen tiedostot, voit tehdä sen käyttämällä IF Then -lauseketta tarkistaaksesi, onko laajennus xlsx vai ei.
Sub CopyExcelFilesOnly () Dim MyFSO kuten FileSystemObject Dim MyFile Tiedoston himmennettynä SourceFolder kuin String Dim DestinationFolder String Dim MyFolder Kansiona Dim MySubFolder As Folder SourceFolder = "C: \ Users \ sumit \ Desktop \ Source" DestinationFolder = "C: \ Us: sumit \ Desktop \ Destination "Aseta MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder (SourceFolder) Jokaiselle MyFile In MyFolder.Files Jos MyFSO.GetExtensionName (MyFile) =" xlsx "Sitten MyFSO.CopyFileGet: (MyFile), _ Destination: = DestinationFolder & "\" & MyFile.Name, Overwritefiles: = False End if Next MyFile End Sub
FileSystemObject (FSO) -menetelmät
Tässä ovat menetelmät, joita voit käyttää kullekin objektille. Tämä on vain viitteellistä tarkoitusta varten, eikä siitä tarvitse liikaa huolehtia. Joidenkin näiden käyttö on esitetty yllä olevissa esimerkeissä.
| FSO -menetelmät | Objektille | Kuvaus |
| DriveExists | Ajaa | Tarkista, onko asema olemassa vai ei |
| GetDrive | Ajaa | Palauttaa asemaobjektin ilmentymän määritetyn polun perusteella |
| GetDriveName | Ajaa | Käynnistää aseman nimen uudelleen |
| BuildPath | Tiedostokansio | Luo polku olemassa olevasta polusta ja nimestä |
| Kopioi tiedosto | Tiedostokansio | Kopioi tiedoston |
| GetAbsolutePathName | Tiedostokansio | Palauta polun kanoninen esitys |
| GetBaseName | Tiedostokansio | Palauta tukiaseman nimi polulta. Esimerkiksi "D: \ TestFolder \ TestFile.xlsm" palauttaa TextFile.xlsm |
| GetTempName | Tiedostokansio | Luo nimi, jota voidaan käyttää väliaikaisen tiedoston nimeämiseen |
| CopyFolder | Kansio | Kopioi kansion paikasta toiseen |
| Luo kansio | Kansio | Luo uuden kansion |
| Poista kansio | Kansio | Poistaa määritetyn kansion |
| KansioOlemassa | Kansio | Tarkista, onko kansio olemassa vai ei |
| GetFolder | Kansio | Palauttaa kansio -objektin ilmentymän määritetyn polun perusteella |
| GetParentFolderName | Kansio | Toistaa pääkansion nimen uudelleen määritetyn polun perusteella |
| GetSpecialFolder | Kansio | Hanki eri järjestelmäkansioiden sijainti. |
| MoveFolder | Kansio | Siirtää kansion paikasta toiseen |
| Poista tiedosto | Tiedosto | Poistaa tiedoston |
| FileExists | Tiedosto | Tarkistaa, onko tiedosto olemassa vai ei |
| GetExtensionName | Tiedosto | Palauttaa tiedostopääte |
| GetFile | Tiedosto | Palauttaa tiedostoobjektin ilmentymän määritetyn polun perusteella |
| GetFileName | Tiedosto | Palauttaa tiedoston nimen |
| GetFileVersion | Tiedosto | Palauttaa tiedoston version |
| MoveFile | Tiedosto | Siirtää tiedoston |
| CreateTextFile | Tiedosto | Luo tekstitiedoston |
| GetStandardStream | Tiedosto | Hae vakiotulo, -lähtö tai virhevirta |
| OpenTextFile | Tiedosto | Avaa tiedosto TextStreamina |
