Időről időre szemben találom magam azzal a feladattal, hogy az egy mappában található fájlokról kellene listát készítenem, lehetőleg egy továbbszerkeszthető formátumban. Úgy sejtem, hogy mások is találkoztak már a problémával, akár szabadidejükben a zenéiket, fényképeiket, e-bookjaikat (stb.) próbálván rendezgetni, akár a munkájuk során, amikor ellenőrizniük kellett, hogy minden fájl benne van-e a mappában, vagy egy hivatalos adatátadási dokumentum mellékletét kellett elkészíteni az átadott fájlokról.
Amikor szükségem van egy ilyen fájllistára, többnyire más adatokkal is szeretném kiegészíteni, ezért számomra az Excel egy ideális formátum. Emiatt készítettem egy makrót, ami elkészíti ezt a fájllistát és minden fájl nevét beírja egy munkalap első oszlopába. Az alábbiakban azt mutatom be, hogyan épül fel ez a makró, és elmagyarázom, egyes részei mit is csinálnak pontosan.
0. lépés - Excel makróírás alapok
Néhány általános gondolatot már leírtam korábban az Excelben készített makrókkal kapcsolatban. Az itt leírtak nagyban segítik a következők megértését, ezért ha még nem olvastad, javaslom, hogy fusd át!
1. lépés - Névadás
Mindegy, milyen nevet választunk a makrónak, bár javaslom, hogy a névből derüljön ki, nagyjából mit is csinál. A példában a ListFilenamesInFolder nevet adom neki. A típusa Sub legyen, mivel nem lesz visszatérő értéke, csak elvégzi a feladatot, amit beleírunk és aztán leáll. Rögtön írjunk is (komment formájában) pár szót arról, hogy mit is csinál a makró.
Sub ListFilenamesInFolder()
'A makró célja, hogy egy kiválasztott mappában található fájlokat kilistázza egy új munkalapra.'
End Sub
2. lépés - Változók létrehozása
Definiáljuk azokat a változókat, amelyeket használni szeretnénk a makró futtatása során. Nem baj, ha nem jut eszünkbe minden már rögtön a legelején, ez a lista később is bővíthető! Szükségünk lesz egy szöveges típusú változóra, ami a feldolgozandó mappa elérési útját tartalmazza, legyen a neve path. Kelleni fog továbbá egy másik szöveges változó, amelyben a fájl nevét fogjuk tárolni, ennek a neve legyen file. Az utolsó (legalábbis ezen a ponton az utolsó) változó az első üres sor számát adja meg a munkalapon, ahova a fájlok listáját írni fogjuk. Ez hosszú egész szám típusú legyen, hogy akár sok százezer fájlt is fel tudjunk dolgoztatni a makróval, a neve pedig legyen row.
Sub ListFileNamesInFolder()
'A makró célja, hogy kilistázza egy munkalapra az egy mappában található fájlokat.'
'Változók definiálása.'
Dim path As String
Dim file As String
Dim row As Long
End Sub
3. lépés - A mappa elérési útjának bekérése
A makró akkor lesz igazán jól használható, ha futtatáskor megkérdezi, melyik mappa tartalmát szeretnénk kilistázni és ezt nem a makró kódjának szerkesztésével kell megtennünk. Ennek érdekében megkérjük a felhasználót, hogy adja meg az elérési utat. Ez egyelőre egy egyszerű szöveges elérési út, vagyis nem teszi lehetővé, hogy tallózva válasszuk ki a mappát. Érdemes a fájlkezelő fejlécéből kimásolni az elérési utat, ha nincs kedvünk gépelni, és így az elgépelések esélyét is csökkentjük.
Az adat bekéréséhez az InputBox-ot használjuk, amelynek két paramétert adunk meg szövegesen: először a súgó szöveget, hogy mit várunk a beviteli mezőben, másodszor pedig az ablak fejlécében szereplő nevet. Végül megadunk egy harmadik paramétert (Type), aminek értéke 2. Ez egy kód, ami azt mondja meg, hogy csak szöveges bevitelt fogadjon el az InputBox.
Sub ListFileNamesInFolder()
'A makró célja, hogy kilistázza egy munkalapra az egy mappában található fájlokat.'
'Változók definiálása.'
Dim path As String
Dim file As String
Dim row As Long
'A fájlokat tartalmazó mappa elérési útjának bekérése'
path = Application.InputBox("Adja meg a mappa elérési útját: ", "Listázandó mappa elérési útja", Type:=2)
End Sub
4. lépés - Feldolgozás előkészítése
A legfontosabb információt már megszereztük, ám mielőtt neki kezdenénk a fájlok listázásának, néhány előkészítő lépést még meg kell tennünk.
- Hozzunk létre egy új munkalapot, aminek a neve legyen "LISTA". Ehhez a Worksheets.Add utasítást használjuk, kiegészítve a Before paraméterrel, hogy a legelső munkalap elé kerüljön az új.
- Nevezzük át az újonnan létrehozott munkalapot. Ehhez kihasználjuk, hogy tudjuk, immár az általunk létrehozott munkalap az első és így a sorszámával hivatkozhatunk rá.
- Az A1 cellába írjunk bele valamit, ami még nem fájlnév, inkább a lista fejléce lesz, például "Fájlok". Egy cellára többféle módon is lehet hivatkozni. Az egyik mód az Excelben megszokott módon az oszlop betűjelének és a sor számának kombinálása pl. G23. A másik, általam gyakrabban használt mód, hogy a cella koordinátáit (sorszámát) adom meg sor, oszlop sorrendben, vesszővel elválasztva. Szerintem az utóbbit könnyebb kezelni, amikor celláról cellára haladunk pl. egy iteráció közben, ezért már most is így (Worksheets.Cells(1,1).Value) hivatkozok rá.
- Adjuk meg, hogy a fájlokat hányadik sortól kezdve listázza. Mivel az előbb megadtunk egy fejlécet, ezért praktikusan a második sortól jó kezdeni.
- Olvassuk be a legelső fájlt. Ehhez a Dir utasítást tudjuk használni, aminek a korábban a felhasználó által megadott elérési utat adjuk paraméterül. Ez az utasítás szöveges (String) típust ad eredményül, ezért definiáltuk így a file változót.
Sub ListFileNamesInFolder()
'A makró célja, hogy kilistázza egy munkalapra az egy mappában található fájlokat.'
'Változók definiálása.'
Dim path As String
Dim file As String
Dim row As Long
'A fájlokat tartalmazó mappa elérési útjának bekérése'
path = Application.InputBox("Adja meg a mappa elérési útját: ", "Listázandó mappa elérési útja", Type:=2)
'Új munkalap beszúrása, ami a fájlok listáját fogja tartalmazni.'
Worksheets.Add Before := Worksheets(1)
'Az imént létrehozott munkalap átnevezése'
Worksheets(1).Name = "LISTA"
'Fejléc kiírása'
Worksheets(1).Cells(1,1).Value = "Fájlok"
'Melyik sorba kerüljön az első listázott fájl?'
row = 2
'Első fájlnév felolvasása'
file = Dir(path)
End Sub
5. lépés - Fájlok listázása
Most már minden készen áll ahhoz, hogy kilistázhassuk a fájlokat. Ehhez egy iterációra van szükség, vagyis hogy újra és újra és újra elvégezzük ugyanazt a feladatot. Az ismétlendő feladat nagyon egyszerű: írjuk bele a következő cellába a fájl nevét. Az viszont kérdéses, hogy hány alkalommal kell ezt elvégezni? A pontos számot nem tudjuk, ismerjük viszont a feltételt, amikor abba kell hagynia az ismétlést: ha már nincs több fájl.
Mindezt a makró nyelvére lefordítva először is megfogalmazzuk a ciklusmagot, vagyis azokat az utasításokat, amelyeket újra és újra szeretnénk végrehajtani, amíg már nincs több fájl. Sorban:
- A legutoljára beolvasott fájl nevét (ezt a file változó tartalmazza) írjuk bele a legfelső üres cellába (ennek a sorát a row változó tartalmazza, ezt fogjuk a Cells-nél a sor koordinátájaként használni).
- Olvassunk be egy új fájlt. (Ez a Dir() utasítással tehető meg.) Így a ciklusmag következő futásakor már a következő fájl nevét tudjuk majd kiíratni. Ha ezt elmulasztjuk, mindig ugyanazt a fájlnevet (az első fájl nevét) írjuk ki.
- Növeljük meg a row változó értékét eggyel. Így a ciklusmag következő futásakor már a következő sorba tudjuk majd kiíratni. Ha ezt elmulasztjuk, akkor mindig ugyanabba (a második) sorba írjuk a fájlok nevét (felülírva az előzőt), így csak a legutolsóként felolvasott fájl neve fog megjelenni a listánkban.
A ciklusmag többszöri végrehajtásához a While ciklust használjuk. A While típusú iteráció addig ismétli a kezdő (While) és záró (Wend) kulcsszavak közötti utasításokat (a ciklusmagot), amíg közvetlenül a While kulcsszó után megfogalmazott feltétel igaznak bizonyul. A ciklusból való kilépés feltételét így fogalmaztuk meg korábban: "ha már nincs több fájl". Ezt a makró nyelvére úgy tudjuk lefordítani, hogy addig hajtsa végre a ciklusmagot, amíg a file változónak van értéke vagyis nem üres. Tehát maga a feltétel így fog kinézni: file <> "".
Amikor elérjük az utolsó fájlt és kiírjuk a nevét (ciklusmag 1. utasítása) és megpróbáljuk beolvasni az újabb fájlt (2. utasítás), a Dir() nem talál majd újabb fájlt. Ilyen esetben egy üres szöveget ír majd bele a file változóba, vagyis ezen a ponton file = "" lesz. A While feltétele a következő vizsgálatnál hamis lesz és a ciklus véget ér.
Sub ListFileNamesInFolder()
'A makró célja, hogy kilistázza egy munkalapra az egy mappában található fájlokat.'
'Változók definiálása.'
Dim path As String
Dim file As String
Dim row As Long
'A fájlokat tartalmazó mappa elérési útjának bekérése'
path = Application.InputBox("Adja meg a mappa elérési útját: ", "Listázandó mappa elérési útja", Type:=2)
'Új munkalap beszúrása, ami a fájlok listáját fogja tartalmazni.'
Worksheets.Add Before := Worksheets(1)
'Az imént létrehozott munkalap átnevezése'
Worksheets(1).Name = "LISTA"
'Fejléc kiírása'
Worksheets(1).Cells(1,1).Value = "Fájlok"
'Melyik sorba kerüljön az első listázott fájl?'
row = 2
'Első fájlnév felolvasása'
file = Dir(path)
'A ciklus kezdete. A ciklusmag (A While... és Wend közötti sorok) addig fog újra és újra lefutni,'
'míg a file változó rendelkezik értékkel (pontosabban: nem üres szöveg az értéke)'
While file <> ""
'A fájl nevének kiírása a következő üres sorba.'
Worksheets(1).Cells(row, 1).Value = file
'A következő fájl felolvasása'
file = Dir()
'Az eredmény munkalapon a sor számának növelése'
row = row + 1
Wend
End Sub
6. lépés - A makró futtatása
Nincs más hátra, mint kipróbálni a makrót, hogy valóban jó-e!
Ha eddig nem az Excel makrószerkesztőjébe írtad a kódot, most nyisd meg az Excelt, válaszd ki a Nézet szalagon a Makrók ikont. Add meg a makró nevét, kattints a Létrehozás gombra, majd másold be a kódot! Nyomd meg a Futtatás ikont vagy az F5 billentyűt és reménykedjünk, hogy jól dolgoztunk, nem gépeltünk el semmit!
Kész is?
Sajnos ennyire azért nem egyszerű a helyzet. Még abban az esetben, ha nem gépeltél el semmit (ha mégis, ne aggódj, ez teljesen normális, mindenki vét gépelési hibát kódolás közben), a kódot nem készítettük fel minden helyzetre. A teljesség igénye nélkül néhány gondolat, ötlet, hogy miként lehetne továbbfejleszteni ezt a makrót:
- Nem vizsgálja meg, hogy a megadott elérési út valóban létezik-e, ezért ha az hibás, egy üres listát készít (mintha létezne az a mappa, csak üres lenne), nem hibaüzenetet ad vissza. Jó lenne, ha a lista elkészítése előtt ellenőrizné, hogy létezik-e a megadott mappa vagy sem. Ha nem, akkor felhívhatná a felhasználó figyelmét erre és újra kérhetné az elérési utat.
- Ugyanez a helyzet abban az esetben is, ha az elérési út ugyan helyes, de nem perjellel ("/") végződik. Tovább lehetne fejleszteni a makrót úgy, hogy vizsgálja meg először az eredeti mappát, majd ha az üresnek tűnik, próbálja meg úgy is, hogy mögé tesz egy perjelet és ha így már nem 0 elemű a találati lista, akkor ezt a listát írja ki.
- Az elérési út csak begépelve/bemásolva adható meg, nincs lehetőség tallózásra. Így nagyobb az esély az elgépelésre, hibázásra. Hasznos lenne, ha a fájlrendszerből tudná a felhasználó kitallózni a listázni kívánt mappát.
- Jelenleg, ha a megadott elérési úton nem csak fájlok vannak, hanem (al)mappák is, azokkal nem foglalkozik a makró, nem listázza ki őket. Jó lenne, ha ezeket is kilistázná valamilyen speciális jelölést alkalmazva a megkülönböztetésükre (pl. szögletes zárójelben írná ki a mappa nevét). Megkérdezhetné a felhasználótól is, hogy szeretné-e a listában látni a mappaneveket vagy sem?
- Ha létezik már "LISTA" nevű munkalap, a makró megakad és hibaüzenetet ad. Sokkal elegánsabb megoldás lenne, ha megvizsgáltatnánk, hogy létezik-e már a létrehozni kívánt munkalap, mielőtt létrehozzuk. Ha igen, akkor a név mögé tudunk illeszteni egy vagy több karaktert, hogy megkülönböztessük a már létezőtől, pl. lehetne az új munkalap neve "LISTA-1", ha "LISTA" már létezik.
- A létrejövő munkalap neve, valamint a fejléc fix, a felhasználó legfeljebb utólag tudja átnevezni. Ha most ezen változtatni szeretnénk, a makró kódján kell módosítani vagy utólag minden egyes esetben átnevezni. Elegánsabb megoldás lenne, ha a felhasználótól kérdeznénk meg, hogy mi legyen a neve a munkalapnak (aztán persze futtatnánk az ellenőrzést, amelyet az előző pontban vázoltam) és mi legyen a fejléc szövege.
- ...
A listát még lehetne folytatni, hiszen akár a végtelenségig bonyolítható egy ilyen egyszerű feladat is (pl. válassza-e külön a fájlnevet és a kiterjesztést vagy sem). A kérdés, hogy mi is pontosan a cél és ki(k)nek készül a makró? Ha csak mi fogjuk használni a saját makrónkat és minél gyorsabban szeretnénk listákat készíteni, akkor vélhetőleg megfelelő a most elkészített változat is, hiszen minden egyes felhasználótól érkező kérés lassítja a makró használatát (pl. ha mindig be kell gépelnünk, hogy "LISTA"). Ha azonban nem ismerjük még a leendő használóit, érdemes több hibakezelést beletenni, hiszen lehet, hogy a majdani felhasználó nem tudja, hogyan kell elhárítani az egyes hibákat, de lehet, hogy azt sem tudja, hogy kit kell megkeresni a problémájával.
Ennek szellemében egy későbbi cikkben továbbfejlesztjük még ezt az egyszerű kis makrót, de most csak örüljünk annak, amit készítettünk, és amelyet saját használatra (a korlátai ismeretével és figyelembe vételével) bátran ajánlok mindenkinek. Használjátok sok sikerrel és nyúljatok hozzá bátran a kódhoz, módosítgassátok, próbálkozzatok és higgyétek el, egyre jobban fogjátok érteni az Excel makrók működését!