Вземете списъка с имена на файлове от папка в Excel (със и без VBA)

В първия ми ден на работа в малка консултантска фирма бях укомплектован по кратък проект за три дни.

Работата беше проста.

На мрежовото устройство имаше много папки и всяка папка съдържаше стотици файлове.

Трябваше да следвам тези три стъпки:

1. Изберете файла и копирайте името му.
2. Поставете това име в клетка в Excel и натиснете Enter.
3. Преминете към следващия файл и повторете стъпки 1 и 2.

Звучи просто нали?

Беше - Просто и огромна загуба на време.

Това, което ми отне три дни, би могло да се направи за няколко минути, ако знаех правилните техники.

В този урок ще ви покажа различни начини да направите целия този процес супер бърз и супер лесен (със и без VBA).

Ограничения на методите, показани в този урок: С техниките, показани по -долу, ще можете да получите само имената на файловете в основната папка. Няма да получите имената на файловете в подпапките в основната папка. Ето начин да получите имена на файлове от папки и подпапки с помощта на Power Query

Използване на функция FILES за получаване на списък с имена на файлове от папка

Чувал за FILES функция преди?

Не се притеснявайте, ако не сте.

Това е от детските дни на електронни таблици на Excel (формула версия 4).

Въпреки че тази формула не работи в клетките на работния лист, тя все още работи в именувани диапазони. Ще използваме този факт, за да получим списъка с имена на файлове от определена папка.

Сега, да предположим, че имате папка с името - „Тестова папка„На работния плот и искате да получите списък с имената на файловете за всички файлове в тази папка.

Ето стъпките, които ще ви дадат имената на файловете от тази папка:

1. В клетка A1 въведете пълния адрес на папката, последван от звездичка (*)
   • Например, ако вашата папка в устройството C, адресът ще изглежда така
     C: \ Users \ Sumit \ Desktop \ Test Folder \*
   • Ако не сте сигурни как да получите адреса на папката, използвайте следния метод:
     • • В папката, от която искате да получите имената на файловете, или създайте нова работна книга на Excel, или отворете съществуваща работна книга в папката и използвайте формулата по -долу във всяка клетка. Тази формула ще ви даде адреса на папката и ще добави знак със звездичка (*) в края. Сега можете да копирате-поставите (поставите като стойност) този адрес във всяка клетка (A1 в този пример) в работната книга, в която искате имената на файловете.

         = ЗАМЕНИ (КЛЕТКА ("име на файл"), НАМЕРИ ("[", КЛЕТКА ("име на файл")), ЛЕН (КЛЕТКА ("име на файл")), "*")

         [Ако сте създали нова работна книга в папката, за да използвате горната формула и да получите адреса на папката, може да искате да я изтриете, така че да не фигурира в списъка с файлове в тази папка]
2. Отидете в раздела „Формули“ и кликнете върху опцията „Определяне на име“.
3. В диалоговия прозорец Ново име използвайте следните подробности
   • Име: FileNameList (не се колебайте да изберете каквото име искате)
   • Обхват: Работна тетрадка
   • Отнася се до: = FILES (Sheet1! $ A $ 1)
4. Сега, за да получим списъка с файлове, ще използваме именования диапазон в рамките на функция INDEX. Отидете в клетка A3 (или всяка клетка, където искате да започне списъкът с имена) и въведете следната формула:

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

5. Плъзнете това надолу и той ще ви даде списък с всички имена на файлове в папката

Искате да извлечете файлове със специфично разширение ??

Ако искате да получите всички файлове с определено разширение, просто сменете звездичката с това разширение на файла. Например, ако искате само Excel файлове, можете да използвате * xls * вместо *

Така че адресът на папката, който трябва да използвате, ще бъде C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

По същия начин за файлове с документи на Word използвайте *doc *

Как работи това?

Формулата FILES извлича имената на всички файлове с посоченото разширение в посочената папка.

Във формулата INDEX сме дали имената на файловете като масив и връщаме 1 -ва, 2 -ра, 3 -та имена на файлове и така нататък с помощта на функцията ROW.

Обърнете внимание, че съм използвал ROW ()-2, като започнахме от третия ред нататък. Така че ROW ()-2 ще бъде 1 за първата инстанция, 2 за втората, когато номерът на реда е 4, и така нататък и така нататък.

Гледайте видео - Вземете списък с имена на файлове от папка в Excel

Използване на VBA Вземете списък с всички имена на файлове от папка

Сега трябва да кажа, че горният метод е малко сложен (с няколко стъпки).

Това обаче е много по -добре, отколкото да правите това ръчно.

Но ако ви е удобно да използвате VBA (или ако сте добри в следването на точните стъпки, които ще изброя по -долу), можете да създадете персонализирана функция (UDF), която лесно може да ви даде имената на всички файлове.

Ползата от използването на a User дусъвършенстван Function (UDF) е, че можете да запишете функцията в лична работна книга за макроси и да я използвате повторно лесно, без да повтаряте стъпките отново и отново. Можете също да създадете добавка и да споделите тази функция с други.

Сега първо ще ви дам VBA кода, който ще създаде функция за получаване на списъка с всички имена на файлове от папка в Excel.

Функционални GetFileNames (ByVal FolderPath Както стринга) Както Variant Дим резултат като Variant Дим аз Като цяло число Дим моятФайл Като обект Дим MyFSO Като обект Дим MyFolder като Обект на Дим MyFiles като Обект на Set MyFSO = CreateObject ( "Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder (FolderPath) Задайте MyFiles = MyFolder.Files Резултат от повторното димиране (1 към MyFiles.Count) i = 1 За всеки MyFile в MyFiles Резултат (i) = MyFile.Name i = i + 1 Следваща MyFile GetFileNames = Резултат Крайна функция

Горният код ще създаде функция GetFileNames, която може да се използва в работните листове (също като обикновените функции).

Къде да сложа този код?

Следвайте стъпките по -долу, за да копирате този код във VB Editor.

• Отидете в раздела Разработчик.
• Щракнете върху бутона Visual Basic. Това ще отвори VB Editor.
• В редактора на VB щракнете с десния бутон върху някой от обектите на работната книга, в която работите, отидете на Вмъкване и щракнете върху Модул. Ако не виждате Project Explorer, използвайте клавишната комбинация Control + R (задръжте клавиша за управление и натиснете клавиша „R“).
• Щракнете двукратно върху обекта Module и копирайте и поставете горния код в прозореца с код на модула.

Как да използвате тази функция?

По -долу са описани стъпките за използване на тази функция в работен лист:

• Във всяка клетка въведете адреса на папката на папката, от която искате да изброите имената на файловете.
• В клетката, където искате списъка, въведете следната формула (въвеждам я в клетка A3):

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

• Копирайте и поставете формулата в клетките по -долу, за да получите списък с всички файлове.

Обърнете внимание, че въведох местоположението на папката в клетка и след това използвах тази клетка в GetFileNames формула. Можете също така да кодирате твърдо адреса на папката във формулата, както е показано по -долу:

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

В горната формула използвахме ROW ()-2 и започнахме от третия ред нататък. Това гарантира, че докато копирам формулата в клетките по -долу, тя ще се увеличи с 1. В случай, че въвеждате формулата в първия ред на колона, можете просто да използвате ROW ().

Как действа тази формула?

Формулата GetFileNames връща масив, който съдържа имената на всички файлове в папката.

Функцията INDEX се използва за изброяване на едно име на файл на клетка, като се започне от първото.

Функцията IFERROR се използва за връщане на празно място вместо #REF! грешка, която се показва, когато формула се копира в клетка, но няма повече имена на файлове за изброяване.

Използване на VBA Вземете списък с всички имена на файлове с конкретно разширение

Горната формула работи чудесно, когато искате да получите списък с всички имена на файлове от папка в Excel.

Но какво ще стане, ако искате да получите имената само на видео файловете, или само на файловете на Excel, или само на имената на файловете, които съдържат конкретна ключова дума.

В този случай можете да използвате малко по -различна функция.

По -долу е кодът, който ще ви позволи да получите всички имена на файлове с конкретна ключова дума в него (или с конкретно разширение).

Функция GetFileNamesbyExt (ByVal FolderPath As String, FileExt As String) Като вариант Dim Dim Резултат като Variant Dim i As Integer Dim MyFile As Object Dim MyFSO Като Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject" MyFolder = MyFSO. .Name i = i + 1 End If Next MyFile ReDim Запазване на резултата (1 To i - 1) GetFileNamesbyExt = Крайна функция на резултата

Горният код ще създаде функция "GetFileNamesbyExt", Което може да се използва в работните листове (точно като обикновените функции).

Тази функция приема два аргумента - местоположението на папката и ключовата дума за разширение. Той връща масив от имена на файлове, които съответстват на даденото разширение. Ако не е посочено разширение или ключова дума, те ще върнат всички имена на файлове в посочената папка.

Синтаксис: = GetFileNamesbyExt („Местоположение на папката“, „Разширение“)

Къде да сложа този код?

Следвайте стъпките по -долу, за да копирате този код във VB Editor.

• Отидете в раздела Разработчик.
• Щракнете върху бутона Visual Basic. Това ще отвори VB Editor.
• В редактора на VB щракнете с десния бутон върху някой от обектите на работната книга, в която работите, отидете на Вмъкване и щракнете върху Модул. Ако не виждате Project Explorer, използвайте клавишната комбинация Control + R (задръжте клавиша за управление и натиснете клавиша „R“).
• Щракнете двукратно върху обекта Module и копирайте и поставете горния код в прозореца с код на модула.

Как да използвате тази функция?

По -долу са описани стъпките за използване на тази функция в работен лист:

• Във всяка клетка въведете адреса на папката на папката, от която искате да изброите имената на файловете. Въведох това в клетка А1.
• В клетка въведете разширението (или ключовата дума), за което искате всички имена на файлове. Въведох това в клетка В1.
• В клетката, където искате списъка, въведете следната формула (въвеждам я в клетка A3):

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

• Копирайте и поставете формулата в клетките по -долу, за да получите списък с всички файлове.

Ами ти? Всички трикове на Excel, които използвате, за да улесните живота. Бих искал да се уча от вас. Споделете го в секцията за коментари!