Gaukite failų pavadinimų sąrašą iš „Excel“ aplanko (su ir be VBA)

Pirmą dieną dirbdama nedidelėje konsultacinėje firmoje tris dienas dirbau su trumpu projektu.

Darbas buvo paprastas.

Tinklo diske buvo daug aplankų ir kiekviename aplanke buvo šimtai failų.

Turėjau atlikti šiuos tris veiksmus:

1. Pasirinkite failą ir nukopijuokite jo pavadinimą.
2. Įklijuokite šį pavadinimą į „Excel“ langelį ir paspauskite „Enter“.
3. Pereikite prie kito failo ir pakartokite 1 ir 2 veiksmus.

Skamba paprastai, tiesa?

Tai buvo - paprasta ir didžiulis laiko švaistymas.

Tai, kas užtruko man tris dienas, galėjo būti padaryta per kelias minutes, jei žinojau tinkamas technikas.

Šioje pamokoje parodysiu įvairius būdus, kaip padaryti šį procesą itin greitą ir labai lengvą (su VBA ir be jo).

Šiame vadove parodytų metodų apribojimai: Naudodami toliau nurodytus metodus, galėsite gauti tik pagrindinio aplanko failų pavadinimus. Pagrindinio aplanko poaplankiuose esančių failų pavadinimų negausite. Čia yra būdas gauti failų pavadinimus iš aplankų ir poaplankių naudojant „Power Query“

Funkcijos FILES naudojimas failų pavadinimų sąrašui gauti iš aplanko

Girdėta FILES funkcija anksčiau?

Nesijaudinkite, jei to nepadarėte.

Tai iš vaikystės laikų „Excel“ skaičiuoklių (4 versijos formulė).

Nors ši formulė neveikia darbalapio langeliuose, ji vis tiek veikia pavadintuose diapazonuose. Šį faktą naudosime norėdami gauti failų pavadinimų sąrašą iš nurodyto aplanko.

Tarkime, kad turite aplanką pavadinimu - „Bandymo aplankas„Darbalaukyje ir norite gauti visų šio aplanko failų failų pavadinimų sąrašą.

Štai veiksmai, kurie suteiks failų pavadinimus iš šio aplanko:

1. A1 langelyje įveskite viso aplanko adresą ir po to žvaigždutės ženklą (*)
   • Pavyzdžiui, jei jūsų aplankas C diske, adresas atrodys taip
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Jei nesate tikri, kaip gauti aplanko adresą, naudokite šį metodą:
     • • Aplanke, iš kurio norite gauti failų pavadinimus, sukurkite naują „Excel“ darbaknygę arba atidarykite esamą darbaknygę aplanke ir naudokite toliau pateiktą formulę bet kuriame langelyje. Ši formulė suteiks jums aplanko adresą ir pabaigoje pridės žvaigždutės ženklą (*). Dabar galite nukopijuoti ir įklijuoti (įklijuoti kaip vertę) šį adresą bet kurioje darbaknygės, kurioje norite failų pavadinimų, langelyje (šiame pavyzdyje-A1).

         = REPLACE (CELL ("failo pavadinimas"), FIND ("[", CELL ("failo pavadinimas")), LEN (CELL ("failo pavadinimas")), "*")

         [Jei aplanke sukūrėte naują darbaknygę, kad galėtumėte naudoti aukščiau pateiktą formulę ir gauti aplanko adresą, galbūt norėsite ją ištrinti, kad ji nebūtų įtraukta į to aplanko failų sąrašą]
2. Eikite į skirtuką „Formulės“ ir spustelėkite parinktį „Apibrėžti vardą“.
3. Dialogo lange Naujas vardas naudokite šią informaciją
   • Pavadinimas: „FileNameList“ (nedvejodami pasirinkite norimą pavadinimą)
   • Taikymo sritis: darbo knyga
   • Nurodo: = FILES (Sheet1! $ A $ 1)
4. Dabar, norėdami gauti failų sąrašą, mes naudosime pavadintą diapazoną INDEX funkcijoje. Eikite į langelį A3 (arba bet kurį langelį, kuriame norite pradėti vardų sąrašą) ir įveskite šią formulę:

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

5. Vilkite žemyn ir pamatysite visų aplanke esančių failų pavadinimų sąrašą

Norite ištraukti failus naudodami konkretų plėtinį ??

Jei norite gauti visus failus su tam tikru plėtiniu, tiesiog pakeiskite žvaigždutę su tuo failo plėtiniu. Pavyzdžiui, jei norite tik „Excel“ failų, vietoj * galite naudoti * xls *

Taigi aplanko adresas, kurį jums reikia naudoti, būtų C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Panašiai, jei naudojate „Word“ dokumentų failus, naudokite *doc *

Kaip tai veikia?

FILES formulė nuskaito visų nurodyto plėtinio failų pavadinimus nurodytame aplanke.

INDEX formulėje kaip masyvą nurodėme failų pavadinimus ir grąžiname 1, 2, 3 failų pavadinimus ir pan., Naudodami funkciją ROW.

Atkreipkite dėmesį, kad aš naudojau EILUTĖ ()-2, nes pradėjome nuo trečios eilės. Taigi ROW ()-2 būtų 1 pirmajam egzemplioriui, 2 antrajam egzemplioriui, kai eilutės numeris yra 4 ir pan.

Žiūrėkite vaizdo įrašą - gaukite failų pavadinimų sąrašą iš „Excel“ aplanko

VBA naudojimas Gaukite visų failų pavadinimų sąrašą iš aplanko

Dabar turiu pasakyti, kad aukščiau pateiktas metodas yra šiek tiek sudėtingas (su keliais žingsniais).

Tačiau tai yra daug geriau nei tai padaryti rankiniu būdu.

Bet jei jums patogu naudotis VBA (arba jei gerai atliekate tikslius veiksmus, kuriuos išvardysiu žemiau), galite sukurti pasirinktinę funkciją (UDF), kuri gali lengvai suteikti jums visų failų pavadinimus.

Naudojimo nauda a User Definuotas FUnction (UDF) yra tai, kad galite išsaugoti funkciją asmeninėje makrokomandos darbaknygėje ir lengvai ją pakartotinai naudoti, nekartodami veiksmų vėl ir vėl. Taip pat galite sukurti priedą ir bendrinti šią funkciją su kitais.

Dabar leiskite man duoti jums VBA kodą, kuris sukurs funkciją gauti visų failų pavadinimų sąrašą iš „Excel“ aplanko.

Funkcija „GetFileNames“ („ByVal FolderPath“ kaip eilutė) „Variant Dim“ Rezultatas kaip variantas Dim i „Integer Dim DimFile“ kaip objektas „Dim“ „MyFSO“ kaip objektas „Dim MyFolder“ kaip objektas „Dim MyFiles“ kaip objekto rinkinys „MyFSO = CreateObject“ („Scripting.FileSystemObject“) Nustatykite „MyFolder = MyFSO“. GetFolder (FolderPath) Nustatykite MyFiles = MyFolder.Files ReDim Result (1 į MyFiles.Count) i = 1 Kiekvienam MyFile In MyFiles Rezultatas (i) = MyFile.Name i = i + 1 Kitas MyFile GetFileNames = Rezultato pabaigos funkcija

Aukščiau pateiktas kodas sukurs funkciją „GetFileNames“, kurią galima naudoti darbalapiuose (kaip ir įprastas funkcijas).

Kur dėti šį kodą?

Norėdami nukopijuoti šį kodą į VB redaktorių, atlikite toliau nurodytus veiksmus.

• Eikite į skirtuką Kūrėjas.
• Spustelėkite „Visual Basic“ mygtuką. Tai atidarys VB redaktorių.
• VB redaktoriuje dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės, kurioje dirbate, objektą, eikite į Įterpti ir spustelėkite Modulis. Jei nematote „Project Explorer“, naudokite spartųjį klavišą „Control + R“ (palaikykite nuspaudę valdymo klavišą ir paspauskite „R“ klavišą).
• Dukart spustelėkite Module objektą ir nukopijuokite bei įklijuokite aukščiau pateiktą kodą į modulio kodo langą.

Kaip naudotis šia funkcija?

Žemiau pateikiami šios funkcijos naudojimo darbalapyje veiksmai:

• Bet kuriame langelyje įveskite aplanko, iš kurio norite išvardyti failų pavadinimus, adreso adresą.
• Ląstelėje, kurioje norite sąrašo, įveskite šią formulę (aš ją įvedu į langelį A3):

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

• Nukopijuokite ir įklijuokite formulę į žemiau esančius langelius, kad gautumėte visų failų sąrašą.

Atminkite, kad įvedžiau aplanko vietą langelyje ir panaudojau tą langelį GetFileNames formulė. Taip pat galite koduoti aplanko adresą pagal formulę, kaip parodyta žemiau:

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

Aukščiau pateiktoje formulėje mes naudojome ROW ()-2 ir pradėjome nuo trečios eilės. Tai užtikrino, kad kopijuojant formulę į žemiau esančius langelius ji bus padidinta 1. Jei įvesite formulę pirmoje stulpelio eilutėje, galite tiesiog naudoti ROW ().

Kaip veikia ši formulė?

„GetFileNames“ formulė grąžina masyvą, kuriame yra visų aplanke esančių failų pavadinimai.

Funkcija INDEX naudojama išvardyti vieną failo pavadinimą kiekvienoje ląstelėje, pradedant nuo pirmojo.

Funkcija IFERROR naudojama tuščiai, o ne #REF! klaida, kuri rodoma, kai formulė nukopijuojama į langelį, bet nėra daugiau sąrašo failų pavadinimų.

VBA naudojimas Gaukite visų failų pavadinimų, turinčių tam tikrą plėtinį, sąrašą

Aukščiau pateikta formulė puikiai tinka, kai norite gauti visų failų pavadinimų sąrašą iš „Excel“ aplanko.

O kas, jei norite gauti tik vaizdo įrašų failų pavadinimus arba tik „Excel“ failus arba tik failų pavadinimus, kuriuose yra konkretus raktinis žodis.

Tokiu atveju galite naudoti šiek tiek kitokią funkciją.

Žemiau yra kodas, leidžiantis gauti visus failų pavadinimus su konkrečiu raktiniu žodžiu (arba konkrečiu plėtiniu).

Funkcija GetFileNamesbyExt (ByVal FolderPath kaip eilutė, FileExt kaip eilutė) Kaip variantas Dim Rezultatas kaip variantas Dim kaip sveikasis dydis MyFile kaip objektas Dim MyFSO kaip objektas Dim MyFolder kaip objektas Dim MyFiles kaip objektų rinkinys MyFSO = CreateObject ("Scripting.FileSystemObject") MyFolder = MyFSO.GetFolder (FolderPath) Nustatykite MyFiles = MyFolder.Files ReDim Result (1 į MyFiles.Count) i = 1 Kiekvienam MyFile aplanke MyFiles If InStr (1, MyFile.Name, FileExt) 0 Tada Result (i) = MyFile .Pavadinimas i = i + 1 pabaiga, jei kitas MyFile ReDim išsaugo rezultatą (nuo 1 iki i - 1) GetFileNamesbyExt = Rezultato pabaigos funkcija

Aukščiau pateiktas kodas sukurs funkciją „„GetFileNamesbyExt“„Kurie gali būti naudojami darbalapiuose (kaip ir įprastos funkcijos).

Ši funkcija turi du argumentus - aplanko vietą ir plėtinio raktinį žodį. Tai grąžina failų pavadinimų masyvą, atitinkantį nurodytą plėtinį. Jei nenurodytas joks plėtinys ar raktinis žodis, jis grąžins visus nurodyto aplanko failų pavadinimus.

Sintaksė: = GetFileNamesbyExt („Aplanko vieta“, „Plėtinys“)

Kur dėti šį kodą?

Norėdami nukopijuoti šį kodą į VB redaktorių, atlikite toliau nurodytus veiksmus.

• Eikite į skirtuką Kūrėjas.
• Spustelėkite „Visual Basic“ mygtuką. Tai atidarys VB redaktorių.
• VB redaktoriuje dešiniuoju pelės mygtuku spustelėkite bet kurį darbaknygės, kurioje dirbate, objektą, eikite į Įterpti ir spustelėkite Modulis. Jei nematote „Project Explorer“, naudokite spartųjį klavišą „Control + R“ (laikykite nuspaudę valdymo klavišą ir paspauskite „R“ klavišą).
• Dukart spustelėkite Module objektą ir nukopijuokite bei įklijuokite aukščiau pateiktą kodą į modulio kodo langą.

Kaip naudotis šia funkcija?

Žemiau pateikiami šios funkcijos naudojimo darbalapyje veiksmai:

• Bet kuriame langelyje įveskite aplanko, iš kurio norite išvardyti failų pavadinimus, adreso adresą. Aš tai įvedžiau į langelį A1.
• Langelyje įveskite plėtinį (arba raktinį žodį), kuriam norite nurodyti visus failų pavadinimus. Aš tai įvedžiau į langelį B1.
• Langelyje, kuriame norite sąrašo, įveskite šią formulę (aš ją įvedu į langelį A3):

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

• Nukopijuokite ir įklijuokite formulę į žemiau esančius langelius, kad gautumėte visų failų sąrašą.

O kaip tau? Bet kokie „Excel“ triukai, kuriuos naudojate norėdami palengvinti gyvenimą. Norėčiau iš jūsų pasimokyti. Pasidalinkite ja komentarų skiltyje!