Obțineți lista numelor de fișiere dintr-un folder în Excel (cu și fără VBA)

În prima mea zi de serviciu într-o mică firmă de consultanță, am fost angajat într-un proiect scurt timp de trei zile.

Lucrarea a fost simplă.

Pe unitatea de rețea erau multe foldere și fiecare folder conținea sute de fișiere.

A trebuit să urmez acești trei pași:

1. Selectați fișierul și copiați numele acestuia.
2. Lipiți acest nume într-o celulă din Excel și apăsați Enter.
3. Treceți la următorul fișier și repetați pașii 1 și 2.

Sună simplu nu?

A fost - Simplu și o mare pierdere de timp.

Ceea ce mi-a luat trei zile s-ar fi putut face în câteva minute dacă știam tehnicile potrivite.

În acest tutorial, vă voi arăta diferite moduri de a face acest întreg proces foarte rapid și foarte ușor (cu și fără VBA).

Limitările metodelor prezentate în acest tutorial: Cu tehnicile prezentate mai jos, veți putea obține doar numele fișierelor din folderul principal. Nu veți primi numele fișierelor din subdosarele din folderul principal. Iată o modalitate de a obține nume de fișiere din foldere și sub-foldere utilizând Power Query

Utilizarea funcției FILES pentru a obține o listă de nume de fișiere dintr-un folder

A auzit de Funcția FILES inainte de?

Nu vă faceți griji dacă nu ați făcut-o.

Este din zilele copilăriei foilor de calcul Excel (o formulă versiunea 4).

Deși această formulă nu funcționează în celulele foii de lucru, funcționează în continuare în intervale denumite. Vom folosi acest fapt pentru a obține lista numelor de fișiere dintr-un folder specificat.

Acum, să presupunem că aveți un folder cu numele - „Folder de testare„Pe desktop și doriți să obțineți o listă de nume de fișiere pentru toate fișierele din acest folder.

Iată pașii care vă vor oferi numele fișierelor din acest folder:

1. În celula A1, introduceți adresa completă a folderului urmată de un semn cu asterisc (*)
   • De exemplu, dacă folderul dvs. din unitatea C, atunci adresa ar arăta ca.
     C: \ Users \ Sumit \ Desktop \ Test Folder \ *
   • Dacă nu sunteți sigur cum să obțineți adresa folderului, utilizați următoarea metodă:
     • • În dosarul din care doriți să obțineți numele fișierelor, fie creați un nou registru de lucru Excel, fie deschideți un registru de lucru existent în dosar și utilizați formula de mai jos în orice celulă. Această formulă vă va oferi adresa folderului și va adăuga un semn de asterisc (*) la final. Acum puteți copia-lipiți (lipiți ca valoare) această adresă în orice celulă (A1 în acest exemplu) din registrul de lucru în care doriți numele fișierelor.

         = REPLACE (CELL ("fișier"), FIND ("[", CELL ("fișier")), LEN (CELL ("fișier")), "*")

         [Dacă ați creat un nou registru de lucru în folder pentru a utiliza formula de mai sus și pentru a obține adresa folderului, vă recomandăm să îl ștergeți astfel încât să nu apară în lista de fișiere din acel folder]
2. Accesați fila „Formule” și faceți clic pe opțiunea „Definiți numele”.
3. În caseta de dialog Nume nou, utilizați următoarele detalii
   • Nume: FileNameList (nu ezitați să alegeți orice nume doriți)
   • Domeniu: Caiet de lucru
   • Se referă la: = FIȘIERE (Foaie1! $ A $ 1 $)
4. Acum, pentru a obține lista de fișiere, vom folosi intervalul numit într-o funcție INDEX. Mergeți la celula A3 (sau la orice celulă în care doriți să înceapă lista de nume) și introduceți următoarea formulă:

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

5. Trageți în jos și vă va oferi o listă cu toate numele fișierelor din folder

Doriți să extrageți fișiere cu o extensie specifică ??

Dacă doriți să obțineți toate fișierele cu o anumită extensie, schimbați doar asteriscul cu acea extensie de fișier. De exemplu, dacă doriți doar fișiere Excel, puteți utiliza * xls * în loc de *

Deci adresa folderului pe care trebuie să o utilizați ar fi C: \ Users \ Sumit \ Desktop \ Test Folder \ * xls *

În mod similar, pentru fișierele de documente Word, utilizați * doc *

Cum funcționează asta?

Formula FILES preia numele tuturor fișierelor extensiei specificate în folderul specificat.

În formula INDEX, am dat numele fișierelor ca matrice și returnăm numele de fișiere 1, 2, 3 și așa mai departe folosind funcția ROW.

Rețineți că am folosit ROW () - 2, așa cum am început de la al treilea rând înainte. Deci ROW () - 2 ar fi 1 pentru prima instanță, 2 pentru a doua instanță când numărul rândului este 4 și așa mai departe și așa mai departe.

Vizionați videoclipuri - Obțineți lista de nume de fișiere dintr-un dosar în Excel

Utilizarea VBA Obțineți o listă cu toate numele fișierelor dintr-un folder

Acum, trebuie să spun că metoda de mai sus este puțin complexă (cu mai mulți pași).

Cu toate acestea, este mult mai bine decât să faci asta manual.

Dar dacă sunteți confortabil cu utilizarea VBA (sau dacă sunteți priceput să urmați pașii exacți pe care îi voi lista mai jos), puteți crea o funcție personalizată (UDF) care vă poate ajuta să obțineți cu ușurință numele tuturor fișierelor.

Avantajul utilizării unui User Ddefinit Function (UDF) este că puteți salva funcția într-un registru personal de lucru macro și o puteți refolosi cu ușurință fără a repeta pașii din nou și din nou. De asemenea, puteți crea un program de completare și puteți partaja această funcție cu alții.

Acum permiteți-mi să vă dau mai întâi codul VBA care va crea o funcție pentru a obține lista tuturor numelor de fișiere dintr-un folder din Excel.

Funcția GetFileNames (ByVal FolderPath As String) As Variant Dim Result As Variant Dim i 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 Pentru fiecare MyFile în MyFiles Result (i) = MyFile.Name i = i + 1 Următorul MyFile GetFileNames = Funcția de finalizare a rezultatului

Codul de mai sus va crea o funcție GetFileNames care poate fi utilizată în foile de lucru (la fel ca funcțiile obișnuite).

Unde se pune acest cod?

Urmați pașii de mai jos pentru a copia acest cod în Editorul VB.

• Accesați fila Dezvoltator.
• Faceți clic pe butonul Visual Basic. Aceasta va deschide Editorul VB.
• În Editorul VB, faceți clic dreapta pe oricare dintre obiectele din registrul de lucru în care lucrați, accesați Insert și faceți clic pe Module. Dacă nu vedeți Project Explorer, utilizați comanda rapidă de la tastatură Control + R (țineți apăsată tasta de control și apăsați tasta „R”).
• Faceți dublu clic pe obiectul Module și copiați și lipiți codul de mai sus în fereastra codului modulului.

Cum se folosește această funcție?

Mai jos sunt pașii pentru a utiliza această funcție într-o foaie de lucru:

• În orice celulă, introduceți adresa folderului din folderul din care doriți să enumerați numele fișierelor.
• În celula în care doriți lista, introduceți următoarea formulă (o introduc în celula A3):

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

• Copiați și lipiți formula în celulele de mai jos pentru a obține o listă cu toate fișierele.

Rețineți că am introdus locația folderului într-o celulă și apoi am folosit acea celulă în GetFileNames formulă. Puteți, de asemenea, să codificați adresa folderului în formula așa cum se arată mai jos:

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

În formula de mai sus, am folosit ROW () - 2 și am început de la al treilea rând înainte. Acest lucru s-a asigurat că, pe măsură ce copiez formula în celulele de mai jos, va crește cu 1. În cazul în care introduceți formula în primul rând al unei coloane, puteți utiliza pur și simplu ROW ().

Cum funcționează această formulă?

Formula GetFileNames returnează o matrice care conține numele tuturor fișierelor din folder.

Funcția INDEX este utilizată pentru a lista un nume de fișier pe celulă, începând de la prima.

Funcția IFERROR este utilizată pentru a reveni necompletat în loc de #REF! eroare care este afișată atunci când o formulă este copiată într-o celulă, dar nu mai există nume de fișiere de listat.

Utilizarea VBA Obțineți o listă cu toate numele fișierelor cu o extensie specifică

Formula de mai sus funcționează excelent atunci când doriți să obțineți o listă cu toate numele fișierelor dintr-un folder în Excel.

Dar ce se întâmplă dacă doriți să obțineți numai numele fișierelor video sau numai ale fișierelor Excel sau numai numele fișierelor care conțin un anumit cuvânt cheie.

În acest caz, puteți utiliza o funcție ușor diferită.

Mai jos este codul care vă va permite să obțineți toate numele fișierelor cu un anumit cuvânt cheie (sau al unei extensii specifice).

Funcția 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 Pentru fiecare MyFile din MyFiles Dacă InStr (1, MyFile.Name, FileExt) 0 Apoi Rezultat (i) = MyFile .Nume i = i + 1 Sfârșit dacă următorul MyFile ReDim păstrează rezultatul (de la 1 la i - 1) GetFileNamesbyExt = Funcția de finalizare a rezultatului

Codul de mai sus va crea o funcție „GetFileNamesbyExt‘Care poate fi folosit în foile de lucru (la fel ca funcțiile obișnuite).

Această funcție acceptă două argumente - locația folderului și cuvântul cheie extensie. Returnează o serie de nume de fișiere care se potrivesc cu extensia dată. Dacă nu este specificată nicio extensie sau cuvânt cheie, acesta va returna toate numele fișierelor din folderul specificat.

Sintaxă: = GetFileNamesbyExt („Locația folderului”, „Extensie”)

Unde se pune acest cod?

Urmați pașii de mai jos pentru a copia acest cod în Editorul VB.

• Accesați fila Dezvoltator.
• Faceți clic pe butonul Visual Basic. Aceasta va deschide Editorul VB.
• În Editorul VB, faceți clic dreapta pe oricare dintre obiectele din registrul de lucru în care lucrați, accesați Insert și faceți clic pe Module. Dacă nu vedeți Project Explorer, utilizați comanda rapidă de la tastatură Control + R (țineți apăsată tasta de control și apăsați tasta „R”).
• Faceți dublu clic pe obiectul Module și copiați și lipiți codul de mai sus în fereastra codului modulului.

Cum se folosește această funcție?

Mai jos sunt pașii pentru a utiliza această funcție într-o foaie de lucru:

• În orice celulă, introduceți adresa folderului din folderul din care doriți să enumerați numele fișierelor. Am introdus acest lucru în celula A1.
• Într-o celulă, introduceți extensia (sau cuvântul cheie), pentru care doriți toate numele fișierelor. Am introdus acest lucru în celula B1.
• În celula în care doriți lista, introduceți următoarea formulă (o introduc în celula A3):

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

• Copiați și lipiți formula în celulele de mai jos pentru a obține o listă cu toate fișierele.

Tu ce mai faci? Orice trucuri Excel pe care le folosiți pentru a ușura viața. Mi-ar plăcea să învăț de la tine. Distribuiți-l în secțiunea de comentarii!