Pridobite seznam imen datotek iz mape v Excelu (z in brez VBA)

Prvi dan v službi v majhnem svetovalnem podjetju sem bil tri dni zaposlen na kratkem projektu.

Delo je bilo preprosto.

Na omrežnem pogonu je bilo veliko map in vsaka mapa je imela na stotine datotek.

Moral sem slediti tem trem korakom:

1. Izberite datoteko in kopirajte njeno ime.
2. To ime prilepite v celico v Excelu in pritisnite Enter.
3. Premaknite se na naslednjo datoteko in ponovite koraka 1 in 2.

Sliši se preprosto kajne?

Bilo je - preprosto in velika izguba časa.

Kar sem potreboval tri dni, bi bilo mogoče narediti v nekaj minutah, če bi poznal prave tehnike.

V tej vadnici vam bom pokazal različne načine, kako narediti celoten proces super hiter in zelo enostaven (z in brez VBA).

Omejitve metod, prikazanih v tej vadnici: S spodnjimi tehnikami boste lahko dobili imena datotek samo v glavni mapi. Imen datotek ne boste dobili v podmapah v glavni mapi. Tu je način pridobivanja imen datotek iz map in podmap z uporabo Power Query

Uporaba funkcije FILES za pridobivanje seznama imen datotek iz mape

Slišano za Funkcija FILES prej?

Ne skrbite, če niste.

To je iz otroških dni Excelovih preglednic (formula različice 4).

Čeprav ta formula ne deluje v celicah delovnega lista, še vedno deluje v imenovanih obsegih. To dejstvo bomo uporabili za pridobitev seznama imen datotek iz določene mape.

Recimo, da imate mapo z imenom - 'Preskusna mapa"Na namizju in želite dobiti seznam imen datotek za vse datoteke v tej mapi.

Tu so koraki, ki vam bodo dali imena datotek iz te mape:

1. V celico A1 vnesite popoln naslov mape, ki ji sledi zvezdica (*)
   • Na primer, če je vaša mapa v pogonu C, bi bil naslov videti tako
     C: \ Users \ Sumit \ Desktop \ Test Mapa \*
   • Če niste prepričani, kako pridobiti naslov mape, uporabite naslednjo metodo:
     • • V mapi, iz katere želite dobiti imena datotek, ustvarite nov Excelov delovni zvezek ali odprite obstoječi delovni zvezek v mapi in uporabite spodnjo formulo v kateri koli celici. Ta formula vam bo dala naslov mape in dodala zvezdico (*) na koncu. Zdaj lahko ta naslov kopirate in prilepite (prilepite kot vrednost) v katero koli celico (A1 v tem primeru) v delovnem zvezku, v katerem želite imena datotek.

         = REPLACE (CELL ("ime datoteke"), FIND ("[", CELL ("ime datoteke")), LEN (CELL ("ime datoteke")), "*")

         [Če ste v mapi ustvarili nov delovni zvezek za uporabo zgornje formule in dobili naslov mape, ga boste morda želeli izbrisati, da ne bo na seznamu datotek v tej mapi]
2. Pojdite na zavihek »Formule« in kliknite možnost »Določi ime«.
3. V pogovornem oknu Novo ime uporabite naslednje podrobnosti
   • Ime: FileNameList (izberite ime, ki vam je všeč)
   • Področje uporabe: Delovni zvezek
   • Nanaša se na: = FILES (List1! $ A $ 1)
4. Zdaj, da dobimo seznam datotek, bomo imenovani obseg uporabili v funkciji INDEX. Pojdite v celico A3 (ali katero koli celico, kjer želite, da se začne seznam imen) in vnesite naslednjo formulo:

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

5. Povlecite to navzdol in prikazal vam bo seznam vseh imen datotek v mapi

Želite izvleči datoteke z določeno razširitvijo ??

Če želite dobiti vse datoteke z določeno razširitvijo, samo spremenite zvezdico s to razširitvijo datoteke. Na primer, če želite samo datoteke Excel, lahko uporabite * xls * namesto *

Naslov mape, ki ga morate uporabiti, bi bil C: \ Users \ Sumit \ Desktop \ Test Mapa \*xls*

Podobno za datoteke dokumentov Word uporabite *doc *

Kako to deluje?

Formula FILES prikliče imena vseh datotek določene razširitve v določeni mapi.

V formuli INDEX smo dali imena datotek kot matriko in vrnemo 1., 2., 3. ime datoteke in tako naprej s funkcijo ROW.

Upoštevajte, da sem uporabil ROW ()-2, kot smo začeli od tretje vrstice naprej. Torej bi bilo ROW ()-2 1 za prvo instanco, 2 za drugo stopnjo, ko je številka vrstice 4, itd in tako naprej.

Oglejte si video - Pridobite seznam imen datotek iz mape v Excelu

Uporaba VBA Pridobite seznam vseh imen datotek iz mape

Zdaj moram reči, da je zgornja metoda nekoliko zapletena (z več koraki).

Je pa veliko bolje kot to narediti ročno.

Če pa uporabljate VBA (ali če dobro sledite natančnim korakom, ki jih bom navedel spodaj), lahko ustvarite funkcijo po meri (UDF), ki vam zlahka priskrbi imena vseh datotek.

Prednost uporabe a User Defined F.unction (UDF) je, da lahko funkcijo shranite v osebni delovni zvezek makra in jo preprosto znova uporabite, ne da bi korake ponavljali vedno znova. Ustvarite lahko tudi dodatek in delite to funkcijo z drugimi.

Naj vam najprej predstavim kodo VBA, ki bo ustvarila funkcijo za pridobivanje seznama vseh imen datotek iz mape v Excelu.

Funkcija GetFileNames (ByVal FolderPath As String) Kot Variant Dim Rezultat Kot Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder Kot Object Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = GetFolder (FolderPath) Nastavi MyFiles = MyFolder.Files Rezultat ReDim (1 do MyFiles.Count) i = 1 Za vsako MyFile v MyFiles Rezultat (i) = MyFile.Name i = i + 1 Naslednja MyFile GetFileNames = Končna funkcija rezultata

Zgornja koda bo ustvarila funkcijo GetFileNames, ki jo je mogoče uporabiti na delovnih listih (tako kot običajne funkcije).

Kam postaviti to kodo?

Sledite spodnjim korakom za kopiranje te kode v urejevalniku VB.

• Pojdite na zavihek Razvijalec.
• Kliknite gumb Visual Basic. S tem se odpre urejevalnik VB.
• V urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerem delate, pojdite na Vstavi in ​​kliknite na Modul. Če ne vidite Project Explorerja, uporabite bližnjico na tipkovnici Control + R (držite tipko za upravljanje in pritisnite tipko 'R').
• Dvokliknite objekt Module in zgornjo kodo kopirajte in prilepite v okno kode modula.

Kako uporabljati to funkcijo?

Spodaj so navedeni koraki za uporabo te funkcije na delovnem listu:

• V katero koli celico vnesite naslov mape, iz katere želite navesti imena datotek.
• V celico, kjer želite seznam, vnesite naslednjo formulo (vnesem jo v celico A3):

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

• Kopirajte in prilepite formulo v spodnje celice, da dobite seznam vseh datotek.

Upoštevajte, da sem v celico vnesel mesto mape in nato to celico uporabil v GetFileNames formula. Naslov mape lahko tudi težko kodirate v formuli, kot je prikazano spodaj:

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

V zgornji formuli smo uporabili ROW ()-2 in smo začeli od tretje vrstice naprej. To je zagotovilo, da se bo pri kopiranju formule v spodnjih celicah povečala za 1. Če formulo vnesete v prvo vrstico stolpca, lahko preprosto uporabite ROW ().

Kako deluje ta formula?

Formula GetFileNames vrne polje, ki vsebuje imena vseh datotek v mapi.

Funkcija INDEX se uporablja za navajanje enega imena datoteke v celico, začenši s prvo.

Funkcija IFERROR se uporablja za vrnitev praznega namesto #REF! napaka, ki se prikaže, ko se formula kopira v celico, vendar na seznamu ni več imen datotek.

Z uporabo VBA Pridobite seznam vseh imen datotek s posebno razširitvijo

Zgornja formula odlično deluje, če želite dobiti seznam vseh imen datotek iz mape v Excelu.

Kaj pa, če želite dobiti imena samo video datotek ali samo datotek Excel ali samo imen datotek, ki vsebujejo določeno ključno besedo.

V tem primeru lahko uporabite nekoliko drugačno funkcijo.

Spodaj je koda, ki vam bo omogočila, da dobite vsa imena datotek z določeno ključno besedo (ali z določeno razširitvijo).

Funkcija GetFileNamesbyExt (ByVal FolderPath As String, FileExt As String) Kot varianta Dim Rezultat Kot Variant Dim in As Integer Dim MyFile As Object Dim MyFSO Kot Object Dim MyFolder Kot Object Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject" MyFolder = MyFSO.GetFolder (FolderPath) Nastavi MyFiles = MyFolder.Files Rezultat ponovne prilagoditve (1 do MyFiles.Count) i = 1 Za vsako datoteko MyFile v MyFiles If InStr (1, MyFile.Name, FileExt) 0 Nato rezultat (i) = MyFile .Ime i = i + 1 konec, če naslednji MyFile ReDim ohrani rezultat (1 do i - 1) GetFileNamesbyExt = končna funkcija rezultata

Zgornja koda bo ustvarila funkcijo "GetFileNamesbyExt", Ki se lahko uporablja na delovnih listih (tako kot običajne funkcije).

Ta funkcija ima dva argumenta - lokacijo mape in ključno besedo razširitve. Vrne niz imen datotek, ki se ujemajo z dano razširitvijo. Če razširitev ali ključna beseda nista podani, bo vrnila vsa imena datotek v podani mapi.

Sintaksa: = GetFileNamesbyExt (»Lokacija mape«, »Razširitev«)

Kam postaviti to kodo?

Sledite spodnjim korakom za kopiranje te kode v urejevalniku VB.

• Pojdite na zavihek Razvijalec.
• Kliknite gumb Visual Basic. S tem se odpre urejevalnik VB.
• V urejevalniku VB z desno tipko miške kliknite kateri koli predmet delovnega zvezka, v katerem delate, pojdite na Vstavi in ​​kliknite na Modul. Če ne vidite Project Explorerja, uporabite bližnjico na tipkovnici Control + R (držite tipko za upravljanje in pritisnite tipko 'R').
• Dvokliknite objekt Module in zgornjo kodo kopirajte in prilepite v okno kode modula.

Kako uporabljati to funkcijo?

Spodaj so navedeni koraki za uporabo te funkcije na delovnem listu:

• V katero koli celico vnesite naslov mape, iz katere želite navesti imena datotek. To sem vnesel v celico A1.
• V celico vnesite pripono (ali ključno besedo), za katero želite vsa imena datotek. To sem vnesel v celico B1.
• V celico, kjer želite seznam, vnesite naslednjo formulo (vnesem jo v celico A3):

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

• Kopirajte in prilepite formulo v spodnje celice, da dobite seznam vseh datotek.

Kaj pa ti? Vsi triki v Excelu, s katerimi olajšate življenje. Rad bi se učil od vas. Delite to v oddelku za komentarje!