Pridobite seznam imen datotek iz mape z uporabo Excela (z in brez VBA)

Prvi dan v majhnem svetovalnem podjetju so mi dodelili tridnevni kratkoročni projekt.Delo je preprosto.

Na omrežnem pogonu je veliko map, v vsaki je na stotine datotek.

Moral sem slediti tem trem korakom:

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

Sliši se preprosto, kajne?

Je preprosto in zelo zamudno.

Kar sem naredil v treh dneh, lahko naredim v nekaj minutah, če poznam pravo tehniko.

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

Omejitve metod, prikazanih v tej vadnici:S tehniko, prikazano spodaj, boste lahko dobili samo imena datotek v domači mapi.Ne dobite imen datotek v podmapah v glavni mapi.Tukaj je način za pridobivanje imen datotek iz map in podmap z uporabo Power Query

Uporabite funkcijo FILES, da dobite seznam imen datotek iz mape

slišal o temFunkcija FILES?

Če ne, ne skrbi.

To je iz Excelove preglednice iz otroštva (formule različice 4).

Čeprav ta formula ne deluje s celicami delovnega lista, še vedno deluje z imenovanimi obsegi.To dejstvo bomo uporabili za pridobitev seznama imen datotek iz navedene mape.

Zdaj pa recimo, da imate na namizju datoteko z imenom " Preskusna mapa « in želite dobiti seznam imen datotek za vse datoteke v tej mapi.

Tu so koraki, ki vam bodo dali imena datotek v tej mapi:

1. V celico A1 vnesite celoten naslov mape, ki mu sledi zvezdica (*)
   • Na primer, če je bila vaša mapa na pogonu C, bi bil naslov nekaj takega
     C:\Uporabniki\VAŠE IME\Namizje\Preskusna mapa\*
   • Če niste prepričani, kako pridobiti naslov mape, uporabite naslednjo metodo:
     • • V mapi, iz katere želite pridobiti ime datoteke, ustvarite nov delovni zvezek Excel ali odprite obstoječi delovni zvezek v mapi in v kateri koli celici uporabite naslednjo formulo.Ta formula vam bo dala naslov mape z zvezdico (*) na koncu.Zdaj lahko ta naslov kopirate in prilepite (prilepite kot vrednost) v katero koli celico v delovnem zvezku (v tem primeru A1), kjer želite ime datoteke.

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

         [Če ste ustvarili nov delovni zvezek v mapi, da uporabite zgornjo formulo in dobite naslov mape, ga boste morda morali izbrisati, da ne bo na seznamu datotek v tej mapi]

2. Pojdite na zavihek »Formule« in kliknite možnost »Določi imena«.
3. V pogovornem oknu Novo ime uporabite naslednje podrobnosti
   • Ime: FileNameList (izberite poljubno ime, ki vam je všeč)
   • Obseg: Delovni zvezek
   • Nanaša se na: =FILES(Sheet1!$A$1)
4. Zdaj, da bi dobili seznam datotek, bomo v funkciji INDEX uporabili imenovani obseg.Pojdite na celico A3 (ali katero koli celico, s katero želite, da se seznam imen začne) in vnesite naslednjo formulo:

   =ČE NAPAKA(INDEX(seznam imen datoteke,vrstica()-2),"")

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

Želite ekstrahirati datoteke z določeno pripono? ?

Če želite dobiti vse datoteke z določeno končnico, samo spremenite zvezdico s to končnico datoteke.Na primer, če želite samo datoteko excel, lahko uporabite *xls* namesto *

Torej je naslov mape, ki jo morate uporabitiC:UsersSumitDesktopTest Map*xls*

Podobno za datoteke Wordovih dokumentov uporabite *doc*

Kako to deluje?

Formula FILES pridobi imena vseh datotek z določeno končnico v navedeni mapi.

V formuli INDEX podamo imena datotek kot matriko in uporabimo funkcijo ROW za vrnitev prvega, drugega, tretjega itd. imen datotek.

Upoštevajte, da sem uporabilVRSTICA()-2, saj začnemo v tretji vrstici.Torej, ko je številka vrstice 4, bo ROW()-2 1 za prvi primer, 2 za drugi in tako naprej.

Pridobite seznam vseh imen datotek iz mape z uporabo VBA

Zdaj moram reči, da je zgornja metoda nekoliko zapletena (obstaja veliko korakov).

Vendar je veliko bolje, kot da bi to naredili ročno.

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

Prednosti uporabe uporabniško definiranih funkcij (UDF)是TiLahko將funkcijoShranite v osebne delovne zvezke makrov in jih enostavno znova uporabite, ne da bi morali te korake ponavljati znova in znova.Ustvarite lahko tudi dodatke in to funkcijo delite z drugimi.

Zdaj naj vam najprej dam kodo VBA, ki bo ustvarila funkcijo za pridobitev seznama vseh imen datotek iz mape v Excelu.

Funkcija GetFileNames(ByVal FolderPath kot niz) Kot Variant Dim Result Kot Variant Dim i Kot Integer Dim MyFile Kot Object Dim MyFSO Kot Object Dim MyFolder Kot Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemFolder") Set MySOObject.F GetFolder(FolderPath) Nastavi MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Za vsako MyFile In MyFiles Result(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 delovnem listu (tako kot navadna funkcija).

Kam postaviti to kodo?

Sledite spodnjim korakom, da kopirate to kodo v urejevalnik VB.

Kako uporabljati to funkcijo?

Tu so koraki za uporabo te funkcije na delovnem listu:

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

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

• Kopirajte in prilepite formulo v spodnjo celico, da dobite seznam vseh datotek.

Upoštevajte, da sem lokacijo mape vnesel v eno celico in nato vGetFileNamesTa celica se uporablja v formuli.Naslov mape v formuli lahko tudi trdo kodirate takole:

=IFERROR(INDEX(GetFileNames("C:\Users\VAŠE IME\Namizje\Preskusna mapa"),ROW()-2),"")

V zgornji formuli smo uporabili ROW()-2 in začnemo s tretjo vrstico.To zagotavlja, da se bo, ko kopiram formulo v celico spodaj, povečala za 1.Če vnesete formulo v prvo vrstico stolpca, lahko preprosto uporabite ROW().

Kako deluje ta formula?

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

Funkcija INDEX se uporablja za seznam enega imena datoteke na celico, začenši s prvim.

Funkcija IFERROR se uporablja za vrnitev praznega namesto #REF!Napaka se prikaže, ko je formula kopirana v celico, vendar ni več imen datotek za seznam.

Pridobite seznam vseh imen datotek z določeno pripono z uporabo VBA

Zgornja formula je uporabna, če želite dobiti seznam vseh imen datotek iz mape v Excelu.

Kaj pa, če želite dobiti samo ime video datoteke ali samo Excelove datoteke ali samo datoteke, ki vsebuje določeno ključno besedo.

V tem primeru lahko uporabite nekoliko drugačno funkcijo.

Spodnja koda vam bo omogočila, da dobite vsa imena datotek, ki imajo v sebi določeno ključno besedo (ali končnico).

Funkcija GetFileNamesbyExt(ByVal FolderPath kot niz, FileExt kot niz) Kot Variant Dim Result Kot Variant Dim i As Integer Dim MyFile Kot Object Dim MyFSO Kot Object Dim MyFolder Kot Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripte Set)" MyFolder = MyFSO.GetFolder(FolderPath) Nastavite MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Za vsako MyFile v MyFiles Če InStr(1, MyFile.Name, FileExt) <> 0 Nato Result(i) = MyFile.Name i = i + 1 End, če je naslednji MyFile ReDim Ohrani rezultat (1 do i - 1) GetFileNamesbyExt = Končna funkcija rezultata

Zgornja koda bo ustvarila funkcijo, ki se lahko uporablja na delovnem listu" GetFileNamesbyExt " (tako kot običajne funkcije).

Ta funkcija potrebuje dva parametra - lokacijo mape in ključno besedo razširitve.Vrne niz imen datotek, ki se ujemajo z dano pripono.Če ni podana nobena pripona ali ključna beseda, bo vrnil vsa imena datotek v navedeni mapi.

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

Kam postaviti to kodo?

Sledite spodnjim korakom, da kopirate to kodo v urejevalnik VB.

• Pojdite na zavihek za razvijalce.
• Kliknite gumb Visual Basic.To bo odprlo urejevalnik VB.
• V urejevalniku VB z desno tipko miške kliknite kateri koli predmet v delovnem zvezku, na katerem delate, pojdite na Vstavi in ​​kliknite Modul.Če ne vidite Project Explorerja, uporabite bližnjico na tipkovnici Control + R (pridržite tipko in pritisnite "R").
• Dvokliknite predmet modula ter kopirajte in prilepite zgornjo kodo v okno kode modula.

Kako uporabljati to funkcijo?

Tu so koraki za uporabo te funkcije na delovnem listu:

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

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

• Kopirajte in prilepite formulo v spodnjo celico, da dobite seznam vseh datotek.

Kaj pa ti?Vsi Excelovi triki, ki jih uporabite za poenostavitev življenja.Rad bi se učil od vas.Delite ga v razdelku za komentarje!