Ko uporabljamo VBA v Excelu, je večina za avtomatizacijo naših nalog.
To tudi pomeni, da večino časa delamo s celicami in obsegi, delovnimi listi, delovnimi zvezki in drugimi predmeti, ki so del aplikacije Excel.
Toda VBA je veliko močnejša in se lahko uporablja tudi za delo z zunaj Excela.
V tej vadnici vam bom pokazal, kako uporabljati VBA FileSystemObject (FSO) za delo z datotekami in mapami na vašem sistemu ali omrežnih pogonih.
Kaj je VBA FileSystemObject (FSO)?
FileSystemObject (FSO) omogoča dostop do datotečnega sistema vašega računalnika. Z njim lahko dostopate do datotek/map/imenikov v računalniškem sistemu in jih spreminjate.
Spodaj je na primer nekaj stvari, ki jih lahko naredite z uporabo FileSystemObject v Excelu VBA:
- Preverite, ali obstaja datoteka ali mapa.
- Ustvarite ali preimenujte mape/datoteke.
- Pridobite seznam vseh imen datotek (ali imen podmap) v mapi.
- Kopirajte datoteke iz ene mape v drugo.
Upam, da razumete idejo.
Vse te zgornje primere (in še več) bom obravnaval kasneje v tej vadnici.
Čeprav je nekatere zgoraj omenjene stvari mogoče narediti tudi s tradicionalnimi funkcijami VBA (na primer funkcijo DIR) in metodami, bi to vodilo v daljše in bolj zapletene kode. FileSystemObject olajša delo z datotekami in mapami, hkrati pa ohrani kodo čisto in kratko.
Opomba: FSO je mogoče uporabljati samo v Excelu 2000 in novejših različicah.
Do katerih objektov lahko dostopate prek FileSystemObject?
Kot sem že omenil, lahko do datotek in map dostopate in jih spreminjate s pomočjo FileSystemObject v VBA.
Spodaj je tabela, ki prikazuje najpomembnejše predmete, do katerih lahko dostopate in jih spreminjate z uporabo FSO:
| Objekt | Opis |
| Vozi | Drive Object vam omogoča, da dobite informacije o pogonu, na primer, ali obstaja ali ne, njegovo ime poti, vrsto pogona (odstranljivo ali fiksno), njegovo velikost itd. |
| Mapa | Objekt mape vam omogoča ustvarjanje ali spreminjanje map v vašem sistemu. S tem predmetom lahko na primer ustvarite, izbrišete, preimenujete in kopirate mape. |
| mapa | File Object vam omogoča delo z datotekami v vašem sistemu. S tem predmetom lahko na primer ustvarite, odprete, kopirate, premaknete in izbrišete datoteke. |
| TextStream | Objekt TextStream vam omogoča ustvarjanje ali branje besedilnih datotek. |
Vsak od zgornjih predmetov ima metode, s katerimi lahko delate s temi.
Če želite na primer zbrisati mapo, uporabite metodo DeleteFolder predmeta Folder. Podobno, če želite kopirati datoteko, boste uporabili metodo CopyFile predmeta File.
Ne skrbite, če se vam zdi to pretežko ali težko razumljivo. Ko boste pregledali primere, ki sem jih obravnaval v tej vadnici, boste veliko bolje razumeli.
Za referenco sem obravnaval vse metode FileSystemObject (za vsak predmet) na koncu te vadnice.
Omogočanje FileSystemObject v Excelu VBA
FileSystemObject privzeto ni na voljo v Excelovem VBA.
Ker imamo opravka z datotekami in mapami, ki so zunaj aplikacije Excel, moramo najprej ustvariti referenco na knjižnico, ki hrani te predmete (pogone, datoteke, mape).
Zdaj obstajata dva načina za začetek uporabe FileSystemObject v Excelu VBA:
- Nastavitev sklica na Microsoftovo knjižnico izvajalnega skripta (Scrrun.dll)
- Ustvarjanje predmeta za sklicevanje na knjižnico iz same kode
Medtem ko obe metodi delujeta (v nadaljevanju vam bom pokazal, kako to storiti), priporočam uporabo prve metode.
Opomba: Ko omogočite FileSystemObject, lahko dostopate do vseh predmetov v njem. To vključuje FileSystemObject, Drive, Datoteke, Mape itd. V tej vadnici se bom osredotočil predvsem na FileSystemObject.Nastavitev sklica na knjižnico Microsoft Scripting Runtime Library
Ko ustvarite referenco na Knjižnico izvajalnega skripta, omogočite Excelu VBA dostop do vseh lastnosti in metod datotek in map. Ko to storite, se lahko v Excelu VBA sklicujete na predmet datoteke/mape/pogone (tako kot se lahko sklicujete na celice, delovne liste ali delovne zvezke).
Spodaj so navedeni koraki za ustvarjanje sklica na knjižnico Microsoft Scripting Runtime Library:
- V urejevalniku VB kliknite Orodja.
- Kliknite na Reference.
- V pogovornem oknu Reference, ki se odpre, se pomaknite po razpoložljivih referencah in preverite možnost »Microsoft Scripting Runtime«.
- Kliknite V redu.
Zgornji koraki bi vam zdaj omogočili sklicevanje na objekte FSO iz Excela VBA.
Ustvarjanje primerka FileSystemObject v kodi
Ko nastavite sklic na knjižnico Scripting FileSystemObject, morate v kodi ustvariti primerek objekta FSO.
Ko je to ustvarjeno, ga lahko uporabite v VBA.
Spodaj je koda, ki bo spremenljivko objekta MyFSO nastavila kot objekt FileSystemObject:
Sub CreatingFSO () Dim MyFSO As FileSystemObject Set MyFSO = New FileSystemObject End Sub
V tej kodi sem najprej spremenljivko MyFSO razglasil za objekt tipa FileSystemObject. To je mogoče le zato, ker sem ustvaril referenco na knjižnico Microsoft Scripting Runtime Library. Če sklic ni ustvarjen, bo to povzročilo napako (saj Excel ne bi prepoznal, kaj pomeni FileSystemObject).
V drugi vrstici se zgodita dve stvari:
- Ključna beseda NEW ustvari primerek FileSystemObject. To pomeni, da lahko zdaj uporabljam vse metode FileSystemObject za delo z datotekami in mapami. Če tega primerka ne ustvarite, ne boste mogli dostopati do metod FSO.
- Ključna beseda SET nastavi objekt MyFSO na ta nov primerek FileSystemObject. To mi omogoča uporabo tega predmeta za dostop do datotek in map. Na primer, če moram ustvariti mapo, lahko uporabim metodo MyFSO.CreateFolder.
Če želite, lahko zgornji dve izjavi združite tudi v eno, kot je prikazano spodaj:
Pod UstvarjanjeFSO () Zatemni MyFSO kot novo končnico podv. FileSystemObject
Velika prednost uporabe te metode (to je nastavitev sklicevanja na knjižnico izvajalnega okolja Microsoft Scripting Runtime) je, da boste pri uporabi objektov FSO v kodi lahko uporabili funkcijo IntelliSense, ki prikazuje metode in lastnosti, povezane z predmet (kot je prikazano spodaj).
To ni mogoče, če sklic ustvarite znotraj kode (opisano v nadaljevanju).
Ustvarjanje predmeta iz kode
Drug način za ustvarjanje sklicevanja na FSO je, da to storite iz kode. Pri tej metodi vam ni treba ustvariti referenc (kot je bilo storjeno v prejšnji metodi).
Ko pišete kodo, lahko ustvarite predmet v kodi in se obrnete na Scripting.FileSystemObject.
Spodnja koda ustvari objekt FSO in nato to naredi kot tip FileSystemObject.
Sub FSODemo () Dim FSO As Set Object FSO = CreateObject ("Scripting.FileSystemObject") End Sub
Čeprav se to morda zdi bolj priročno, je velika pomanjkljivost uporabe te metode v tem, da ne prikazuje IntelliSense, ko delate s predmeti v FSO. Zame je to velik minus in vedno priporočam uporabo prejšnje metode omogočanja FSO (to je z nastavitvijo sklica na »Microsoft Scripting Runtime«)
Primeri datotek VBA FileSystemObject
Zdaj pa se potopimo in poglejmo nekaj praktičnih primerov uporabe FileSystemObject v Excelu.
Primer 1: Preverite, ali datoteka ali mapa obstaja
Naslednja koda bo preverila, ali mapa z imenom "Test" obstaja ali ne (na določenem mestu).
Če mapa obstaja, je pogoj IF resničen in v sporočilnem polju se prikaže sporočilo - "Mapa obstaja". In če ne obstaja, se prikaže sporočilo - Mapa ne obstaja '.
Sub CheckFolderExist () Zatemni MyFSO kot FileSystemObject Nastavi MyFSO = Nov FileSystemObject Če MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Nato MsgBox "Mapa obstaja" Sicer MsgBox "Mapa ne obstaja" Konec, če se konča Pod
Podobno lahko preverite tudi, ali datoteka obstaja ali ne.
Spodnja koda preveri, ali je v podani mapi datoteka z imenom Test.xlsx ali ne.
Sub CheckFileExist () Dim MyFSO kot FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FileExists ("C: \ Users \ sumit \ Desktop \ Test \ Test.xlsx") Potem MsgBox "Datoteka obstaja" Drugače MsgBox "Datoteka ne obstaja" "End If End Sub
Primer 2: ustvarite novo mapo na določenem mestu
Spodnja koda bi ustvarila mapo z imenom 'Test' v pogonu C mojega sistema (v sistemu boste morali določiti pot, kamor želite ustvariti mapo).
Sub CreateFolder () Zatemni MyFSO kot FileSystemObject Set MyFSO = New FileSystemObject MyFSO.CreateFolder ("C: \ Users \ sumit \ Desktop \ Test") End Sub
Čeprav ta koda deluje v redu, bi prikazala napako, če mapa že obstaja.
Spodnja koda preveri, ali mapa že obstaja, in ustvari mapo, če je ni. Če mapa že obstaja, se prikaže sporočilo. Za preverjanje, ali mapa obstaja, sem uporabil datoteko Metoda FolderExists FSO.
Sub CreateFolder () Dim MyFSO kot FileSystemObject Set MyFSO = New FileSystemObject If MyFSO.FolderExists ("C: \ Users \ sumit \ Desktop \ Test") Nato MsgBox "Mapa že obstaja" Drugače MyFSO.CreateFolder ("C: \ Users \" sumit \ Desktop \ Test ") End If End Sub
Primer 3: Pridobite seznam vseh datotek v mapi
Spodnja koda bi prikazala imena vseh datotek v določeni mapi.
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") Za vsako datoteko MyFolder.Fi Debug.Print MyFile.Name Next MyFile End Sub
Ta koda je nekoliko bolj zapletena od tistih, ki smo jih že videli.
Kot sem že omenil v tej vadnici, ko se sklicujete na »knjižnico izvajalnega okolja Microsoft Scripting Runtime«, lahko uporabite FileSystemObject in vse druge predmete (na primer datoteke in mape).
V zgornji kodi uporabljam tri predmete - FileSystemObject, File in Folder. To mi omogoča pregled vseh datotek v določeni mapi. Nato z lastnostjo name dobim seznam vseh imen datotek.
Upoštevajte, da uporabljam Debug.Print, da dobim imena vseh datotek. Ta imena bodo navedena v neposrednem oknu urejevalnika VB.
Primer 4: Pridobite seznam vseh podmap v mapi
Spodnja koda bo dala imena vseh podmap v določeni mapi. Logika je popolnoma enaka kot v zgornjem primeru. Namesto datotek smo v tej kodi uporabili podmape.
Sub GetSubFolderNames () Dim MyFSO As FileSystemObject Dim MyFile As File Dim MyFolder Kot Mapa Dim MySubFolder Kot Mapa Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder ("C: \ Users \ sumit \ Desktop \ Test \ Desktop" V MyFolder.SubFolders Debug.Natisni MySubFolder.Name Naprej MySubFolder End Sub
Primer 5: Kopirajte datoteko z enega mesta na drugega
Spodnja koda bo datoteko kopirala iz mape »Vir« in jo kopirala v mapo »Destinacija«.
Sub CopyFile () Dim MyFSO kot 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 Vir: = SourceFile, Destination: = DestinationFolder &" \ SampleFileCopy.xlsx "End Sub
V zgornji kodi sem uporabil dve spremenljivki - SourceFile in DestinationFolder.
Izvorna datoteka vsebuje naslov datoteke, ki jo želim kopirati, spremenljivka DestinationFolder pa naslov v mapo, v katero želim kopirati datoteko.
Upoštevajte, da pri kopiranju datoteke ni dovolj, da navedete ime ciljne mape. Določiti morate tudi ime datoteke. Uporabite lahko isto ime datoteke ali pa ga tudi spremenite. V zgornjem primeru sem kopiral datoteko in jo poimenoval SampleFileCopy.xlsx
Primer 6: Kopirajte vse datoteke iz ene mape v drugo
Spodnja koda bo kopirala vse datoteke iz izvorne mape v ciljno mapo.
Sub CopyAllFiles () Zatemni MyFSO kot FileSystemObject Dim MyFile kot 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) Za vsak MyFile v MyFolder.Files MyFSO.CopyFile Vir: = MyFSO.GetFile (MyFile), _ Destination: = Desto "& MyFile.Name, Overwritefiles: = False Next MyFile End Sub
Zgornja koda bo kopirala vse datoteke iz izvorne mape v ciljno mapo.
Upoštevajte, da sem v metodi MyFSO.CopyFile lastnost 'Overwritefiles' določil kot False (to je privzeto True). To zagotavlja, da v primeru, da datoteko že imate v mapi, ni kopirana (in prikazala se bo napaka). Če odstranite »Overwritefiles« ali nastavite na True, bi bile v primeru, če so datoteke v ciljni mapi z istim imenom, te prepisane.
Nasvet za profesionalce: Pri kopiranju datotek vedno obstaja možnost prepisa datotek. V tem primeru bi bilo dobro dodati časovni žig skupaj z imenom. Tako boste zagotovili, da so imena vedno različna, in lahko preprosto sledite, katere datoteke so bile ob določenem času kopirane.Če želite kopirati datoteke samo določene razširitve, lahko to storite z uporabo stavka IF Then, da preverite, ali je razširitev xlsx ali ne.
Sub CopyExcelFilesOnly () Dim MyFSO Kot FileSystemObject Dim MyFile Kot File Dim SourceFolder Kot String Dim DestinationFolder As String Dim MyFolder Kot mapa Dim MySubFolder kot mapa SourceFolder = "C: \ Users \ sumit \ Desktop \ Source \ DestinationFolder =" C: sumit \ Desktop \ Destination "Set MyFSO = New Scripting.FileSystemObject Set MyFolder = MyFSO.GetFolder (SourceFolder) Za vsako MyFile v MyFolder.Files Če MyFSO.GetExtensionName (MyFile) =" xlsx "Potem MyFSO.CopyFile Vir: (MyFile), _ Destination: = DestinationFolder & "\" & MyFile.Name, Overwritefiles: = False End Če je naslednji MyFile End Sub
Metode FileSystemObject (FSO)
Tu so metode, ki jih lahko uporabite za vsak predmet. To je samo za referenco in ne skrbite preveč. Uporaba nekaterih od teh je bila prikazana v zgornjih primerih.
| Metode FSO | Za Objekt | Opis |
| DriveExists | Vozi | Preveri, ali pogon obstaja ali ne |
| GetDrive | Vozi | Vrne primerek pogonskega objekta na podlagi podane poti |
| GetDriveName | Vozi | Ponovi ime pogona |
| BuildPath | Mapa z datotekami | Ustvarite pot iz obstoječe poti in imena |
| CopyFile | Mapa z datotekami | Kopira datoteko |
| GetAbsolutePathName | Mapa z datotekami | Vrnite kanonično predstavitev poti |
| GetBaseName | Mapa z datotekami | Vrni osnovno ime s poti. Na primer, "D: \ TestFolder \ TestFile.xlsm" vrne TextFile.xlsm |
| GetTempName | Mapa z datotekami | Ustvarite ime, ki ga lahko uporabite za poimenovanje začasne datoteke |
| CopyFolder | Mapa | Kopira mapo z ene lokacije na drugo |
| Ustvari mapo | Mapa | Ustvari novo mapo |
| Izbriši mapo | Mapa | Izbriše določeno mapo |
| MapaObstoja | Mapa | Preveri, ali mapa obstaja ali ne |
| GetFolder | Mapa | Vrne primerek predmeta mape na podlagi podane poti |
| GetParentFolderName | Mapa | Ponovno prikaže ime nadrejene mape na podlagi podane poti |
| GetSpecialFolder | Mapa | Ugotovite lokacijo različnih sistemskih map. |
| Premakni mapo | Mapa | Premakne mapo z ene lokacije na drugo |
| DeleteFile | mapa | Izbriše datoteko |
| FileExists | mapa | Preveri, ali datoteka obstaja ali ne |
| GetExtensionName | mapa | Vrne pripono datoteke |
| GetFile | mapa | Vrne primerek datotečnega objekta na podlagi podane poti |
| GetFileName | mapa | Vrne ime datoteke |
| GetFileVersion | mapa | Vrne različico datoteke |
| Premakni datoteko | mapa | Premakne datoteko |
| CreateTextFile | mapa | Ustvari besedilno datoteko |
| GetStandardStream | mapa | Pridobite standardni vnos, izhod ali tok napak |
| OpenTextFile | mapa | Odprite datoteko kot TextStream |
