Nabavite popis imena datoteka iz mape u Excelu (sa i bez VBA)

Prvog dana u poslu u maloj konzultantskoj tvrtki tri dana sam imala kratki projekt.

Posao je bio jednostavan.

Na mrežnom pogonu bilo je mnogo mapa i svaka je mapa imala stotine datoteka.

Morao sam slijediti ova tri koraka:

1. Odaberite datoteku i kopirajte njezin naziv.
2. Zalijepite to ime u ćeliju u Excelu i pritisnite Enter.
3. Prijeđite na sljedeću datoteku i ponovite korake 1 i 2.

Zvuči jednostavno zar ne?

Bilo je to - Jednostavno i veliki gubitak vremena.

Ono što mi je trebalo tri dana moglo se učiniti za nekoliko minuta da sam znao prave tehnike.

U ovom tutorialu pokazat ću vam različite načine kako cijeli ovaj proces učiniti super brzim i super lakim (sa i bez VBA).

Ograničenja metoda prikazanih u ovom vodiču: Pomoću dolje prikazanih tehnika moći ćete dobiti samo nazive datoteka unutar glavne mape. Nećete dobiti imena datoteka u podmapama unutar glavne mape. Evo načina za dobivanje naziva datoteka iz mapa i podmapa pomoću programa Power Query

Korištenje funkcije FILES za preuzimanje popisa naziva datoteka iz mape

Čuo za Funkcija FILES prije?

Ne brinite ako niste.

To je iz djetinjstva Excel tablica (formula verzije 4).

Iako ova formula ne radi u ćelijama radnog lista, još uvijek radi u imenovanim rasponima. Ovu ćemo činjenicu iskoristiti za dobivanje popisa naziva datoteka iz određene mape.

Pretpostavimo da sada imate mapu s imenom - 'Testna mapa‘Na radnoj površini i želite dobiti popis naziva datoteka za sve datoteke u ovoj mapi.

Evo koraka koji će vam dati nazive datoteka iz ove mape:

1. U ćeliju A1 unesite potpunu adresu mape iza koje slijedi zvjezdica (*)
   • Na primjer, ako je vaša mapa u pogonu C, adresa bi izgledala ovako
     C: \ Users \ Sumit \ Desktop \ Test Mapa \*
   • Ako niste sigurni kako doći do adrese mape, upotrijebite sljedeću metodu:
     • • U mapi iz koje želite dobiti nazive datoteka ili stvorite novu radnu knjigu programa Excel ili otvorite postojeću radnu knjigu u mapi i upotrijebite donju formulu u bilo kojoj ćeliji. Ova formula daje vam adresu mape i dodaje znak zvjezdice (*) na kraju. Sada možete kopirati-zalijepiti (zalijepiti kao vrijednost) ovu adresu u bilo koju ćeliju (A1 u ovom primjeru) u radnoj knjizi u kojoj želite nazive datoteka.

         = ZAMJENA (CELL ("naziv datoteke"), FIND ("[", CELL ("naziv datoteke")), LEN (CELL ("naziv datoteke")), "*")

         [Ako ste u mapi stvorili novu radnu knjigu kako biste koristili gornju formulu i dobili adresu mape, možda ćete je htjeti izbrisati tako da se ne nalazi na popisu datoteka u toj mapi]
2. Idite na karticu 'Formule' i kliknite opciju 'Definiraj naziv'.
3. U dijaloškom okviru Novi naziv upotrijebite sljedeće detalje
   • Ime: FileNameList (slobodno odaberite bilo koje ime koje želite)
   • Opseg: Radna bilježnica
   • Odnosi se na: = FILES (List 1! $ A $ 1)
4. Sada ćemo dobiti popis datoteka, koristit ćemo imenovani raspon unutar funkcije INDEX. Idite u ćeliju A3 (ili bilo koju ćeliju u kojoj želite da počne popis imena) i unesite sljedeću formulu:

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

5. Povucite ovo prema dolje i dobit ćete popis svih naziva datoteka u mapi

Želite li izdvojiti datoteke s određenim nastavkom ??

Ako želite dobiti sve datoteke s određenim nastavkom, samo promijenite zvjezdicu s tim nastavkom datoteke. Na primjer, ako želite samo Excel datoteke, možete koristiti * xls * umjesto *

Dakle, adresa mape koju trebate koristiti bila bi C: \ Users \ Sumit \ Desktop \ Test Mapa \*xls*

Slično, za datoteke Word dokumenata koristite *doc *

Kako ovo radi?

Formula FILES dohvaća imena svih datoteka navedenog proširenja u navedenoj mapi.

U formuli INDEX dali smo nazive datoteka kao niz i vraćamo 1., 2., 3. naziv datoteke i tako dalje pomoću funkcije ROW.

Imajte na umu da sam koristio RED ()-2, kako smo krenuli od trećeg reda nadalje. Dakle, ROW ()-2 bi bilo 1 za prvu instancu, 2 za drugu instancu kada je broj retka 4, i tako dalje, i tako dalje.

Gledajte video zapis - preuzmite popis imena datoteka iz mape u Excelu

Korištenje VBA Nabavite popis svih naziva datoteka iz mape

Moram reći da je gornja metoda pomalo složena (s nekoliko koraka).

To je, međutim, puno bolje nego to učiniti ručno.

No ako vam je ugodno koristiti VBA (ili ako dobro slijedite točne korake koje ću navesti u nastavku), možete stvoriti prilagođenu funkciju (UDF) koja vam može lako donijeti imena svih datoteka.

Prednost korištenja a User Ddefinirano ŽUnction (UDF) je da funkciju možete spremiti u osobnu radnu knjigu makronaredbi i jednostavno je ponovno koristiti bez ponavljanja koraka uvijek iznova. Također možete stvoriti dodatak i podijeliti ovu funkciju s drugima.

Dopustite mi prvo da vam dam VBA kôd koji će stvoriti funkciju za dobivanje popisa svih naziva datoteka iz mape u Excelu.

Funkcija GetFileNames (ByVal FolderPath As String) Kao Variant Dim Rezultat Kao Variant Dim i As Integer Dim MyFile As Object Dim MyFSO Kao Object Dim MyFolder Kao Objekt Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = GetFolder (FolderPath) Postavi MyFiles = MyFolder.Files Rezultat ReDim -a (1 do MyFiles.Count) i = 1 Za svaki MyFile u MyFiles Rezultat (i) = MyFile.Name i = i + 1 Sljedeća MyFile GetFileNames = Funkcija završetka rezultata

Gornji kôd će stvoriti funkciju GetFileNames koja se može koristiti na radnim listovima (baš kao i uobičajene funkcije).

Gdje staviti ovaj kod?

Slijedite donje korake za kopiranje ovog koda u VB Editor.

• Idite na karticu Developer.
• Pritisnite gumb Visual Basic. Ovo će otvoriti VB Editor.
• U VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u kojem radite, idite na Umetni i kliknite na Modul. Ako ne vidite Project Explorer, upotrijebite tipkovnički prečac Control + R (držite kontrolnu tipku i pritisnite tipku 'R').
• Dvaput kliknite objekt Module i kopirajte i zalijepite gornji kôd u prozor koda modula.

Kako koristiti ovu funkciju?

U nastavku su navedeni koraci za korištenje ove funkcije na radnom listu:

• U bilo koju ćeliju unesite adresu mape iz koje želite navesti nazive datoteka.
• U ćeliju u kojoj želite popis unesite sljedeću formulu (unosim je u ćeliju A3):

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

• Kopirajte i zalijepite formulu u donje ćelije da biste dobili popis svih datoteka.

Imajte na umu da sam unio mjesto mape u ćeliju, a zatim tu ćeliju upotrijebio u GetFileNames formula. Također možete teško kodirati adresu mape u formuli kao što je prikazano u nastavku:

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

U gornjoj formuli smo koristili ROW ()-2 i krenuli smo od trećeg reda nadalje. Time sam osigurao da dok kopiram formulu u donje ćelije, ona će se povećati za 1. U slučaju da formulu unosite u prvi redak stupca, jednostavno možete upotrijebiti ROW ().

Kako funkcionira ova formula?

Formula GetFileNames vraća niz koji sadrži imena svih datoteka u mapi.

Funkcija INDEX služi za popis jednog naziva datoteke po ćeliji, počevši od prve.

IFERROR funkcija se koristi za vraćanje praznog umjesto #REF! pogreška koja se prikazuje kada se formula kopira u ćeliju, ali nema više imena datoteka za popis.

Korištenje VBA Nabavite popis svih naziva datoteka s određenim proširenjem

Gornja formula odlično funkcionira kada želite dobiti popis svih naziva datoteka iz mape u Excelu.

Ali što ako želite dobiti imena samo video datoteka ili samo Excel datoteka ili samo imena datoteka koje sadrže određenu ključnu riječ.

U tom slučaju možete koristiti malo drugačiju funkciju.

Dolje se nalazi kôd koji će vam omogućiti da dobijete sve nazive datoteka s određenom ključnom riječi (ili određenom ekstenzijom).

Funkcija GetFileNamesbyExt (ByVal FolderPath kao niz, FileExt kao niz) Kao varijanta zatamnjenja Rezultat Kao varijanta Dim i Kao cijeli broj Dim MyFile Kao objekt Dim MyFSO Kao objekt Dim MyFolder Kao objekt Dim MyFiles as Object Set MyFSO = CreateObject ("Scripting.FileSystemObject" MyFolder = MyFSO.GetFolder (FolderPath) Postavi MyFiles = MyFolder.Files Rezultat ReDim (1 do MyFiles.Count) i = 1 Za svaki MyFile u MyFiles If InStr (1, MyFile.Name, FileExt) 0 Zatim Result (i) = MyFile .Ime i = i + 1 End If Next MyFile ReDim očuvanje rezultata (1 To i - 1) GetFileNamesbyExt = Funkcija završetka rezultata

Gornji kôd će stvoriti funkciju 'GetFileNamesbyExt'Koje se mogu koristiti u radnim listovima (baš kao i regularne funkcije).

Ova funkcija ima dva argumenta - lokaciju mape i ključnu riječ proširenja. Vraća niz naziva datoteka koji odgovaraju danom proširenju. Ako nije navedeno proširenje ili ključna riječ, vratit će se svi nazivi datoteka u navedenoj mapi.

Sintaksa: = GetFileNamesbyExt ("Lokacija mape", "Proširenje")

Gdje staviti ovaj kod?

Slijedite donje korake za kopiranje ovog koda u VB Editor.

• Idite na karticu Developer.
• Pritisnite gumb Visual Basic. Ovo će otvoriti VB Editor.
• U VB Editoru desnom tipkom miša kliknite bilo koji objekt radne knjige u kojem radite, idite na Umetni i kliknite na Modul. Ako ne vidite Project Explorer, upotrijebite tipkovnički prečac Control + R (držite kontrolnu tipku i pritisnite tipku 'R').
• Dvaput kliknite objekt Module i kopirajte i zalijepite gornji kôd u prozor koda modula.

Kako koristiti ovu funkciju?

U nastavku su navedeni koraci za korištenje ove funkcije na radnom listu:

• U bilo koju ćeliju unesite adresu mape iz koje želite navesti nazive datoteka. Upisao sam ovo u ćeliju A1.
• U ćeliju unesite proširenje (ili ključnu riječ) za koje želite sve nazive datoteka. Upisao sam ovo u ćeliju B1.
• U ćeliju u kojoj želite popis unesite sljedeću formulu (unosim je u ćeliju A3):

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

• Kopirajte i zalijepite formulu u donje ćelije da biste dobili popis svih datoteka.

A ti? Bilo koji Excel trikovi koje koristite za olakšavanje života. Volio bih učiti od vas. Podijelite ga u odjeljku komentara!