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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Посмотреть видео — получить список имен файлов из папки в Excel

Использование VBA для получения списка всех имен файлов из папки

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

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

Преимущество использования Uсер Dопределен FФункция (UDF) заключается в том, что вы можете сохранить функцию в личной книге макросов и легко использовать ее повторно, не повторяя эти шаги снова и снова. Вы также можете создать надстройку и поделиться этой функцией с другими.

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

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

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

Ниже приведены шаги по использованию этой функции на листе:

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

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

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

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

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

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

Использование VBA для получения списка всех имен файлов с определенным расширением

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

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

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

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

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

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

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

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

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

Ниже приведены шаги по использованию этой функции на листе:

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

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

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

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

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

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