Získejte seznam názvů souborů ze složky v aplikaci Excel (s VBA i bez)

První den v práci v malé poradenské firmě jsem byl tři dny zaměstnán krátkým projektem.

Práce byla jednoduchá.

Na síťové jednotce bylo mnoho složek a každá složka obsahovala stovky souborů.

Musel jsem postupovat podle těchto tří kroků:

1. Vyberte soubor a zkopírujte jeho název.
2. Vložte tento název do buňky v aplikaci Excel a stiskněte Enter.
3. Přejděte na další soubor a opakujte kroky 1 a 2.

Zní to jednoduše, že?

Bylo to - jednoduché a obrovská ztráta času.

To, co mi trvalo tři dny, by se dalo zvládnout za pár minut, kdybych znal správné techniky.

V tomto tutoriálu vám ukážu různé způsoby, jak celý tento proces udělat super rychlý a super snadný (s VBA i bez něj).

Omezení metod uvedených v tomto kurzu: S níže uvedenými technikami budete moci získat pouze názvy souborů v hlavní složce. Názvy souborů nedostanete v podsložkách v hlavní složce. Zde je způsob, jak získat názvy souborů ze složek a podsložek pomocí Power Query

Pomocí funkce FILES získáte seznam názvů souborů ze složky

Slyšel jsem Funkce FILES před?

Nebojte se, pokud nemáte.

Je to z dětských dob excelových tabulek (vzorec verze 4).

I když tento vzorec nefunguje v buňkách listu, stále funguje v pojmenovaných rozsazích. Tuto skutečnost použijeme k získání seznamu názvů souborů ze zadané složky.

Předpokládejme, že máte složku se jménem - „Testovací složka‘Na ploše a chcete získat seznam názvů souborů pro všechny soubory v této složce.

Zde jsou kroky, které vám poskytnou názvy souborů z této složky:

1. Do buňky A1 zadejte úplnou adresu složky následovanou znakem hvězdičky (*)
   • Pokud například vaše složka na jednotce C vypadá, bude adresa vypadat takto
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Pokud si nejste jisti, jak získat adresu složky, použijte následující metodu:
     • • Ve složce, ze které chcete získat názvy souborů, buď vytvořte nový sešit aplikace Excel, nebo otevřete existující sešit ve složce a použijte níže uvedený vzorec v jakékoli buňce. Tento vzorec vám poskytne adresu složky a na konec přidá znak hvězdičky (*). Nyní můžete tuto adresu zkopírovat (vložit jako hodnotu) do libovolné buňky (v tomto případě A1) v sešitu, ve kterém chcete názvy souborů.

         = REPLACE (CELL ("název souboru"), FIND ("[", CELL ("název souboru")), LEN (CELL ("název souboru")), "*")

         [Pokud jste ve složce vytvořili nový sešit, který bude používat výše uvedený vzorec a získáte adresu složky, můžete jej smazat, aby se nezobrazoval v seznamu souborů v této složce]
2. Přejděte na kartu „Vzorce“ a klikněte na možnost „Definovat jméno“.
3. V dialogovém okně Nový název použijte následující podrobnosti
   • Název: FileNameList (můžete si vybrat libovolné jméno)
   • Rozsah: Sešit
   • Odkazuje na: = SOUBORY (List1! $ A $ 1)
4. Nyní, abychom získali seznam souborů, použijeme pojmenovaný rozsah v rámci funkce INDEX. Přejděte do buňky A3 (nebo do jakékoli buňky, kde chcete, aby začínal seznam jmen) a zadejte následující vzorec:

   = IFERROR (INDEX (FileNameList, ROW ()-2), "")

5. Přetáhněte toto dolů a zobrazí se seznam všech názvů souborů ve složce

Chcete extrahovat soubory se specifickou příponou ??

Pokud chcete získat všechny soubory s konkrétní příponou, změňte hvězdičku s touto příponou. Pokud například chcete pouze soubory aplikace Excel, můžete místo * použít * xls *

Adresa složky, kterou musíte použít, by tedy byla C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Podobně pro soubory dokumentů Word použijte *doc *

Jak to funguje?

Vzorec FILES načte názvy všech souborů zadané přípony v zadané složce.

Ve vzorci INDEX jsme jako pole zadali názvy souborů a pomocí funkce ROW vrátíme názvy 1., 2., 3. souboru atd.

Všimněte si, že jsem použil Řádek ()-2, jak jsme začínali od třetí řady dále. Takže ROW ()-2 by bylo 1 pro první instanci, 2 pro druhou instanci, když je číslo řádku 4, a tak dále a tak dále.

Sledujte video - získejte seznam názvů souborů ze složky v aplikaci Excel

Pomocí VBA Získejte seznam všech názvů souborů ze složky

Nyní musím říci, že výše uvedená metoda je poněkud složitá (s řadou kroků).

Je to však mnohem lepší, než to dělat ručně.

Pokud vám ale vyhovuje používání VBA (nebo pokud umíte přesně postupovat podle níže uvedených kroků), můžete si vytvořit vlastní funkci (UDF), která vám snadno poskytne názvy všech souborů.

Výhoda používání a User Dvyčerpaný Function (UDF) je, že můžete funkci uložit do osobního sešitu maker a snadno ji znovu použít, aniž byste kroky znovu a znovu opakovali. Můžete také vytvořit doplněk a sdílet tuto funkci s ostatními.

Nyní mi nejprve poskytněte kód VBA, který vytvoří funkci pro získání seznamu všech názvů souborů ze složky v aplikaci Excel.

Funkce GetFileNames (ByVal FolderPath jako řetězec) Jako Variant Dim Výsledek Jako Variant Dim i Jako Integer Dim MyFile Jako 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 For each MyFile In MyFiles Result (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

Výše uvedený kód vytvoří funkci GetFileNames, kterou lze použít v pracovních listech (stejně jako běžné funkce).

Kam tento kód vložit?

Chcete -li zkopírovat tento kód do editoru VB, postupujte podle následujících pokynů.

• Přejděte na kartu Vývojář.
• Klikněte na tlačítko Visual Basic. Tím se otevře editor VB.
• V editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, ve kterém pracujete, přejděte na Vložit a klikněte na Modul. Pokud nevidíte Průzkumníka projektů, použijte klávesovou zkratku Control + R (podržte ovládací klávesu a stiskněte klávesu „R“).
• Poklepejte na objekt Module a zkopírujte a vložte výše uvedený kód do okna kódu modulu.

Jak tuto funkci používat?

Níže jsou uvedeny kroky k použití této funkce v listu:

• Do libovolné buňky zadejte adresu složky, ze které chcete vypsat názvy souborů.
• Do buňky, kde chcete seznam, zadejte následující vzorec (zadávám jej do buňky A3):

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

• Zkopírujte a vložte vzorec do níže uvedených buněk, abyste získali seznam všech souborů.

Všimněte si toho, že jsem do buňky zadal umístění složky a poté tuto buňku použil v souboru GetFileNames vzorec. Adresu složky můžete také pevně kódovat ve vzorci, jak je uvedeno níže:

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

Ve výše uvedeném vzorci jsme použili ROW ()-2 a začali jsme od třetí řady dále. Tím bylo zajištěno, že když zkopíruji vzorec do níže uvedených buněk, zvýší se o 1. V případě, že zadáváte vzorec v prvním řádku sloupce, můžete jednoduše použít ROW ().

Jak tento vzorec funguje?

Vzorec GetFileNames vrací pole, které obsahuje názvy všech souborů ve složce.

Funkce INDEX se používá k vypsání jednoho názvu souboru na buňku, počínaje od prvního.

Funkce IFERROR se používá k vrácení prázdné místo #REF! chyba, která se zobrazí, když je vzorec zkopírován do buňky, ale nejsou k dispozici další názvy souborů.

Pomocí VBA získejte seznam všech názvů souborů se specifickou příponou

Výše uvedený vzorec funguje skvěle, pokud chcete získat seznam všech názvů souborů ze složky v aplikaci Excel.

Ale co když chcete získat názvy pouze video souborů nebo pouze souborů aplikace Excel nebo pouze názvy souborů, které obsahují konkrétní klíčové slovo.

V takovém případě můžete použít trochu jinou funkci.

Níže je kód, který vám umožní získat všechny názvy souborů s konkrétním klíčovým slovem (nebo s konkrétní příponou).

Funkce GetFileNamesbyExt (ByVal FolderPath jako řetězec, FileExt jako řetězec) Jako varianta Dim Výsledek jako Variant Dim i jako Integer Dim MyFile jako Object Dim MyFSO jako Object Dim MyFolder jako Object Dim MyFiles jako Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder (FolderPath) Nastavit MyFiles = MyFolder.Files Výsledek ReDim (1 To MyFiles.Count) i = 1 Pro 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

Výše uvedený kód vytvoří funkci „GetFileNamesbyExt„Které lze použít v pracovních listech (stejně jako běžné funkce).

Tato funkce má dva argumenty - umístění složky a klíčové slovo rozšíření. Vrací pole názvů souborů, které odpovídají dané příponě. Pokud není zadána žádná přípona ani klíčové slovo, vrátí všechny názvy souborů v zadané složce.

Syntaxe: = GetFileNamesbyExt („Umístění složky“, „Rozšíření“)

Kam tento kód vložit?

Při kopírování tohoto kódu do editoru VB postupujte podle následujících pokynů.

• Přejděte na kartu Vývojář.
• Klikněte na tlačítko Visual Basic. Tím se otevře editor VB.
• V editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, ve kterém pracujete, přejděte na Vložit a klikněte na Modul. Pokud Průzkumníka nevidíte, použijte klávesovou zkratku Control + R (podržte ovládací klávesu a stiskněte klávesu „R“).
• Dvakrát klikněte na objekt Module a zkopírujte a vložte výše uvedený kód do okna kódu modulu.

Jak tuto funkci používat?

Níže jsou uvedeny kroky k použití této funkce v listu:

• Do libovolné buňky zadejte adresu složky, ze které chcete vypsat názvy souborů. Zadal jsem to do buňky A1.
• Do buňky zadejte příponu (nebo klíčové slovo), pro které chcete všechny názvy souborů. Zadal jsem to do buňky B1.
• Do buňky, kde chcete seznam, zadejte následující vzorec (zadávám jej do buňky A3):

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

• Zkopírujte a vložte vzorec do níže uvedených buněk, abyste získali seznam všech souborů.

Co ty? Jakékoli triky aplikace Excel, které vám usnadní život. Rád bych se od vás učil. Podělte se o to v sekci komentářů!