✅ Získajte zoznam názvov súborov zo priečinka v programe Excel (s VBA a bez neho)

Obsah

Použitie funkcie SÚBORY na získanie zoznamu názvov súborov zo priečinka
Pomocou VBA získajte zoznam všetkých názvov súborov z priečinka
Pomocou VBA získate zoznam všetkých názvov súborov s konkrétnou príponou

Prvý deň v práci v malej poradenskej firme som bol tri dni zamestnaný krátkym projektom.

Práca bola jednoduchá.

Na sieťovom disku bolo veľa priečinkov a v každom priečinku boli stovky súborov.

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

Vyberte súbor a skopírujte jeho názov.
Vložte tento názov do bunky v programe Excel a stlačte kláves Enter.
Prejdite na nasledujúci súbor a zopakujte kroky 1 a 2.

Znie to jednoducho, že?

Bolo to - jednoduché a obrovská strata času.

To, čo mi trvalo tri dni, by sa dalo zvládnuť za pár minút, keby som poznal správne techniky.

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

Obmedzenia metód uvedených v tomto návode: Pomocou nižšie uvedených techník budete môcť získať názvy súborov iba v hlavnom priečinku. Názvy súborov v podpriečinkoch v hlavnom priečinku nedostanete. Toto je spôsob, ako získať názvy súborov z priečinkov a podpriečinkov pomocou Power Query

Použitie funkcie SÚBORY na získanie zoznamu názvov súborov zo priečinka

Počul Funkcia FILES predtým?

Ak nie, nebojte sa.

Je to z detských čias excelových tabuliek (vzorec verzie 4).

Aj keď tento vzorec nefunguje v bunkách pracovného hárka, stále funguje v pomenovaných rozsahoch. Túto skutočnosť použijeme na získanie zoznamu názvov súborov zo zadaného priečinka.

Predpokladajme, že máte priečinok s názvom - „Testovací priečinok„Na ploche a chcete získať zoznam názvov súborov pre všetky súbory v tomto priečinku.

Nasledujú kroky, ktoré vám poskytnú názvy súborov z tohto priečinka:

Do bunky A1 zadajte úplnú adresu priečinka a za ním znak hviezdičky (*)
- Ak napríklad máte priečinok na disku C, adresa by vyzerala takto
  C: \ Users \ Sumit \ Desktop \ Test Folder \*
- Ak si nie ste istí, ako získať adresu priečinka, použite nasledujúci postup:
  - - V priečinku, z ktorého chcete získať názvy súborov, buď vytvorte nový zošit programu Excel, alebo otvorte existujúci zošit v priečinku a v ktorejkoľvek bunke použite nižšie uvedený vzorec. Tento vzorec vám poskytne adresu priečinka a na koniec pridá znak hviezdičky (*). Teraz môžete túto adresu skopírovať (prilepiť ako hodnotu) do ľubovoľnej bunky (v tomto prípade A1) v zošite, v ktorom chcete názvy súborov.
```
= REPLACE (CELL ("názov súboru"), FIND ("[", CELL ("názov súboru")), LEN (CELL ("názov súboru")), "*")
```
      [Ak ste v priečinku vytvorili nový zošit na použitie vyššie uvedeného vzorca a získali adresu priečinka, možno ho budete chcieť odstrániť, aby sa nezobrazoval v zozname súborov v tomto priečinku]
Prejdite na kartu „Vzorce“ a kliknite na možnosť „Definovať meno“.
V dialógovom okne Nové meno použite nasledujúce podrobnosti
- Názov: FileNameList (neváhajte si vybrať meno, ktoré sa vám páči)
- Rozsah: Pracovný zošit
- Vzťahuje sa na: = SÚBORY (list1! $ A $ 1)
Teraz, aby sme získali zoznam súborov, použijeme pomenovaný rozsah v rámci funkcie INDEX. Prejdite do bunky A3 (alebo do ľubovoľnej bunky, v ktorej chcete začať zoznam mien) a zadajte nasledujúci vzorec:
```
= IFERROR (INDEX (FileNameList, ROW ()-2), "")
```
Potiahnite prstom 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, zmeňte hviezdičku pomocou tejto prípony. Napríklad, ak chcete iba súbory programu Excel, môžete použiť * xls * namiesto *

Adresa priečinka, ktorú musíte použiť, by teda bola C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

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

Ako to funguje?

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

Vo vzorci INDEX sme ako pole uviedli názvy súborov a pomocou funkcie ROW vrátime 1., 2., 3. názov súboru a podobne.

Všimnite si, že som použil RIADOK ()-2, keďže sme začínali od tretieho radu ďalej. ROW ()-2 by teda bolo 1 pre prvú inštanciu, 2 pre druhú inštanciu, ak je číslo riadka 4, a tak ďalej a tak ďalej.

Sledujte video - získajte zoznam názvov súborov zo priečinka v programe Excel

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

Teraz musím povedať, že vyššie uvedená metóda je trochu zložitá (s niekoľkými krokmi).

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

Ak vám však používanie VBA vyhovuje (alebo ak sa vyznáte v presných krokoch, ktoré uvediem nižšie), môžete si vytvoriť vlastnú funkciu (UDF), pomocou ktorej môžete ľahko získať názvy všetkých súborov.

Výhoda používania a User Defektívne Function (UDF) je, že funkciu môžete uložiť do osobného zošita makier a ľahko ju znova použiť bez toho, aby ste kroky znova a znova opakovali. Môžete tiež vytvoriť doplnok a zdieľať túto funkciu s ostatnými.

Teraz mi najskôr poskytnem kód VBA, ktorý vytvorí funkciu na získanie zoznamu všetkých názvov súborov z priečinka v programe Excel.

Funkcia GetFileNames (ByVal FolderPath ako reťazec) Ako Variant Dim Výsledok Ako Variant Dim i Ako Integer Dim MyFile Ako Object Dim MyFSO Ako Object Dim MyFolder Ako Object Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder (FolderPath) Nastaviť MyFiles = MyFolder.Files Výsledok ReDim (1 To MyFiles.Count) i = 1 pre každý MyFile v MyFiles Výsledok (i) = MyFile.Name i = i + 1 Ďalší MyFile GetFileNames = Výsledok Koniec Funkcia

Vyššie uvedený kód vytvorí funkciu GetFileNames, ktorú je možné použiť v pracovných hárkoch (rovnako ako bežné funkcie).

Kam vložiť tento kód?

Pri kopírovaní tohto kódu do editora VB postupujte podľa nižšie uvedených pokynov.

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 na ľubovoľný objekt zošita, v ktorom pracujete, prejdite na Vložiť a kliknite na Modul. Ak sa vám Project Explorer nezobrazuje, použite klávesovú skratku Control + R (podržte kláves Ctrl a stlačte kláves „R“).
Dvakrát kliknite na objekt Module a skopírujte a prilepte vyššie uvedený kód do okna kódu modulu.

Ako používať túto funkciu?

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

Do ľubovoľnej bunky zadajte adresu priečinka, z ktorého chcete uviesť názvy súborov.
Do bunky, v ktorej chcete získať zoznam, zadajte nasledujúci vzorec (zadávam ho do bunky A3):
```
= IFERROR (INDEX (GetFileNames ($ A $ 1), ROW ()-2), "")
```
Skopírujte a prilepte vzorec do nižšie uvedených buniek, aby ste získali zoznam všetkých súborov.

Všimnite si toho, že som zadal umiestnenie priečinka do bunky a potom som použil túto bunku v GetFileNames vzorec. Adresu priečinka môžete tiež natvrdo kódovať vo vzorci, ako je uvedené nižšie:

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

Vo vyššie uvedenom vzorci sme použili ROW ()-2 a začali sme od tretieho radu. To zaistilo, že keď skopírujem vzorec do buniek nižšie, zvýši sa o 1. V prípade, že zadávate vzorec v prvom riadku stĺpca, môžete jednoducho použiť ROW ().

Ako tento vzorec funguje?

Vzorec GetFileNames vracia pole, ktoré obsahuje názvy všetkých súborov v priečinku.

Funkcia INDEX sa používa na vypísanie jedného názvu súboru na bunku od prvého.

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

Pomocou VBA získate zoznam všetkých názvov súborov s konkrétnou príponou

Vyššie uvedený vzorec funguje skvele, keď chcete získať zoznam všetkých názvov súborov z priečinka v programe Excel.

Ale čo keď chcete získať názvy iba súborov videa alebo iba súborov programu Excel alebo iba názvov súborov, ktoré obsahujú konkrétne kľúčové slovo.

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

Nasleduje kód, ktorý vám umožní získať všetky názvy súborov s konkrétnym kľúčovým slovom (alebo s konkrétnou príponou).

Funkcia GetFileNamesbyExt (ByVal FolderPath ako reťazec, FileExt ako reťazec) Ako Variant Dim Výsledok Ako Variant Dim I Ako Integer Dim MyFile Ako Object Dim MyFSO Ako Object Dim MyFolder Ako Object Dim MyFiles Ako Objekt Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder (FolderPath) Nastaviť MyFiles = MyFolder.Files Výsledok ReDim (1 To MyFiles.Count) i = 1 pre každý MyFile v MyFiles If InStr (1, MyFile.Name, FileExt) 0 Potom Výsledok (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Zachovať výsledok (1 až i - 1) GetFileNamesbyExt = Výsledok Koniec Funkcia

Vyššie uvedený kód vytvorí funkciu „GetFileNamesbyExt„Ktoré je možné použiť v pracovných listoch (rovnako ako bežné funkcie).

Táto funkcia vyžaduje dva argumenty - umiestnenie priečinka a kľúčové slovo rozšírenia. Vracia pole názvov súborov, ktoré sa zhodujú s danou príponou. Ak nie je zadaná žiadna prípona 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?

Pri kopírovaní tohto kódu do editora VB postupujte podľa nižšie uvedených pokynov.

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 na ľubovoľný objekt zošita, v ktorom pracujete, prejdite na Vložiť a kliknite na Modul. Ak sa vám Project Explorer nezobrazuje, použite klávesovú skratku Control + R (podržte kláves Ctrl a stlačte kláves „R“).
Dvakrát kliknite na objekt Module a skopírujte a prilepte vyššie uvedený kód do okna kódu modulu.

Ako používať túto funkciu?

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

Do ľubovoľnej bunky zadajte adresu priečinka, z ktorého chcete uviesť názvy súborov. Zadal som to do bunky A1.
Do bunky zadajte príponu (alebo kľúčové slovo), pre ktoré chcete zadať všetky názvy súborov. Zadal som to do bunky B1.
Do bunky, kde chcete zoznam, zadajte nasledujúci vzorec (zadávam ho do bunky A3):
```
= IFERROR (INDEX (GetFileNamesbyExt ($ A $ 1, $ B $ 1), ROW ()-2), "")
```
Skopírujte a prilepte vzorec do nižšie uvedených buniek, aby ste získali zoznam všetkých súborov.