Szerezze be a fájlnevek listáját az Excel mappájából (VBA -val és anélkül)

Munkám első napján egy kis tanácsadó cégben három napig egy rövid projekten dolgoztam.

A munka egyszerű volt.

Sok mappa volt a hálózati meghajtón, és minden mappában több száz fájl volt.

Ezt a három lépést kellett követnem:

1. Válassza ki a fájlt, és másolja a nevét.
2. Illessze be ezt a nevet az Excel egyik cellájába, és nyomja meg az Enter billentyűt.
3. Lépjen a következő fájlra, és ismételje meg az 1. és 2. lépést.

Egyszerűen hangzik ugye?

Ez volt - Egyszerű és hatalmas időpocsékolás.

Ami három napig tartott, néhány perc alatt elkészülhetett volna, ha ismerem a megfelelő technikákat.

Ebben az oktatóanyagban különböző módszereket mutatok be, amelyekkel szupergyors és szuper egyszerűvé teheti ezt az egész folyamatot (VBA -val és anélkül).

Az oktatóanyagban bemutatott módszerek korlátai: Az alábbiakban bemutatott technikákkal csak a fő mappában található fájlok nevét kaphatja meg. A fő mappa almappáiban található fájlok nevét nem kapja meg. Íme egy módja annak, hogy a Power Query használatával letöltse a fájlok nevét a mappákból és almappákból

A FILES funkció használata a fájlnevek listájának lekéréséhez egy mappából

Hallott róla FILES funkció előtt?

Ne aggódjon, ha nem tette.

Az Excel táblázatok gyermekkori korából származik (egy 4 -es verzió képlet).

Ez a képlet nem működik a munkalap celláiban, de megnevezett tartományokban működik. Ezt a tényt fogjuk használni a fájlnevek listájának lekéréséhez egy megadott mappából.

Tegyük fel, hogy van egy mappája a következő névvel: "Tesztmappa„Az asztalon, és meg akarja kapni a mappa összes fájljának fájlneveinek listáját.

Íme a lépések, amelyek megadják a mappából származó fájlneveket:

1. Az A1 cellában adja meg a mappa teljes címét, majd egy csillagjelet (*)
   • Például, ha a mappája a C meghajtón van, akkor a cím így néz ki
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Ha nem biztos abban, hogyan szerezheti be a mappa címét, használja a következő módszert:
     • • Abban a mappában, ahonnan a fájlneveket szeretné lekérni, hozzon létre egy új Excel -munkafüzetet, vagy nyisson meg egy meglévő munkafüzetet a mappában, és használja az alábbi képletet bármely cellában. Ez a képlet megadja a mappa címét, és egy csillag jelet (*) ad hozzá a végéhez. Most másolhatja és beillesztheti (beillesztheti értékként) ezt a címet a munkafüzet bármely cellájába (ebben a példában A1), amelyben a fájlneveket szeretné megadni.

         = REPLACE (CELL ("fájlnév"), FIND ("[", CELL ("fájlnév")), LEN (CELL ("fájlnév")), "*")

         [Ha új munkafüzetet hozott létre a mappában a fenti képlet használatához, és megkapta a mappa címét, érdemes törölni, hogy ne jelenjen meg az adott mappa fájllistájában]
2. Lépjen a „Képletek” fülre, és kattintson a „Név meghatározása” lehetőségre.
3. Az Új név párbeszédpanelen használja a következő részleteket
   • Név: FileNameList (nyugodtan válassza ki a kívánt nevet)
   • Hatály: Munkafüzet
   • Hivatkozik: = FILES (1. lap! $ A $ 1)
4. Most, hogy megkapjuk a fájlok listáját, az INDEX függvényen belül a megnevezett tartományt fogjuk használni. Lépjen az A3 cellába (vagy bármelyik cellába, ahol a nevek listáját szeretné elindítani), és írja be a következő képletet:

   = IFERROR (INDEX (FájlnévLista, ROW ()-2), "")

5. Húzza lefelé, és megjelenik a mappa összes fájlneve

Szeretne fájlokat kibontani egy adott kiterjesztéssel ??

Ha az összes fájlt egy adott kiterjesztéssel szeretné megkapni, csak módosítsa a csillagot az adott fájlkiterjesztéssel. Például, ha csak Excel fájlokat szeretne, használhatja az * xls * parancsot a * helyett

Tehát a használni kívánt mappa címe az lenne C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Hasonlóképpen, Word dokumentumfájlok esetén használja a *doc *

Hogy működik ez?

A FILES képlet lekéri a megadott kiterjesztésű fájlok nevét a megadott mappában.

Az INDEX képletben a fájlneveket adtuk meg tömbként, és visszaadjuk az 1., 2., 3. fájlnevet és így tovább a ROW függvény használatával.

Vegye figyelembe, hogy használtam Sor ()-2, ahogy a harmadik sorból indultunk. Tehát a ROW ()-2 az első esetben 1, a második a 2, ha a sor száma 4, és így tovább.

Videó megtekintése - Fájlnevek listájának lekérése az Excel mappájából

A VBA használata Lásd az összes fájlnevet egy mappából

Most azt kell mondanom, hogy a fenti módszer kissé összetett (több lépéssel).

Ez azonban sokkal jobb, mint manuálisan csinálni.

De ha jól érzi magát a VBA használatával (vagy ha jól tudja követni az alább felsorolt ​​lépéseket), akkor létrehozhat egy egyéni funkciót (UDF), amely könnyen megkapja az összes fájl nevét.

A használat előnye a User Defined Function (UDF), hogy elmentheti a függvényt egy személyes makró munkafüzetbe, és könnyen felhasználhatja anélkül, hogy a lépéseket újra és újra megismételné. Létrehozhat egy bővítményt, és megoszthatja ezt a funkciót másokkal.

Most először hadd adjam meg a VBA kódot, amely létrehoz egy függvényt, amely lekéri az összes fájlnév listáját egy Excel mappából.

Funkció GetFileNames (ByVal FolderPath mint karakterlánc) Variant Dim eredményeként Variant Dim i Mint Integer Dim MyFile objektumként Dim MyFSO objektumként Dim MyFolder objektumként Dim MyFiles objektumhalmazként MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder (FolderPath) Állítsa be a MyFiles = MyFolder.Files ReDim eredményt (1 a MyFiles.Count -hoz) i = 1 Minden MyFile -ban a MyFiles -ben Eredmény (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Eredmény vége funkció

A fenti kód létrehoz egy GetFileNames függvényt, amely használható a munkalapokon (csakúgy, mint a szokásos függvények).

Hova tegyem ezt a kódot?

Kövesse az alábbi lépéseket a kód másolásához a VB szerkesztőben.

• Lépjen a Fejlesztő fülre.
• Kattintson a Visual Basic gombra. Ez megnyitja a VB szerkesztőt.
• A VB szerkesztőben kattintson a jobb gombbal a munkafüzet bármelyik objektumára, amelyben dolgozik, lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ha nem látja a Project Explorer programot, használja a Control + R billentyűparancsot (tartsa lenyomva a Control billentyűt, és nyomja meg az „R” gombot).
• Kattintson duplán a Modul objektumra, és másolja be a fenti kódot a modulkód ablakba.

Hogyan kell használni ezt a funkciót?

Az alábbiakban bemutatjuk a funkció használatát a munkalapon:

• Bármely cellába írja be annak a mappának a címét, amelyből fel szeretné sorolni a fájlneveket.
• A listába tartozó cellába írja be a következő képletet (ezt az A3 cellába írom be):

  = IFERROR (INDEX (GetFileNames ($ A $ 1), ROW ()-2), "")

• Másolja és illessze be a képletet az alábbi cellákba az összes fájl listájának megtekintéséhez.

Ne feledje, hogy beírtam a mappa helyét egy cellába, majd ezt a cellát használtam a GetFileNames képlet. A mappa címét az alábbi képlet szerint is kódolhatja:

= IFERROR (INDEX (GetFileNames ("C: \ Users \ Sumit \ Desktop \ Test Folder"), ROW ()-2), "")

A fenti képletben a ROW ()-2-t használtuk, és a harmadik sorból kezdtük. Ez biztosította, hogy amikor a képletet az alábbi cellákba másolom, az 1 -gyel nő. Ha a képletet az oszlop első sorába írja be, akkor egyszerűen használhatja a ROW () billentyűt.

Hogyan működik ez a képlet?

A GetFileNames képlet egy tömböt ad vissza, amely a mappa összes fájljának nevét tartalmazza.

Az INDEX funkció segítségével cellánként egy fájlnevet sorolhat fel, az elsőtől kezdve.

Az IFERROR függvényt üresen adja vissza a #REF! hiba, amely akkor jelenik meg, ha egy képletet egy cellába másol, de nincs több listázandó fájlnév.

A VBA használata Szerezze be az összes fájlnév listáját egy adott kiterjesztéssel

A fenti képlet nagyszerűen működik, ha az Excel fájlban lévő összes fájlnevet szeretné listázni.

De mi van akkor, ha csak a videofájlok nevét, vagy csak az Excel fájlokat szeretné megkapni, vagy csak azokat a fájlneveket, amelyek egy adott kulcsszót tartalmaznak.

Ebben az esetben egy kicsit más funkciót használhat.

Az alábbiakban található a kód, amely lehetővé teszi, hogy minden fájlnevet megkapjon egy adott kulcsszóval (vagy egy adott kiterjesztéssel).

Funkció GetFileNamesbyExt (ByVal FolderPath mint karakterlánc, FileExt mint karakterlánc) Variant Dim eredményeként Variant Dim i Mint Integer Dim MyFile mint Objektum Dim MyFSO mint Objektum Dim Saját mappa Objektum Dim Saját fájlok objektumhalmazként MyFSO = CreateObject ("Scripting.FileSystemObject") MyFolder = MyFSO.GetFolder (FolderPath) Állítsa be a MyFiles = MyFolder.Files ReDim eredményt (1 a MyFiles.Count -hoz) i = 1 Minden MyFile -ban a MyFiles -ben If InStr (1, MyFile.Name, FileExt) 0 Aztán Result (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Reserve Result (1 to i - 1) GetFileNamesbyExt = Result End Function

A fenti kód létrehoz egy függvényt "GetFileNamesbyExt„Használható a munkalapokon (csakúgy, mint a szokásos funkciók).

Ez a függvény két érvet tartalmaz - a mappa helyét és a kiterjesztés kulcsszavát. A megadott kiterjesztésnek megfelelő fájlnevek tömbjét adja vissza. Ha nincs megadva kiterjesztés vagy kulcsszó, akkor a megadott mappa összes fájlnevét visszaadja.

Szintaxis: = GetFileNamesbyExt („Mappa helye”, „Bővítmény”)

Hova tegyem ezt a kódot?

Kövesse az alábbi lépéseket a kód másolásához a VB szerkesztőben.

• Lépjen a Fejlesztő fülre.
• Kattintson a Visual Basic gombra. Ez megnyitja a VB szerkesztőt.
• A VB szerkesztőben kattintson a jobb gombbal a munkafüzet bármelyik objektumára, amelyben dolgozik, lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ha nem látja a Project Explorer programot, használja a Control + R billentyűparancsot (tartsa lenyomva a Control billentyűt, és nyomja meg az „R” gombot).
• Kattintson duplán a Modul objektumra, és másolja be a fenti kódot a modulkód ablakba.

Hogyan kell használni ezt a funkciót?

Az alábbiakban bemutatjuk a funkció használatát a munkalapon:

• Bármely cellába írja be annak a mappának a címét, amelyből fel szeretné sorolni a fájlneveket. Ezt beírtam az A1 cellába.
• Egy cellába írja be a kiterjesztést (vagy a kulcsszót), amelyhez az összes fájlnevet szeretné megadni. Ezt beírtam a B1 cellába.
• A listába tartozó cellába írja be a következő képletet (ezt az A3 cellába írom be):

  = IFERROR (INDEX (GetFileNamesbyExt ($ A $ 1, $ B $ 1), ROW ()-2), "")

• Másolja és illessze be a képletet az alábbi cellákba az összes fájl listájának megtekintéséhez.

És veled mi van? Bármilyen Excel -trükk, amellyel megkönnyíti az életet. Szívesen tanulnék tőled. Oszd meg a megjegyzés rovatban!