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

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

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

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

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

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

Това е просто и отнема много време.

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

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

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

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

чу за товаФункция FILES?

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

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

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

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

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

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

         =REPLACE(CELL("име на файл"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")

         [Ако сте създали нова работна книга в папка, за да използвате горната формула и да получите адреса на папката, може да се наложи да я изтриете, така че да не е в списъка с файлове в тази папка]

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

   =АКО ГРЕШКА(ИНДЕКС(Списък с имена на файлове,РЕД()-2),"")

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

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

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

Така че адресът на папката, който трябва да използвате, еC:UsersSumitDesktopTest Папка*xls*

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

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

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

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

Имайте предвид, че използвахРЕД()-2, тъй като започваме от третия ред.Така че, когато номерът на реда е 4, ROW()-2 ще бъде 1 за първия екземпляр, 2 за втория и т.н.

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

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

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

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

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

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

Функция GetFileNames(ByVal FolderPath като низ) Като вариант Dim Резултат като вариант Dim i Като цяло число Dim MyFile Като обект Dim MyFSO Като обект Dim MyFolder Като обект Dim MyFiles Като обект Задайте MyFSO = CreateObject("Scripting.FileSystemFolder") Set MySOObject. GetFolder(FolderPath) Задайте MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 За всеки MyFile в MyFiles Result(i) = MyFile.Name i = i + 1 Следващ MyFile GetFileNames = Крайна функция на резултата

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

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

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

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

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

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

  =АКО ГРЕШКА(ИНДЕКС(GetFileNames($A$1),ROW()-2),"")

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

Имайте предвид, че въведох местоположението на папката в една клетка и след това вGetFileNamesТази клетка се използва във формула.Можете също така твърдо да кодирате адреса на папката във формулата по следния начин:

=АКО ГРЕШКА(ИНДЕКС(GetFileNames("C:\Users\ВАШЕТО ИМЕ\Desktop\Test Folder"),ROW()-2),"")

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

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

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

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

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

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

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

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

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

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

Функция GetFileNamesbyExt(ByVal FolderPath като низ, FileExt като низ) As Variant Dim Result As Variant Dim i As Integer Dim MyFile Като Object Dim MyFSO Като Object Dim MyFolder Като Object Dim MyFiles Като Object Set MyFSO = CreateObject("Scripte) Set" MyFolder = MyFSO.GetFolder(FolderPath) Задайте MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 За всеки MyFile в MyFiles Ако InStr(1, MyFile.Name, FileExt) <> 0 Тогава Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Запазване на резултата (1 до i - 1) GetFileNamesbyExt = Функция за край на резултата

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

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

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

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

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

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

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

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

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

  =АКО ГРЕШКА(ИНДЕКС(GetFileNamesbyExt($A$1,$B$1),ROW()-2),"")

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

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