Iegūstiet failu nosaukumu sarakstu no mapes programmā Excel (ar un bez VBA)

Pirmajā dienā, strādājot nelielā konsultāciju firmā, trīs dienas strādāju pie īsa projekta.

Darbs bija vienkāršs.

Tīkla diskā bija daudz mapju, un katrā mapē bija simtiem failu.

Man bija jāveic šādi trīs soļi:

1. Atlasiet failu un nokopējiet tā nosaukumu.
2. Ielīmējiet šo nosaukumu šūnā programmā Excel un nospiediet taustiņu Enter.
3. Pārejiet uz nākamo failu un atkārtojiet 1. un 2. darbību.

Izklausās vienkārši, vai ne?

Tā bija - Vienkārša un milzīga laika izšķiešana.

Tas, kas man prasīja trīs dienas, varēja tikt paveikts dažu minūšu laikā, ja zinātu pareizos paņēmienus.

Šajā apmācībā es jums parādīšu dažādus veidus, kā padarīt šo procesu īpaši ātru un ļoti vienkāršu (ar un bez VBA).

Šajā apmācībā parādīto metožu ierobežojumi: Izmantojot tālāk norādītās metodes, jūs varēsit iegūt tikai galvenās mapes failu nosaukumus. Jūs nesaņemsiet failu nosaukumus galvenās mapes apakšmapēs. Šeit ir veids, kā iegūt failu nosaukumus no mapēm un apakšmapēm, izmantojot Power Query

Funkcijas FILES izmantošana, lai no mapes iegūtu failu nosaukumu sarakstu

Dzirdēts FILES funkcija pirms tam?

Neuztraucieties, ja neesat.

Tas ir no Excel izklājlapu bērnības laikiem (4. versijas formula).

Lai gan šī formula nedarbojas darblapas šūnās, tā joprojām darbojas nosauktajos diapazonos. Mēs izmantosim šo faktu, lai no norādītās mapes iegūtu failu nosaukumu sarakstu.

Pieņemsim, ka jums ir mape ar nosaukumu - “Pārbaudes mape“Uz darbvirsmas, un jūs vēlaties iegūt visu šajā mapē esošo failu nosaukumu sarakstu.

Tālāk ir norādītas darbības, kas dos jums failu nosaukumus no šīs mapes:

1. Šūnā A1 ievadiet mapes pilno adresi, kam seko zvaigznītes zīme (*)
   • Piemēram, ja jūsu mape C diskā, adrese izskatīsies šādi
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Ja neesat pārliecināts, kā iegūt mapes adresi, izmantojiet šādu metodi:
     • • Mapē, no kuras vēlaties iegūt failu nosaukumus, vai nu izveidojiet jaunu Excel darbgrāmatu, vai atveriet mapē esošu darbgrāmatu un izmantojiet zemāk esošo formulu jebkurā šūnā. Šī formula sniegs jums mapes adresi un beigās pievienos zvaigznītes zīmi (*). Tagad jūs varat kopēt-ielīmēt (ielīmēt kā vērtību) šo adresi jebkurā darbgrāmatas šūnā (šajā piemērā A1), kurā vēlaties failu nosaukumus.

         = REPLACE (CELL ("faila nosaukums"), FIND ("[", CELL ("faila nosaukums")), LEN (CELL ("faila nosaukums")), "*")

         [Ja mapē esat izveidojis jaunu darbgrāmatu, lai izmantotu iepriekš minēto formulu un iegūtu mapes adresi, iespējams, vēlēsities to izdzēst, lai tā netiktu iekļauta šīs mapes failu sarakstā]
2. Dodieties uz cilni “Formulas” un noklikšķiniet uz opcijas “Definēt vārdu”.
3. Dialoglodziņā Jauns nosaukums izmantojiet šādu informāciju
   • Nosaukums: FileNameList (nekautrējieties izvēlēties, kāds nosaukums jums patīk)
   • Darbības joma: darba grāmata
   • Attiecas uz: = FILES (Sheet1! $ A $ 1)
4. Tagad, lai iegūtu failu sarakstu, mēs izmantosim nosaukto diapazonu funkcijā INDEX. Dodieties uz šūnu A3 (vai jebkuru šūnu, kurā vēlaties sākt nosaukumu sarakstu) un ievadiet šādu formulu:

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

5. Velciet to uz leju, un tiks parādīts visu mapē esošo failu nosaukumu saraksts

Vai vēlaties iegūt failus ar noteiktu paplašinājumu ??

Ja vēlaties iegūt visus failus ar noteiktu paplašinājumu, vienkārši mainiet zvaigznīti ar šo faila paplašinājumu. Piemēram, ja vēlaties tikai Excel failus, varat izmantot * xls *, nevis *

Tātad mapes adrese, kas jums jāizmanto, būtu C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Līdzīgi Word dokumentu failiem izmantojiet *doc *

Kā tas darbojas?

FILES formula izgūst visu norādītā paplašinājuma failu nosaukumus norādītajā mapē.

Formulā INDEX mēs esam norādījuši failu nosaukumus kā masīvu un atgriežam 1., 2., 3. failu nosaukumus un tā tālāk, izmantojot funkciju ROW.

Ņemiet vērā, ka esmu izmantojis Rinda ()-2, jo sākām no trešās rindas. Tātad ROW ()-2 būtu 1 pirmajā instancē, 2 otrajā instancē, ja rindas numurs ir 4 utt. Un tā tālāk.

Skatieties video - iegūstiet failu nosaukumu sarakstu no mapes programmā Excel

VBA izmantošana Iegūstiet visu mapes failu nosaukumu sarakstu

Tagad man jāsaka, ka iepriekš minētā metode ir nedaudz sarežģīta (ar vairākiem soļiem).

Tomēr tas ir daudz labāk nekā to darīt manuāli.

Bet, ja jums patīk izmantot VBA (vai ja jūs labi izpildāt precīzas darbības, kuras es uzskaitīšu zemāk), varat izveidot pielāgotu funkciju (UDF), kas var viegli iegūt visu failu nosaukumus.

Ieguvums no a User Defined Function (UDF) ir tas, ka jūs varat saglabāt funkciju personiskajā makro darbgrāmatā un viegli to atkārtoti izmantot, neatkārtojot darbības atkal un atkal. Varat arī izveidot pievienojumprogrammu un koplietot šo funkciju ar citiem.

Tagad ļaujiet man vispirms sniegt jums VBA kodu, kas izveidos funkciju, lai iegūtu visu failu nosaukumu sarakstu no mapes programmā Excel.

Funkcija GetFileNames (ByVal FolderPath kā ​​virkne) Kā variants Dim Rezultāts kā variants Dim i kā vesels skaitlis Dim MyFile kā objekts Dim MyFSO kā objekts Dim MyFolder kā objekts Dim MyFiles kā objektu kopa MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder (FolderPath) Iestatiet MyFiles = MyFolder.Files ReDim Result (1 uz MyFiles.Count) i = 1 Katram MyFile In MyFi Result (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Rezultāta beigu funkcija

Iepriekš minētais kods izveidos funkciju GetFileNames, ko var izmantot darblapās (tāpat kā parastās funkcijas).

Kur ievietot šo kodu?

Lai kopētu šo kodu VB redaktorā, veiciet tālāk norādītās darbības.

• Dodieties uz cilni Izstrādātājs.
• Noklikšķiniet uz pogas Visual Basic. Tas atvērs VB redaktoru.
• VB redaktorā ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, kurā strādājat, dodieties uz Ievietot un noklikšķiniet uz moduļa. Ja neredzat Project Explorer, izmantojiet īsinājumtaustiņu Control + R (turiet nospiestu vadības taustiņu un nospiediet taustiņu “R”).
• Veiciet dubultklikšķi uz moduļa objekta un nokopējiet un ielīmējiet iepriekš minēto kodu moduļa koda logā.

Kā izmantot šo funkciju?

Tālāk ir norādītas darbības, kā izmantot šo funkciju darblapā:

• Jebkurā šūnā ievadiet tās mapes adresi, no kuras vēlaties uzskaitīt failu nosaukumus.
• Šūnā, kurā vēlaties sarakstu, ievadiet šādu formulu (es to ievadu šūnā A3):

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

• Nokopējiet un ielīmējiet formulu zemāk esošajās šūnās, lai iegūtu visu failu sarakstu.

Ņemiet vērā, ka šūnā es ievadīju mapes atrašanās vietu un pēc tam izmantoju šo šūnu GetFileNames formula. Mapes adresi varat arī kodēt ar formulu, kā parādīts zemāk:

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

Iepriekšminētajā formulā mēs izmantojām ROW ()-2 un sākām no trešās rindas. Tas nodrošināja, ka, kopējot formulu zemāk esošajās šūnās, tā tiks palielināta par 1. Ja ievadāt formulu kolonnas pirmajā rindā, varat vienkārši izmantot ROW ().

Kā šī formula darbojas?

Formula GetFileNames atgriež masīvu, kurā ir visu mapē esošo failu nosaukumi.

Funkciju INDEX izmanto, lai katrai šūnai uzskaitītu vienu faila nosaukumu, sākot ar pirmo.

Funkcija IFERROR tiek izmantota, lai atgrieztu tukšu, nevis #REF! kļūda, kas tiek parādīta, kad šūnā tiek kopēta formula, bet sarakstā vairs nav failu nosaukumu.

VBA izmantošana Iegūstiet visu failu nosaukumu sarakstu ar noteiktu paplašinājumu

Iepriekš minētā formula darbojas lieliski, ja vēlaties no Excel mapes iegūt visu failu nosaukumu sarakstu.

Bet ko darīt, ja vēlaties iegūt tikai video failu nosaukumus vai tikai Excel failus vai tikai to failu nosaukumus, kas satur noteiktu atslēgvārdu.

Tādā gadījumā varat izmantot nedaudz atšķirīgu funkciju.

Zemāk ir kods, kas ļaus iegūt visus failu nosaukumus ar noteiktu atslēgvārdu (vai konkrētu paplašinājumu).

Funkcija GetFileNamesbyExt (ByVal FolderPath kā ​​virkne, FileExt kā virkne) Kā variants Dim Rezultāts kā variants Dim i kā vesels skaitlis Dim MyFile kā objekts Dim MyFSO kā objekts Dim MyFolder kā objekts Dim MyFiles kā objektu kopa MyFSO = CreateObject ("Scripting.FileSystemObject" MyFolder = MyFSO.GetFolder (FolderPath) Iestatīt MyFiles = MyFolder.Files ReDim Result (1 uz MyFiles.Count) i = 1 Katram MyFile failā MyFiles If InStr (1, MyFile.Name, FileExt) 0 Tad rezultāts (i) = MyFile .Nosaukums i = i + 1 beigas, ja nākamais MyFile ReDim saglabā rezultātu (1 līdz i - 1) GetFileNamesbyExt = Rezultāta beigu funkcija

Iepriekš minētais kods izveidos funkciju “GetFileNamesbyExt“Ko var izmantot darblapās (tāpat kā parastās funkcijas).

Šai funkcijai ir divi argumenti - mapes atrašanās vieta un paplašinājuma atslēgvārds. Tas atgriež failu nosaukumu masīvu, kas atbilst norādītajam paplašinājumam. Ja nav norādīts neviens paplašinājums vai atslēgvārds, tas atgriezīs visus failu nosaukumus norādītajā mapē.

Sintakse: = GetFileNamesbyExt (“Mapes atrašanās vieta”, “Paplašinājums”)

Kur ievietot šo kodu?

Lai kopētu šo kodu VB redaktorā, veiciet tālāk norādītās darbības.

• Dodieties uz cilni Izstrādātājs.
• Noklikšķiniet uz pogas Visual Basic. Tas atvērs VB redaktoru.
• VB redaktorā ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, kurā strādājat, dodieties uz Ievietot un noklikšķiniet uz moduļa. Ja neredzat Project Explorer, izmantojiet īsinājumtaustiņu Control + R (turiet nospiestu vadības taustiņu un nospiediet taustiņu “R”).
• Veiciet dubultklikšķi uz moduļa objekta un nokopējiet un ielīmējiet iepriekš minēto kodu moduļa koda logā.

Kā izmantot šo funkciju?

Tālāk ir norādītas darbības, kā izmantot šo funkciju darblapā:

• Jebkurā šūnā ievadiet tās mapes adresi, no kuras vēlaties uzskaitīt failu nosaukumus. Es to ievadīju šūnā A1.
• Šūnā ievadiet paplašinājumu (vai atslēgvārdu), kuram vēlaties norādīt visus failu nosaukumus. Es to ievadīju šūnā B1.
• Šūnā, kurā vēlaties sarakstu, ievadiet šādu formulu (es to ievadu šūnā A3):

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

• Nokopējiet un ielīmējiet formulu zemāk esošajās šūnās, lai iegūtu visu failu sarakstu.

Kā ar tevi? Jebkuri Excel triki, ko izmantojat, lai atvieglotu dzīvi. Es labprāt mācītos no jums. Kopīgojiet to komentāru sadaļā!