Hankige Exceli kaustast failinimede loend (koos VBA -ga ja ilma)

Esimesel tööpäeval väikeses konsultatsioonifirmas töötasin kolm päeva lühikese projektiga.

Töö oli lihtne.

Võrgudraivil oli palju kaustu ja igas kaustas oli sadu faile.

Pidin järgima neid kolme sammu:

1. Valige fail ja kopeerige selle nimi.
2. Kleepige see nimi Exceli lahtrisse ja vajutage sisestusklahvi.
3. Liikuge järgmise faili juurde ja korrake samme 1 ja 2.

Kõlab lihtsalt eks?

See oli - Lihtne ja tohutu ajaraisk.

See, mis võttis mul kolm päeva, oleks võinud teha mõne minutiga, kui oleksin teadnud õigeid tehnikaid.

Selles õpetuses näitan teile erinevaid viise, kuidas muuta see kogu protsess ülikiireks ja ülilihtsaks (koos VBA -ga ja ilma).

Selles juhendis näidatud meetodite piirangud: Allpool näidatud tehnikate abil saate hankida ainult põhikausta failide nimed. Te ei saa põhikausta alamkaustades olevate failide nimesid. Siin on võimalus hankida Power Query abil failide nimed kaustadest ja alamkaustadest

Funktsiooni FILES kasutamine failinimede loendi hankimiseks kaustast

Kuulnud FILES funktsioon enne?

Ärge muretsege, kui te pole seda teinud.

See pärineb Exceli arvutustabelite lapsepõlvest (versioon 4 valem).

Kuigi see valem ei tööta töölehe lahtrites, töötab see siiski nimega vahemikes. Kasutame seda asjaolu failinimede loendi hankimiseks määratud kaustast.

Oletame, et teil on kaust nimega - "Testkaust"Töölaual ja soovite saada kõigi selles kaustas olevate failide failinimede loendi.

Siin on sammud, mis annavad teile selle kausta failinimed:

1. Lahtrisse A1 sisestage kausta täielik aadress, millele järgneb tärn (*)
   • Näiteks kui teie kaust C -draivis näeb aadress välja selline
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Kui te pole kindel, kuidas kausta aadressi saada, kasutage järgmist meetodit.
     • • Kaustas, kust soovite failinimesid hankida, looge kas uus Exceli töövihik või avage kaustas olemasolev töövihik ja kasutage mis tahes lahtris allolevat valemit. See valem annab teile kausta aadressi ja lisab lõppu tärnimärgi (*). Nüüd saate selle aadressi kopeerida ja kleepida (kleepida väärtusena) igasse töövihiku lahtrisse (selles näites A1), kuhu soovite failinimed.

         = REPLACE (CELL ("failinimi"), FIND ("[", CELL ("failinimi")), LEN (CELL ("failinimi")), "*")

         [Kui olete loonud kausta uue töövihiku, et kasutada ülaltoodud valemit ja saada kausta aadress, võiksite selle kustutada, nii et see ei kajastuks selle kausta failide loendis]
2. Minge vahekaardile „Valemid” ja klõpsake suvandil „Määra nimi”.
3. Kasutage dialoogiboksis Uus nimi järgmisi üksikasju
   • Nimi: FileNameList (võite vabalt valida mis tahes nime, mis teile meeldib)
   • Reguleerimisala: töövihik
   • Viitab: = FILES (Sheet1! $ A $ 1)
4. Failide loendi saamiseks kasutame INDEX -funktsioonis nimetatud vahemikku. Minge lahtrisse A3 (või mis tahes lahtrisse, kus soovite nimede loendit alustada) ja sisestage järgmine valem:

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

5. Lohistage see alla ja see annab teile kõigi kaustas olevate failinimede loendi

Kas soovite konkreetse laiendiga faile ekstraktida ??

Kui soovite saada kõik failid kindla laiendiga, muutke lihtsalt selle faililaiendiga tärni. Näiteks kui soovite ainult Exceli faile, saate * asemel kasutada * xls *

Nii et kausta aadress, mida peate kasutama, oleks C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Samamoodi kasutage Wordi dokumendifailide jaoks *doc *

Kuidas see töötab?

FILES valem otsib määratud kausta kõigi määratud laiendiga failide nimed.

Valemis INDEX oleme andnud massiivina failinimed ja tagastame funktsiooni ROW abil 1., 2., 3. failinime ja nii edasi.

Pange tähele, et olen kasutanud RIDA ()-2, kuna alustasime kolmandast reast peale. Nii et RIDA ()-2 oleks esimese astme puhul 1, teise astme puhul 2, kui rea number on 4 jne, jne.

Vaadake videot - hankige Exceli kaustast failinimede loend

VBA kasutamine Hankige kaustast kõigi failinimede loend

Nüüd pean ütlema, et ülaltoodud meetod on natuke keeruline (koos mitme sammuga).

See on siiski palju parem kui käsitsi seda teha.

Kuid kui teile meeldib VBA kasutamine (või kui oskate järgida täpseid samme, mida ma allpool loetlen), saate luua kohandatud funktsiooni (UDF), mis võimaldab teil hõlpsalt saada kõigi failide nimed.

Kasutamise eelis a User Defined FUnction (UDF) on see, et saate funktsiooni salvestada isiklikku makro töövihikusse ja seda hõlpsalt uuesti kasutada, kordamata samme. Samuti saate luua lisandmooduli ja jagada seda funktsiooni teistega.

Nüüd lubage mul kõigepealt anda teile VBA -kood, mis loob funktsiooni kõigi failinimede loendi hankimiseks Exceli kaustast.

Funktsioon GetFileNames (ByVal FolderPath kui string) Variandi Dim Tulemus Variant Dim i Täisarv Dim MyFile objektina Dim MyFSO objektina Dim MyFolder objektina Dim MyFiles objektina Set MyFSO = CreateObject ("Scripting.FileSystemObject") Määra MyFolder = MyFSO. GetFolder (FolderPath) Määrake MyFiles = MyFolder.Files ReDim Result (1 To MyFiles.Count) i = 1 Iga MyFile'i kohta MyFiles Tulemus (i) = MyFile.Name i = i + 1 Järgmine MyFile GetFileNames = Tulemuse lõppfunktsioon

Ülaltoodud kood loob funktsiooni GetFileNames, mida saab töölehtedel kasutada (nagu tavalisi funktsioone).

Kuhu see kood panna?

Selle koodi kopeerimiseks VB redaktoris järgige alltoodud samme.

• Minge vahekaardile Arendaja.
• Klõpsake nuppu Visual Basic. See avab VB redaktori.
• VB-redaktoris paremklõpsake töövihiku mis tahes objektil, millega töötate, minge jaotisse Sisesta ja klõpsake Moodul. Kui te Project Explorerit ei näe, kasutage kiirklahvi Control + R (hoidke all juhtklahvi ja vajutage klahvi „R”).
• Topeltklõpsake Module objekti ning kopeerige ja kleepige ülaltoodud kood mooduli koodi aknasse.

Kuidas seda funktsiooni kasutada?

Selle funktsiooni töölehel kasutamiseks toimige järgmiselt.

• Sisestage suvalisse lahtrisse kausta aadress, kust soovite failinimed loetleda.
• Lahtrisse, kuhu soovite loendi sisestada, sisestage järgmine valem (sisestan selle lahtrisse A3):

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

• Kõigi failide loendi saamiseks kopeerige ja kleepige valem allolevatesse lahtritesse.

Pange tähele, et sisestasin lahtrisse kausta asukoha ja kasutasin seda lahtris GetFileNames valem. Samuti saate kausta aadressi kõvakodeerida valemis, nagu allpool näidatud:

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

Ülaltoodud valemis oleme kasutanud ROW ()-2 ja alustasime kolmandast reast. See kindlustas, et kui valemit allolevatesse lahtritesse kopeerin, suureneb see 1 võrra. Kui sisestate valemi veeru esimesse ritta, saate lihtsalt kasutada ROW ().

Kuidas see valem töötab?

Valem GetFileNames tagastab massiivi, mis sisaldab kõigi kaustas olevate failide nimesid.

Funktsiooni INDEX kasutatakse ühe failinime loetlemiseks lahtri kohta, alustades esimesest.

Funktsiooni IFERROR kasutatakse tühiku tagastamiseks #REF! viga, mis kuvatakse valemi lahtrisse kopeerimisel, kuid loetlemiseks pole enam failinimesid.

VBA kasutamine Hankige nimekiri kõigist kindla laiendiga failinimedest

Ülaltoodud valem töötab suurepäraselt, kui soovite saada Exceli kaustast kõigi failinimede loendi.

Aga mis siis, kui soovite hankida ainult videofailide nimed või ainult Exceli failid või ainult teatud märksõna sisaldavad failinimed.

Sel juhul saate kasutada veidi erinevat funktsiooni.

Allpool on kood, mis võimaldab teil hankida kõik failinimed koos kindla märksõnaga (või konkreetse laiendiga).

Funktsioon GetFileNamesbyExt (ByVal FolderPath kui string, FileExt kui string) Variandi Dim tulemus Variandina Dim i Täisarv Dim MyFile objektina Dim MyFSO objektina Dim MyFolder As Objekt Dim MyFiles objektikomplektina MyFSO = CreateObject ("Scripting.FileSystemObject") MyFolder = MyFSO.GetFolder (FolderPath) Määra MyFiles = MyFolder.Files ReDim Result (1 To MyFiles.Count) i = 1 Iga MyFile'i kohta minu failides If InStr (1, MyFile.Name, FileExt) 0 Siis Tulemus (i) = MyFile .Nimi i = i + 1 Lõpp, kui järgmine MyFile ReDim säilitab tulemuse (1 kuni i - 1) GetFileNamesbyExt = Tulemuse lõppfunktsioon

Ülaltoodud kood loob funktsiooni "GetFileNamesbyExt„Mida saab töölehtedel kasutada (nagu tavalisi funktsioone).

Sellel funktsioonil on kaks argumenti - kausta asukoht ja laiendi märksõna. See tagastab massiivi failinimesid, mis vastavad antud laiendile. Kui ühtegi laiendit või märksõna pole määratud, tagastab see kõik määratud kausta failinimed.

Süntaks: = GetFileNamesbyExt ("Kausta asukoht", "Laiend")

Kuhu see kood panna?

Selle koodi kopeerimiseks VB redaktoris järgige alltoodud samme.

• Minge vahekaardile Arendaja.
• Klõpsake nuppu Visual Basic. See avab VB redaktori.
• VB-redaktoris paremklõpsake töövihiku mis tahes objektil, milles te töötate, minge Lisa ja klõpsake moodulil. Kui te Project Explorerit ei näe, kasutage kiirklahvi Control + R (hoidke all juhtklahvi ja vajutage klahvi „R”).
• Topeltklõpsake Module objekti ning kopeerige ja kleepige ülaltoodud kood mooduli koodi aknasse.

Kuidas seda funktsiooni kasutada?

Selle funktsiooni töölehel kasutamiseks toimige järgmiselt.

• Sisestage suvalisse lahtrisse kausta aadress, kust soovite failinimed loetleda. Olen selle sisestanud lahtrisse A1.
• Sisestage lahtrisse laiend (või märksõna), mille kõik failinimed soovite. Olen selle sisestanud lahtrisse B1.
• Lahtrisse, kuhu soovite loendi sisestada, sisestage järgmine valem (sisestan selle lahtrisse A3):

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

• Kõigi failide loendi saamiseks kopeerige ja kleepige valem allolevatesse lahtritesse.

Kuidas sinul? Kõik Exceli nipid, mida kasutate elu lihtsustamiseks. Mulle meeldiks sinult õppida. Jagage seda kommentaaride jaotises!