Получить список имен файлов из папки с помощью Excel (с VBA и без него)

В мой первый день в небольшой консалтинговой фирме мне поручили трехдневный краткосрочный проект.Работа проста.

На сетевом диске много папок, в каждой сотни файлов.

Я должен был выполнить следующие три шага:

1. Выберите файл и скопируйте его имя.
2. Вставьте имя в ячейку Excel и нажмите Enter.
3. Перейдите к следующему файлу и повторите шаги 1 и 2.

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

Это просто и требует очень много времени.

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

В этом уроке я покажу вам различные способы сделать весь процесс очень быстрым и очень простым (с VBA и без него).

Ограничения методов, показанных в этом руководстве:Используя технику, показанную ниже, вы сможете получить имена файлов только в вашей домашней папке.Вы не получаете имена файлов в подпапках в основной папке.Вот способ получить имена файлов из папок и подпапок с помощью Power Query.

Используйте функцию ФАЙЛЫ, чтобы получить список имен файлов из папки

слышал об этомФАЙЛЫ функция?

Если нет, не волнуйтесь.

Это из таблицы Excel из детства (формулы версии 4).

Хотя эта формула не работает с ячейками рабочего листа, она по-прежнему работает с именованными диапазонами.Мы будем использовать этот факт, чтобы получить список имен файлов из указанной папки.

Теперь предположим, что у вас на рабочем столе есть файл с именем " Папка тестирования ” и вы хотите получить список имен файлов для всех файлов в этой папке.

Вот шаги, которые дадут вам имена файлов в этой папке:

1. В ячейке A1 введите полный адрес папки, а затем звездочку (*).
   • Например, если ваша папка находилась на диске C, адрес будет примерно таким
     C:\Users\ВАШЕ ИМЯ\Рабочий стол\Тестовая папка\*
   • Если вы не знаете, как получить адрес папки, используйте следующий метод:
     • • В папке, из которой вы хотите получить имя файла, создайте новую книгу Excel или откройте существующую книгу в папке и используйте следующую формулу в любой ячейке.Эта формула даст вам адрес папки со звездочкой (*) в конце.Теперь вы можете скопировать и вставить (вставить как значение) этот адрес в любую ячейку рабочей книги (в данном примере A1), где вы хотите указать имя файла.

         =ЗАМЕНИТЬ(ЯЧЕЙКА("имя файла"),НАЙТИ("[",ЯЧЕЙКА("имя файла")),ДЛСТР(ЯЧЕЙКА("имя файла")),"*")

         [Если вы создали новую книгу в папке, чтобы использовать приведенную выше формулу и получить адрес папки, вам может потребоваться удалить ее, чтобы ее не было в списке файлов в этой папке]

2. Перейдите на вкладку «Формулы» и нажмите «Определить имена».
3. В диалоговом окне «Новое имя» используйте следующие сведения.
   • Имя: FileNameList (не стесняйтесь выбирать любое имя, которое вам нравится)
   • Область применения: Рабочая тетрадь
   • Относится к: =ФАЙЛЫ(Лист1!$A$1)
4. Теперь, чтобы получить список файлов, мы будем использовать именованный диапазон в функции ИНДЕКС.Перейдите в ячейку A3 (или любую другую ячейку, с которой вы хотите, чтобы список имен начинался) и введите следующую формулу:

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

5. Перетащите его вниз, и он даст вам список всех имен файлов в папке.

Хотите извлечь файлы с определенным расширением? ?

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

Таким образом, адрес папки, который вам нужно использовать,C: папка UsersSumitDesktopTest*xls*

Аналогично, для файлов документов Word используйте *doc*

Как это работает?

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

В формуле ИНДЕКС мы задаем имена файлов в виде массива и используем функцию СТРОКА для возврата первого, второго, третьего и т. д. имен файлов.

Обратите внимание, что я использовалРЯД()-2, так как мы начинаем с третьей строки.Таким образом, когда номер строки равен 4, ROW()-2 будет равен 1 для первого экземпляра, 2 для второго и так далее.

Получить список всех имен файлов из папки с помощью VBA

Теперь я должен сказать, что описанный выше метод немного сложен (много шагов).

Однако это намного лучше, чем делать это вручную.

Однако, если вам удобно использовать VBA (или если вы хорошо выполняете точные шаги, которые я перечислю ниже), вы можете создать пользовательскую функцию (UDF), которая может легко получить имена всех файлов.

Преимущества использования пользовательских функций (UDF)是выå ¯ä »¥將функцияСохраняйте макросы в личных книгах и легко используйте их повторно, не повторяя эти шаги снова и снова.Вы также можете создавать надстройки и делиться этой функциональностью с другими.

Теперь позвольте мне сначала дать вам код VBA, который создаст функцию для получения списка всех имен файлов из папки в Excel.

Функция GetFileNames(ByVal FolderPath As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Для каждого MyFile в MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Result End Function

Приведенный выше код создаст функцию GetFileNames, которую можно использовать на листе (как обычную функцию).

Куда вставить этот код?

Выполните следующие шаги, чтобы скопировать этот код в редакторе VB.

Как использовать эту функцию?

Вот шаги, чтобы использовать эту функцию на листе:

• В любую ячейку введите адрес папки, из которой вы хотите получить список имен файлов.
• В ячейку, где вы хотите получить список, введите следующую формулу (я ввел ее в ячейку A3):

  =ЕСЛИОШИБКА(ИНДЕКС(ПолучитьИменаФайлов($A$1),СТРОКА()-2),"")

• Скопируйте и вставьте формулу в ячейку ниже, чтобы получить список всех файлов.

Обратите внимание, что я ввел расположение папки в одну ячейку, а затем вПолучить имена файловЭта ячейка используется в формуле.Вы также можете жестко указать адрес папки в формуле следующим образом:

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

В приведенной выше формуле мы использовали ROW()-2 и начинаем с третьей строки.Это гарантирует, что когда я копирую формулу в ячейку ниже, она будет увеличиваться на 1.Если вы вводите формулу в первой строке столбца, вы можете просто использовать ROW().

Как работает эта формула?

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

Функция ИНДЕКС используется для перечисления одного имени файла в ячейке, начиная с первого.

Функция ЕСЛИОШИБКА используется для возврата пустого значения вместо #ССЫЛКА!Ошибка отображается, когда формула копируется в ячейку, но больше нет имен файлов для списка.

Получить список всех имен файлов с определенным расширением с помощью VBA

Приведенная выше формула полезна, когда вы хотите получить список всех имен файлов из папки в Excel.

Однако что, если вы хотите получить только имя видеофайла, или просто файла Excel, или просто файла, содержащего определенное ключевое слово.

В этом случае можно использовать немного другую функцию.

Приведенный ниже код позволит вам получить все имена файлов, в которых есть определенное ключевое слово (или определенное расширение).

Функция GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant Dim Result As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 Для каждого MyFile в MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Функция завершения результата

Приведенный выше код создаст функцию, которую можно использовать на листе. GetFileNamesbyExt " (как обычные функции).

Эта функция принимает два параметра — расположение папки и ключевое слово расширения.Он возвращает массив имен файлов, соответствующих заданному расширению.Если расширение или ключевое слово не указано, будут возвращены все имена файлов в указанной папке.

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

Куда вставить этот код?

Выполните следующие шаги, чтобы скопировать этот код в редакторе VB.

• Перейдите на вкладку разработчика.
• Нажмите кнопку Visual Basic.Это откроет редактор VB.
• В редакторе VB щелкните правой кнопкой мыши любой объект в рабочей книге, над которой вы работаете, выберите «Вставить» и нажмите «Модуль».Если вы не видите Project Explorer, используйте сочетание клавиш Control + R (удерживайте Ctrl и нажмите «R»).
• Дважды щелкните объект модуля, скопируйте и вставьте приведенный выше код в окно кода модуля.

Как использовать эту функцию?

Вот шаги, чтобы использовать эту функцию на листе:

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

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

• Скопируйте и вставьте формулу в ячейку ниже, чтобы получить список всех файлов.

Как насчет васЛюбые приемы Excel, которые вы используете для упрощения своей жизни.Я хотел бы учиться у вас.Поделитесь им в разделе комментариев!