A VBA FileSystemObject (FSO) használata az Excelben – egyszerű áttekintés és példák

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.

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:

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:

1. A hivatkozás beállítása a Microsoft Scripting Runtime Library (Scrrun.dll)
2. 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:

1. A VB-szerkesztőben kattintson az Eszközök gombra.
2. Kattintson a Hivatkozásokra.
3. 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.
4. 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:

1. 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.
2. 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.

.

.

.

.

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.