Amikor a VBA-t használjuk az Excelben, a legtöbbször a feladataink automatizálására használjuk.
Ez azt is jelenti, hogy legtöbbször cellákkal és tartományokkal, munkalapokkal, munkafüzetekkel és más objektumokkal dolgozunk, amelyek az Excel alkalmazás részét képezik.
A VBA azonban ennél sokkal erősebb, és az Excelen kívüli dolgokkal való munkára is használható.
Ebben a bemutatóban megmutatom, hogyan használhatja a VBA FileSystemObject (FSO) eszközt a rendszerben vagy hálózati meghajtókon lévő fájlokkal és mappákkal való munkához.
Ez a bemutató a következőket tartalmazza:
Mi az a VBA FileSystemObject (FSO)?
A FileSystemObject (FSO) lehetővé teszi a számítógép fájlrendszerének elérését. Segítségével elérheti és módosíthatja a számítógép rendszerében lévő fájlokat/mappákat/könyvtárakat.
Az alábbiakban például néhány dolgot mutatunk be, amit a FileSystemObject használatával az Excel VBA-ban elvégezhet:
- Elérheti, hogy egy fájl vagy mappa létezik-e.
- Mappákat/fájlokat hozhat létre vagy nevezhet át.
- Listát kap egy mappában lévő összes fájlnévről (vagy almappa nevéről).
- Fájlok másolása egyik mappából a másikba.
Remélem, érti a lényeget.
A fenti példákat (és még többet is) a tananyag későbbi részében ismertetem.
Míg a fent említett dolgok egy része hagyományos VBA függvények (például a DIR függvény) és módszerek segítségével is elvégezhető, ez hosszabb és bonyolultabb kódokhoz vezetne. A FileSystemObject megkönnyíti a fájlokkal és mappákkal való munkát, miközben a kód tiszta és rövid marad.
Note: FSO can only be used in Excel 2000 and later versions.
Milyen összes objektumot érhetünk el a FileSystemObject segítségével?
Amint már említettem, a FileSystemObject segítségével a VBA-ban elérhetjük és módosíthatjuk a fájlokat és mappákat.
Az alábbi táblázat a legfontosabb objektumokat mutatja be, amelyeket az FSO segítségével elérhet és módosíthat:
Object | Description |
Drive | A Drive objektum segítségével információt kaphat a meghajtóról, például arról, hogy létezik-e vagy sem, az útvonal nevéről, a meghajtó típusáról (cserélhető vagy fix), a méretéről stb. |
Mappa | A Mappa objektum lehetővé teszi a rendszerben lévő mappák létrehozását vagy módosítását. Például létrehozhat, törölhet, átnevezhet, másolhat mappákat ezzel az objektummal. |
File | File objektum lehetővé teszi, hogy a rendszerben lévő fájlokkal dolgozzon. Például létrehozhat, megnyithat, másolhat, áthelyezhet és törölhet fájlokat ezzel az objektummal. |
TextStream | TextStream objektum lehetővé teszi szöveges fájlok létrehozását vagy olvasását. |
A fenti objektumok mindegyikének vannak metódusai, amelyekkel dolgozhatunk velük.
Egy példával élve, ha egy mappát szeretnénk törölni, akkor a Folder objektum DeleteFolder metódusát használjuk. Hasonlóképpen, ha egy fájlt szeretne másolni, akkor a File objektum CopyFile metódusát fogja használni.
Ne aggódjon, ha ez túlterhelőnek vagy nehezen érthetőnek tűnik. Sokkal jobban meg fogja érteni, ha végigmegy a példákon, amelyeket ebben a bemutatóban leírtam.
Csak a hivatkozás kedvéért, a bemutató végén leírtam az összes FileSystemObject metódust (minden egyes objektumhoz).
A FileSystemObject engedélyezése az Excel VBA-ban
A FileSystemObject alapértelmezés szerint nem érhető el az Excel VBA-ban.
Mivel az Excel alkalmazáson kívüli fájlokkal és mappákkal foglalkozunk, először hivatkozást kell létrehoznunk arra a könyvtárra, amely ezeket az objektumokat (meghajtókat, fájlokat, mappákat) tartalmazza.
Most kétféleképpen kezdhetjük el a FileSystemObject használatát az Excel VBA-ban:
- A hivatkozás beállítása a Microsoft Scripting Runtime Library (Scrrun.dll)
- Egy objektum létrehozása a könyvtárra való hivatkozáshoz magából a kódból
Míg mindkét módszer működik (és a következőkben megmutatom, hogyan kell ezt megtenni), én az első módszer használatát javaslom.
A Microsoft Scripting Runtime Libraryre való hivatkozás beállítása
Amikor létrehozza a Scripting Runtime Libraryre való hivatkozást, az Excel VBA számára hozzáférést biztosít a fájlok és mappák összes tulajdonságához és módszeréhez. Ha ez megtörtént, akkor az Excel VBA-n belül hivatkozhat a fájlok/mappák/meghajtók objektumra (ugyanúgy, ahogyan a cellákra, munkalapokra vagy munkafüzetekre is hivatkozhat).
A Microsoft Scripting Runtime Library-re való hivatkozás létrehozásának alábbi lépései:
- A VB-szerkesztőben kattintson az Eszközök gombra.
- Kattintson a Hivatkozásokra.
- A megnyíló Hivatkozások párbeszédpanelen görgesse végig a rendelkezésre álló hivatkozásokat, és jelölje be a ‘Microsoft Scripting Runtime’ opciót.
- Kattintson az OK gombra.
A fenti lépések most már lehetővé teszik az FSO-objektumokra való hivatkozást az Excel VBA-ból.
A FileSystemObject példányának létrehozása a kódban
Mihelyt beállította a hivatkozást a Scripting FileSystemObject könyvtárra, létre kell hoznia az FSO-objektum példányát a kódjában.
Mihelyt ez létrejött, már használhatja is a VBA-ban.
Az alábbi kódban a MyFSO objektumváltozót FileSystemObject objektumként állítjuk be:
Sub CreatingFSO()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectEnd Sub
A kódban először a MyFSO változót FileSystemObject típusú objektumként deklaráltam. Ez csak azért lehetséges, mert létrehoztam egy hivatkozást a Microsoft Scripting Runtime Library-re. Ha a hivatkozás nincs létrehozva, akkor ez hibát fog adni (mivel az Excel nem ismerné fel, hogy mit jelent a FileSystemObject).
A második sorban két dolog történik:
- A NEW kulcsszó létrehozza a FileSystemObject egy példányát. Ez azt jelenti, hogy most már a FileSystemObject összes metódusát használhatom a fájlokkal és mappákkal való munkához. Ha nem hozod létre ezt a példányt, nem fogod tudni elérni az FSO metódusait.
- A SET kulcsszó a MyFSO objektumot a FileSystemObject ezen új példányára állítja. Ez lehetővé teszi, hogy ezt az objektumot használjam a fájlok és mappák elérésére. Ha például egy mappát kell létrehoznom, használhatom a MyFSO.CreateFolder metódust.
Ha akarjuk, a fenti két utasítást kombinálhatjuk egybe is, ahogy az alább látható:
Sub CreatingFSO()Dim MyFSO As New FileSystemObjectEnd Sub
A módszer használatának (vagyis a Microsoft Scripting Runtime Library-re való hivatkozás beállításának) nagy előnye, hogy amikor az FSO objektumokat használjuk a kódunkban, használhatjuk az IntelliSense funkciót, amely megmutatja az objektumhoz tartozó metódusokat és tulajdonságokat (ahogy az alább látható).
Ez nem lehetséges, ha a hivatkozást a kódból hozza létre (ezt a következőkben tárgyaljuk).
Objektum létrehozása a kódból
Az FSO-ra való hivatkozás létrehozásának másik módja a kódból történő létrehozás. Ennél a módszernél nem kell hivatkozást létrehozni (mint az előző módszernél).
A kód írásakor létrehozhatunk egy objektumot a kódból, és hivatkozhatunk a Scripting.FileSystemObject.
Az alábbi kód létrehoz egy FSO objektumot, majd ezt FileSystemObject típusúvá teszi.
Sub FSODemo()Dim FSO As ObjectSet FSO = CreateObject("Scripting.FileSystemObject")End Sub
Míg ez kényelmesebbnek tűnhet, a módszer használatának nagy hátránya, hogy nem jelenítene meg IntelliSense-t, amikor az FSO objektumokkal dolgozik. Számomra ez óriási negatívum, és mindig az FSO engedélyezésének korábbi módszerét javaslom (ami a “Microsoft Scripting Runtime” hivatkozás beállításával történik)
VBA FileSystemObject példák
Most merüljünk bele, és nézzünk meg néhány gyakorlati példát a FileSystemObject használatára az Excelben.
1. példa: Fájl vagy mappa létezésének ellenőrzése
A következő kód ellenőrzi, hogy a ‘Teszt’ nevű mappa létezik-e vagy sem (a megadott helyen).
Ha a mappa létezik, az IF feltétel True lesz, és egy üzenőmezőben megjelenik egy üzenet – ‘A mappa létezik’. Ha pedig nem létezik, akkor megjelenik egy üzenet – A mappa nem létezik’.
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
Hasonlóképpen ellenőrizhetjük azt is, hogy egy fájl létezik-e vagy sem.
Az alábbi kód azt ellenőrzi, hogy van-e a megadott mappában egy Test.xlsx nevű fájl vagy sem.
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
2. példa: Új mappa létrehozása a megadott helyen
Az alábbi kód létrehoz egy ‘Test’ nevű mappát a rendszerem C meghajtóján (meg kell adnia a rendszerén az elérési utat, ahol a mappát létrehozni szeretné).
Sub CreateFolder()Dim MyFSO As FileSystemObjectSet MyFSO = New FileSystemObjectMyFSO.CreateFolder ("C:\Users\sumit\Desktop\Test")End Sub
Míg ez a kód jól működik, hibát mutatna abban az esetben, ha a mappa már létezik.
Az alábbi kód ellenőrzi, hogy a mappa létezik-e már, és létrehoz egy mappát, ha nem. Abban az esetben, ha a mappa már létezik, üzenetet jelenít meg. Annak ellenőrzésére, hogy a mappa létezik-e, az FSO FolderExists metódusát használtam.
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
3. példa: A mappában lévő összes fájl listájának lekérdezése
Az alábbi kód megmutatná a megadott mappában lévő összes fájl nevét.
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
Ez a kód egy kicsit összetettebb, mint az eddig látottak.
Amint azt már említettem ebben a bemutatóban, a ‘Microsoft Scripting Runtime Library’ hivatkozásakor a FileSystemObject és az összes többi objektum (például Files és Folder) is használható.
A fenti kódban három objektumot használok: FileSystemObject, File és Folder. Ez lehetővé teszi számomra, hogy végigmenjek a megadott mappában lévő minden egyes fájlon. Ezután a name tulajdonság segítségével megkapom az összes fájlnév listáját.
Megjegyzem, hogy a Debug.Print segítségével kapom meg az összes fájl nevét. Ezek a nevek a VB-szerkesztő azonnali ablakában lesznek felsorolva.
4. példa: Egy mappa összes almappájának listájának lekérdezése
Az alábbi kód a megadott mappa összes almappájának nevét adja meg. A logika pontosan megegyezik a fenti példában leírtakkal. Ebben a kódban fájlok helyett almappákat használtunk.
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
5. példa: Fájl másolása egyik helyről a másikra
Az alábbi kód kimásolja a fájlt a ‘Source’ mappából és átmásolja a ‘Destination’ mappába.
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
A fenti kódban két változót használtam – SourceFile és DestinationFolder.
A SourceFile a másolni kívánt fájl címét tartalmazza, a DestinationFolder változó pedig annak a mappának a címét, ahová a fájlt másolni szeretném.
Megjegyezzük, hogy nem elegendő megadni a célmappa nevét, amikor egy fájlt másolunk. A fájl nevét is meg kell adnia. Használhatja ugyanazt a fájlnevet, vagy meg is változtathatja azt. A fenti példában a fájlt másoltam, és SampleFileCopy.xlsx
6. példa: Az összes fájl másolása az egyik mappából a másikba
Az alábbi kód az összes fájlt átmásolja a forrásmappából a célmappába.
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
A fenti kód az összes fájlt átmásolja a Forrás mappából a Cél mappába.
Megjegyzem, hogy a MyFSO.CopyFile metódusban a ‘Overwritefiles’ tulajdonságot False értékre állítottam (ez alapértelmezés szerint True). Ez biztosítja, hogy abban az esetben, ha már megvan a fájl a mappában, nem másolódik (és hibaüzenet jelenik meg). Ha eltávolítja a ‘Overwritefiles’-et, vagy True értékre állítja, abban az esetben, ha a célmappában már vannak azonos nevű fájlok, ezek felülíródnak.
Ha csak egy bizonyos kiterjesztésű fájlokat szeretne másolni, akkor ezt egy IF Then utasítással teheti meg, amely ellenőrzi, hogy a kiterjesztés xlsx-e vagy sem.
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) módszerek
Itt vannak az egyes objektumokhoz használható módszerek. Ez csak referenciaként szolgál, és nem foglalkozik vele túl sokat. Ezek közül néhánynak a használatát a fentebb tárgyalt példákban mutattuk be.
FSO Methods | For Object | Description | |
DriveExists | Drive | Leellenőrzi, hogy a meghajtó létezik-e vagy sem | |
GetDrive | Drive | A meghajtó objektum egy példányát adja vissza a megadott elérési útvonal alapján | |
GetDriveName | Drive | Meghajtó | A meghajtó nevének újbóli megadása |
BuildPath | File/Folder | Elkészít egy elérési utat egy meglévő elérési útvonalból és egy névből | |
CopyFile | File/Folder | Másol egy fájlt | |
GetAbsolutePathName | File/Folder | Az elérési útvonal kanonikus ábrázolását adja vissza | |
GetBaseName | File/Folder | Az elérési útvonal alapnevét adja vissza. Például a “D:\TestFolder\TestFile.xlsm” a TextFile fájlt adja vissza.xlsm | |
GetTempName | File/Folder | Nevet generál, amely egy ideiglenes fájl elnevezésére használható | |
CopyFolder | Folder | Mappa | Mappát másol egy helyről egy másik helyre. másikba |
CreateFolder | Folder | Új mappát hoz létre | |
DeleteFolder | Folder | DeleteFolder | Deletes a megadott mappa |
FolderExists | Mappa | Vizsgálja, hogy a mappa létezik-e vagy sem | |
GetFolder | Mappa | A mappa objektum egy példányát adja vissza a megadott útvonal alapján | |
GetParentFolderName | Folder | A szülőmappa nevét adja vissza a megadott elérési útvonal alapján | |
GetSpecialFolder | Folder | Folder | A különböző rendszermappák helyének megadása. |
MoveFolder | Folder | Mappa áthelyezése egyik helyről a másikra | |
DeleteFile | File | Fájl törlése | |
FileExists | File | Vizsgálja, hogy egy fájl létezik-e vagy sem | |
GetExtensionName | File | A fájl kiterjesztését adja vissza | |
GetFile | File | File | A fájl példányát adja vissza. objektum a megadott elérési útvonal alapján |
GetFileName | File | A fájl nevét adja vissza | |
GetFileVersion | File | A fájl verzióját adja vissza | |
MoveFile | File | Mozgat egy fájlt | |
CreateTextFile | File | Elkészít egy szöveges fájlt | |
GetStandardStream | File | Kéri vissza a standard bemenetet, kimeneti vagy hiba folyam | |
OpenTextFile | File | Fájl megnyitása TextStreamként |
Az alábbi Excel oktatóanyagok is tetszhetnek:
- Fájlnevek listájának kinyerése mappákból & Almappák (Power Query használatával).
- Fájlnevek listájának kinyerése egy mappából az Excelben (VBA-val és anélkül).
- Az Excel VBA adattípusok (változók és konstansok) megértése.
- Felhasználó által meghatározott függvény (UDF) létrehozása az Excel VBA-ban.