Fájlnevek listája egy mappából az Excel segítségével (VBA-val és anélkül)

Az első napom egy kis tanácsadó cégnél kaptam egy háromnapos rövid távú projektet.A munka egyszerű.

A hálózati meghajtón sok mappa található, mindegyikben több száz fájl található.

A következő három lépést kellett követnem:

1. Válassza ki a fájlt, és másolja a nevét.
2. Illessze be 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, igaz?

Egyszerű és nagyon időigényes.

Amit három nap alatt megcsináltam, az percek alatt megcsinálható, ha ismerem a megfelelő technikát.

Ebben az oktatóanyagban különféle módokat mutatok be, amelyekkel az egész folyamatot szupergyorsíthatja és szuperkönnyűvé teheti (VBA-val és anélkül).

Az oktatóanyagban bemutatott módszerek korlátai:Az alább bemutatott technikával csak a saját mappájában lévő fájlok nevét tudja lekérni.Nem kapja meg a főmappa almappáiban lévő fájlok nevét.Itt van egy módja annak, hogy a Power Query segítségével fájlneveket szerezzen be mappákból és almappákból

A FÁJLOK funkcióval lekérheti a fájlnevek listáját egy mappából

hallott rólaFILES funkció?

Ha nem, ne aggódj.

Egy gyermekkori Excel-táblázatból származik (4-es verziójú képletek).

Bár ez a képlet nem működik a munkalap celláival, mégis működik az elnevezett tartományokkal.Ezt a tényt arra fogjuk használni, hogy megkapjuk a fájlnevek listáját a megadott mappából.

Most tegyük fel, hogy van egy fájl az asztalon, melynek neve " Tesztmappa ” és szeretné lekérni a fájlnevek listáját az adott mappában lévő összes fájlhoz.

Íme a lépések, amelyek megadják a fájlneveket ebben a mappában:

1. Az A1 cellába írja be a teljes mappacímet, majd egy csillagot (*)
   • Például, ha a mappája a C meghajtóban van, a cím valami ilyesmi lesz
     C:\Felhasználók\NEVÉD\Asztal\Tesztmappa\*
   • Ha nem biztos benne, hogyan szerezheti meg a mappa címét, használja a következő módszert:
     • • Abban a mappában, amelyből a fájlnevet 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 a következő képletet bármelyik cellában.Ez a képlet megadja a mappa címét egy csillaggal (*) a végén.Mostantól ezt a címet a munkafüzet bármely cellájába másolhatja-beillesztheti (beillesztheti értékként) (ebben a példában A1), ahol a fájlnevet szeretné megadni.

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

         [Ha új munkafüzetet hozott létre egy mappában a fenti képlet használatához és a mappa címének lekéréséhez, előfordulhat, hogy törölnie kell, hogy ne szerepeljen a mappában lévő fájlok listájában]

2. Lépjen a "Képletek" fülre, és kattintson a "Nevek meghatározása" lehetőségre.
3. Az Új név párbeszédpanelen használja a következő adatokat
   • Név: FileNameList (bátran válasszon bármilyen nevet)
   • Terjedelem: Munkafüzet
   • Hivatkozás: =FILES(1. lap!$A$1)
4. Most, hogy megkapjuk a fájlok listáját, egy elnevezett tartományt fogunk használni az INDEX függvényben.Lépjen az A3 cellába (vagy arra a cellára, amellyel a névlistát szeretné kezdeni), és írja be a következő képletet:

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

5. Húzza le, és megjelenik a mappában lévő összes fájlnév listája

Egy adott kiterjesztésű fájlokat szeretne kicsomagolni? ?

Ha az összes fájlt egy adott kiterjesztéssel szeretné megszerezni, egyszerűen módosítsa a csillagot az adott fájlkiterjesztéssel.Például, ha csak az excel fájlt szeretné, használhatja az *xls*-t a * helyett.

Tehát a használni kívánt mappacímC:UsersSumitDesktopTest mappa*xls*

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

Hogy működik ez?

A FILES képlet lekéri a megadott mappában lévő összes fájl nevét a megadott kiterjesztéssel.

Az INDEX képletben a fájlneveket tömbként adjuk meg, és a ROW függvénnyel visszaadjuk az első, második, harmadik stb. fájlneveket.

Vegye figyelembe, hogy használtamSOR()-2, hiszen a harmadik sorban kezdjük.Tehát ha a sor száma 4, a ROW()-2 lesz az első példányban 1, a másodikban 2, és így tovább.

Szerezzen le egy listát az összes fájlnévről egy mappából a VBA segítségével

Most azt kell mondanom, hogy a fenti módszer egy kicsit bonyolult (sok lépésből áll).

Ez azonban sokkal jobb, mintha ezt kézzel végezné.

Ha azonban jól érzi magát a VBA használatában (vagy ha jól tudja követni az alábbiakban felsorolt ​​lépéseket), létrehozhat egy egyéni függvényt (UDF), amely könnyen lekéri az összes fájl nevét.

A felhasználó által definiált függvények (UDF) használatának előnyei是Önképes將funkcióMentse el egy személyes makró-munkafüzetbe, és egyszerűen használja újra anélkül, hogy ezeket a lépéseket újra és újra meg kellene ismételnie.Bővítményeket is létrehozhat, é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.

Függvény GetFileNames(ByVal FolderPath As String) As Variant Dim Eredmény mint Variant Dim i As Integer Dim SajátFájl As Objektum Dim MyFSO mint Objektum Dim MyFolder As Object Dim SajátFájlok mint Objektum Set MyFSO = CreateObject("Scripting.Object MyFail")ystemObject. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 to MyFiles.Count) i = 1 Minden MyFile in MyFiles In Result(i) = MyFile.Name i = i + 1 Következő Saját fájl GetFileNames = Eredmény befejező függvény

A fenti kód egy GetFileNames függvényt hoz létre, amely használható a munkalapon (akárcsak egy normál függvény).

Hová kell tenni ezt a kódot?

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

Hogyan kell használni ezt a funkciót?

Íme a lépések a funkció használatához egy munkalapon:

• Bármely cellába írja be annak a mappának a címét, amelyből a fájlneveket listázni kívánja.
• Abba a cellába, ahol a listát szeretné, írja be a következő képletet (az A3 cellába írtam be):

  =IFERROR(INDEX(Fájlnevek lekérése($A$1),ROW()-2),"")

• Másolja és illessze be a képletet az alábbi cellába, hogy megkapja az összes fájl listáját.

Ne feledje, hogy a mappa helyét az egyik cellában, majd beírtamGetFileNamesEzt a cellát egy képletben használják.A mappa címét a képletben is kódolhatja, így:

=IFERROR(INDEX(GetFileNames("C:\Felhasználók\NEVÉD\Asztal\Tesztmappa"),ROW()-2),"")

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

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

A GetFileNames képlet egy tömböt ad vissza, amely tartalmazza a mappában lévő összes fájl nevét.

Az INDEX függvény cellánként egy fájlnév felsorolására szolgál, az elsővel kezdve.

Az IFERROR függvény a #REF helyett üresen tér vissza!Hiba jelenik meg, ha egy képletet másol egy cellába, de nincs több listázható fájlnév.

Szerezzen listát az összes fájlnévről egy adott kiterjesztéssel a VBA segítségével

A fenti képlet akkor hasznos, ha egy Excel mappában lévő összes fájlnév listáját szeretné lekérni.

De mi van akkor, ha csak egy videofájl nevét szeretné megkapni, vagy csak egy Excel-fájlt, vagy csak egy bizonyos kulcsszót tartalmazó fájlt.

Ebben az esetben egy kissé eltérő funkciót használhat.

Az alábbi kód lehetővé teszi az összes olyan fájlnév lekérését, amelyben egy adott kulcsszó (vagy egy adott kiterjesztés) szerepel.

Függvény GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Eredmény Változatként Dim i As Integer Dim As MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set (MyFiles As Object) = CreateObFiles"yst = CreateOb MyFolder = MyFSO.GetFolder(FolderPath) Set MyFolder = MyFolder.Files ReDim Result(1 to MyFiles.Count) i = 1 Minden MyFájlhoz a MyFájlokban If InStr(1, MyFile.Name, FileExt) <> 0 then Result(i) = Saját fájl.név i = i + 1 vége

A fenti kód egy függvényt hoz létre, amely használható a munkalapon" GetFileNamesbyExt " (akárcsak a normál függvényeknél).

Ez a funkció két paramétert igényel: a mappa helyét és a kiterjesztési kulcsszót.A megadott kiterjesztéssel egyező fájlnevek tömbjét adja vissza.Ha nincs megadva kiterjesztés vagy kulcsszó, akkor a megadott mappában lévő összes fájlnevet visszaadja.

Szintaxis: =GetFileNamesbyExt("Mappa helye","Kiterjesztés")

Hová kell tenni 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ői lapra.
• Kattintson a Visual Basic gombra.Ezzel megnyílik a VB szerkesztő.
• A VB-szerkesztőben kattintson a jobb gombbal a munkafüzet bármely objektumára, amelyen dolgozik, lépjen a Beszúrás menüpontra, és kattintson a Modul elemre.Ha nem látja a Project Explorert, használja a Control + R billentyűkombinációt (tartsa lenyomva a vezérlőt, és nyomja meg az "R" billentyűt).
• Kattintson duplán a modul objektumra, majd másolja ki és illessze be a fenti kódot a modul kód ablakába.

Hogyan kell használni ezt a funkciót?

Íme a lépések a funkció használatához egy munkalapon:

• Bármely cellába írja be annak a mappának a címét, amelyből a fájlneveket listázni kívánja.Ezt beírtam az A1 cellába.
• A cellába írja be az összes fájlnév kiterjesztését (vagy kulcsszót).Ezt beírtam a B1 cellába.
• Abba a cellába, ahol a listát szeretné, írja be a következő képletet (az A3 cellába írtam be):

  =IFERROR(INDEX(Fájlnevek lekérése Ext szerint($A$1,$B$1),ROW()-2),"")

• Másolja és illessze be a képletet az alábbi cellába, hogy megkapja az összes fájl listáját.

Mi van veledBármilyen Excel-trükk, amellyel egyszerűbbé teheti életét.Szívesen tanulnék tőled.Oszd meg a megjegyzés rovatban!