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

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

Работа была простой.

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

Я должен был выполнить эти три шага:

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

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

Это было - просто и огромная трата времени.

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

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

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

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

Слышал о ФАЙЛЫ функция перед?

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

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

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

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

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

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

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

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

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

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

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

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

Таким образом, адрес папки, который вам нужно использовать, будет C: \ Users \ Sumit \ Desktop \ Test Folder \ * xls *

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

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

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

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

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

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

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

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

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

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

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

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

Функция GetFileNames (ByVal FolderPath как строка) как вариант Dim Результат как вариант Dim i как целое Dim MyFile как объект Dim MyFSO как объект Dim MyFolder как объект Dim MyFiles как объект Установить MyFSO = CreateObject ("Scripting.FileSystemObject") Установить MyFolder = MyFolder. GetFolder (FolderPath) Set MyFiles = MyFolder.Files ReDim Result (1 To MyFiles.Count) i = 1 для каждого MyFile в MyFiles Результат (i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Результат Конечная функция

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

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

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

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

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

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

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

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

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

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

= ЕСЛИОШИБКА (ИНДЕКС (GetFileNames ("C: \ Users \ Sumit \ Desktop \ Test Folder"), ROW () - 2), "")

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

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

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

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

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

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

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

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

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

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

Функция GetFileNamesbyExt (ByVal FolderPath как строка, FileExt как строка) как вариант Dim Результат как вариант Dim i как целое число Dim MyFile как объект Dim MyFSO как объект Dim MyFolder как объект Dim MyFiles как объект Set MyFSO = CreateObject ("Scripting.FileSystemObject") MyFolder = MyFSO.GetFolder (FolderPath) Установить MyFiles = MyFolder.Files Результат ReDim (1 в MyFiles.Count) i = 1 для каждого MyFile в MyFiles Если InStr (1, MyFile.Name, FileExt) 0, то Результат (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Preserve Result (1 To i - 1) GetFileNamesbyExt = Результат End Функция

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

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

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

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

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

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

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

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

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

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

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

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