Získajte zoznam názvov súborov z priečinka pomocou Excelu (s a bez VBA)

Prvý deň v malej poradenskej firme mi pridelili trojdňový krátkodobý projekt.Práca je jednoduchá.

Na sieťovom disku je veľa priečinkov, každý so stovkami súborov.

Musel som postupovať podľa týchto troch krokov:

1. Vyberte súbor a skopírujte jeho názov.
2. Prilepte názov do bunky v Exceli a stlačte Enter.
3. Prejdite na nasledujúci súbor a zopakujte kroky 1 a 2.

Znie to jednoducho, však?

Je to jednoduché a časovo veľmi náročné.

To, čo som urobil za tri dni, sa dá zvládnuť za pár minút, ak poznám správnu techniku.

V tomto návode vám ukážem rôzne spôsoby, ako urobiť celý proces super rýchlym a super jednoduchým (s VBA aj bez neho).

Obmedzenia metód uvedených v tomto návode:Pomocou techniky uvedenej nižšie budete môcť získať iba názvy súborov vo svojom domovskom priečinku.Nezískate názvy súborov v podpriečinkoch v hlavnom priečinku.Tu je spôsob, ako získať názvy súborov z priečinkov a podpriečinkov pomocou Power Query

Pomocou funkcie FILES získate zoznam názvov súborov z priečinka

počul o tomFunkcia FILES?

Ak nie, nebojte sa.

Je to z excelovskej tabuľky z detstva (vzorce verzie 4).

Hoci tento vzorec nefunguje s bunkami hárka, stále funguje s pomenovanými rozsahmi.Túto skutočnosť využijeme na získanie zoznamu názvov súborov zo zadaného priečinka.

Teraz predpokladajme, že máte na pracovnej ploche súbor s názvom „ Testovací priečinok “ a chcete získať zoznam názvov všetkých súborov v tomto priečinku.

Tu sú kroky, ktoré vám poskytnú názvy súborov v tomto priečinku:

1. Do bunky A1 zadajte celú adresu priečinka a za ňou hviezdičku (*)
   • Napríklad, ak bol váš priečinok na jednotke C, adresa by bola niečo ako
     C:\Používatelia\VAŠE MENO\Počítač\Testovací priečinok\*
   • Ak si nie ste istí, ako získať adresu priečinka, použite nasledujúcu metódu:
     • • V priečinku, z ktorého chcete získať názov súboru, vytvorte nový excelový zošit alebo otvorte existujúci zošit v priečinku a použite nasledujúci vzorec v ľubovoľnej bunke.Tento vzorec vám poskytne adresu priečinka s hviezdičkou (*) na konci.Teraz môžete skopírovať a vložiť (prilepiť ako hodnotu) túto adresu do ľubovoľnej bunky v zošite (v tomto príklade A1), kde chcete názov súboru.

         =REPLACE(BUŇKA("názov súboru"),NÁJSŤ("[",BUNKA("názov súboru")),LEN(BUŇKA("názov súboru"),"*")

         [Ak ste vytvorili nový zošit v priečinku na použitie vyššie uvedeného vzorca a získanie adresy priečinka, možno ho budete musieť odstrániť, aby sa nenachádzal v zozname súborov v tomto priečinku]

2. Prejdite na kartu "Vzorce" a kliknite na možnosť "Definovať názvy".
3. V dialógovom okne Nový názov použite nasledujúce podrobnosti
   • Názov: FileNameList (neváhajte a vyberte si ľubovoľné meno)
   • Rozsah: Pracovný zošit
   • Vzťahuje sa na: =FILES(Hárok1!$A$1)
4. Teraz, aby sme získali zoznam súborov, použijeme pomenovaný rozsah vo funkcii INDEX.Prejdite do bunky A3 (alebo do ktorejkoľvek bunky, od ktorej chcete, aby zoznam mien začínal) a zadajte nasledujúci vzorec:

   =IFERROR(INDEX(Zoznam názvov súborov,RIADOK()-2),"")

5. Potiahnite ho nadol a zobrazí sa zoznam všetkých názvov súborov v priečinku

Chcete extrahovať súbory s konkrétnou príponou? ?

Ak chcete získať všetky súbory s konkrétnou príponou, stačí zmeniť hviezdičku s touto príponou.Napríklad, ak chcete iba súbor Excel, môžete použiť *xls* namiesto *

Takže adresa priečinka, ktorú musíte použiť, jeC: Priečinok UsersSumitDesktopTest*xls*

Podobne pre súbory dokumentov programu Word použite *doc*

Ako to funguje?

Vzorec FILES získa názvy všetkých súborov so zadanou príponou v zadanom priečinku.

Vo vzorci INDEX uvádzame názvy súborov ako pole a pomocou funkcie ROW vrátime prvé, druhé, tretie atď.

Všimnite si, že som použilRIADOK()-2, keďže začíname na treťom riadku.Takže keď je číslo riadku 4, ROW()-2 bude 1 pre prvý výskyt, 2 pre druhý atď.

Získajte zoznam všetkých názvov súborov z priečinka pomocou VBA

Teraz musím povedať, že vyššie uvedená metóda je trochu komplikovaná (existuje veľa krokov).

Je to však oveľa lepšie ako to robiť ručne.

Ak vám však vyhovuje používanie VBA (alebo ak ste dobrí v dodržiavaní presných krokov, ktoré uvediem nižšie), môžete si vytvoriť vlastnú funkciu (UDF), ktorá dokáže jednoducho získať názvy všetkých súborov.

Výhody používania funkcií definovaných používateľom (UDF)是vyplechovka將funkciuUložte si do osobných zošitov makier a jednoducho ich znova použite bez toho, aby ste museli tieto kroky znova a znova opakovať.Môžete tiež vytvárať doplnky a zdieľať túto funkciu s ostatnými.

Teraz mi dovoľte, aby som vám najprv poskytol kód VBA, ktorý vytvorí funkciu na získanie zoznamu všetkých názvov súborov z priečinka v Exceli.

Funkcia GetFileNames(ByVal FolderPath As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO as Object Dim MyFolder as Object Dim MyFiles as Object MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Pre každý MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

Vyššie uvedený kód vytvorí funkciu GetFileNames, ktorú možno použiť v pracovnom hárku (rovnako ako bežnú funkciu).

Kam vložiť tento kód?

Ak chcete tento kód skopírovať do editora VB, postupujte podľa nasledujúcich krokov.

Ako používať túto funkciu?

Tu sú kroky na použitie tejto funkcie v pracovnom hárku:

• Do ľubovoľnej bunky zadajte adresu priečinka, z ktorého chcete vypísať názvy súborov.
• Do bunky, kde chcete zoznam, zadajte nasledujúci vzorec (zadal som ho do bunky A3):

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

• Skopírujte a prilepte vzorec do bunky nižšie, aby ste získali zoznam všetkých súborov.

Všimnite si, že som zadal umiestnenie priečinka do jednej bunky a potom doGetFileNamesTáto bunka sa používa vo vzorci.Adresu priečinka môžete vo vzorci zakódovať aj takto:

=IFERROR(INDEX(GetFileNames("C:\Používatelia\VAŠE MENO\Počítač\Testovací priečinok"),ROW()-2),"")

Vo vyššie uvedenom vzorci sme použili RIADOK()-2 a začíname tretím riadkom.To zaisťuje, že keď skopírujem vzorec v bunke pod ním, zvýši sa o 1.Ak vzorec zadáte do prvého riadku stĺpca, môžete jednoducho použiť ROW().

Ako tento vzorec funguje?

Vzorec GetFileNames vráti pole obsahujúce názvy všetkých súborov v priečinku.

Funkcia INDEX sa používa na zobrazenie zoznamu jedného súboru na bunku, počnúc prvým.

Funkcia IFERROR sa používa na vrátenie prázdneho miesta namiesto #REF!Chyba sa zobrazí, keď sa vzorec skopíruje do bunky, ale v zozname nie sú žiadne ďalšie názvy súborov.

Získajte zoznam všetkých názvov súborov s konkrétnou príponou pomocou VBA

Vyššie uvedený vzorec je užitočný, keď chcete získať zoznam všetkých názvov súborov z priečinka v Exceli.

Čo však robiť, ak chcete získať iba názov súboru videa alebo súboru programu Excel alebo súboru, ktorý obsahuje určité kľúčové slovo.

V tomto prípade môžete použiť trochu inú funkciu.

Nižšie uvedený kód vám umožní získať všetky názvy súborov, ktoré majú v sebe špecifické kľúčové slovo (alebo špecifickú príponu).

Funkcia GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile as Object Dim MyFSO as Object Dim MyFolder as Object Dim MyFiles as Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Pre každý MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

Vyššie uvedený kód vytvorí funkciu, ktorú možno použiť v pracovnom hárku" GetFileNamesbyExt “ (rovnako ako bežné funkcie).

Táto funkcia má dva parametre – umiestnenie priečinka a kľúčové slovo rozšírenia.Vráti pole názvov súborov zodpovedajúcich danej prípone.Ak nie je zadané žiadne rozšírenie alebo kľúčové slovo, vráti všetky názvy súborov v zadanom priečinku.

Syntax: =GetFileNamesbyExt("Umiestnenie priečinka","Rozšírenie")

Kam vložiť tento kód?

Ak chcete tento kód skopírovať do editora VB, postupujte podľa nasledujúcich krokov.

• Prejdite na kartu vývojár.
• Kliknite na tlačidlo Visual Basic.Tým sa otvorí editor VB.
• V editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt v zošite, na ktorom pracujete, prejdite na položku Vložiť a kliknite na položku Modul.Ak nevidíte Project Explorer, použite klávesovú skratku Control + R (podržte control a stlačte "R").
• Dvakrát kliknite na objekt modulu a skopírujte a prilepte vyššie uvedený kód do okna kódu modulu.

Ako používať túto funkciu?

Tu sú kroky na použitie tejto funkcie v pracovnom hárku:

• Do ľubovoľnej bunky zadajte adresu priečinka, z ktorého chcete vypísať názvy súborov.Zadal som to do bunky A1.
• Do bunky zadajte príponu (alebo kľúčové slovo), ktoré chcete, aby boli všetky názvy súborov.Zadal som to do bunky B1.
• Do bunky, kde chcete zoznam, zadajte nasledujúci vzorec (zadal som ho do bunky A3):

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

• Skopírujte a prilepte vzorec do bunky nižšie, aby ste získali zoznam všetkých súborov.

Čo tyAkékoľvek triky Excelu, ktoré používate na zjednodušenie života.Rád by som sa od vás učil.Zdieľajte to v sekcii komentárov!