✅ Отримати список імен файлів з папки в Excel (з VBA та без неї)

Зміст

Використання функції FILES для отримання списку імен файлів з папки
За допомогою VBA Отримайте список усіх імен файлів з папки
За допомогою VBA Отримайте список усіх імен файлів із певним розширенням

У перший день роботи в невеликій консалтинговій фірмі я три дні працював над коротким проектом.

Робота була проста.

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

Мені довелося виконати ці три кроки:

Виберіть файл і скопіюйте його ім'я.
Вставте це ім’я в клітинку в Excel і натисніть Enter.
Перейдіть до наступного файлу та повторіть кроки 1 та 2.

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

Це було - Просто і величезна втрата часу.

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

У цьому підручнику я покажу вам різні способи зробити весь цей процес надзвичайно швидким та надлегким (з VBA та без неї).

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

Використання функції FILES для отримання списку імен файлів з папки

Чув Функція FILES раніше?

Не хвилюйтесь, якщо ви цього не зробили.

Це з дитинства електронних таблиць Excel (формула версії 4).

Хоча ця формула не працює у клітинках аркуша, вона все ще працює у іменованих діапазонах. Ми будемо використовувати цей факт для отримання списку імен файлів із зазначеної папки.

Тепер припустимо, що у вас є папка з назвою - ‘Тестова папка'На робочому столі, і ви хочете отримати список імен файлів для всіх файлів у цій папці.

Ось кроки, які дадуть вам імена файлів із цієї папки:

У клітинці A1 введіть повну адресу папки, а потім знак зірочки (*)
- Наприклад, якщо ваша папка на диску C, то адреса буде виглядати так
  C: \ Users \ Sumit \ Desktop \ Test Folder \*
- Якщо ви не впевнені, як отримати адресу папки, скористайтеся наступним методом:
  - - У папці, з якої потрібно отримати назви файлів, або створіть нову книгу Excel, або відкрийте наявну книгу в папці та скористайтесь наведеною нижче формулою у будь -якій клітинці. Ця формула дасть вам адресу папки та додасть у кінці знак зірочки (*). Тепер ви можете скопіювати-вставити (вставити як значення) цю адресу в будь-яку клітинку (A1 у цьому прикладі) у книзі, у якій ви хочете назвати файли.
```
= REPLACE (CELL ("ім'я файлу")), FIND ("[", CELL ("ім'я файлу")), LEN (CELL ("ім'я файлу")), "*")
```
      [Якщо ви створили нову книгу у папці, щоб використати вищезазначену формулу та отримати адресу папки, ви можете видалити її, щоб вона не відображалася у списку файлів у цій папці]
Перейдіть на вкладку «Формули» та натисніть на опцію «Визначити назву».
У діалоговому вікні «Нове ім'я» використовуйте наведені нижче відомості
- Назва: FileNameList (не соромтеся вибрати будь -яку назву, яка вам подобається)
- Область застосування: Робочий зошит
- Посилається на: = FILES (Sheet1! $ A $ 1)
Тепер, щоб отримати список файлів, ми будемо використовувати іменований діапазон у функції INDEX. Перейдіть до комірки А3 (або будь -якої комірки, де потрібно розпочати список імен) і введіть таку формулу:
```
= IFERROR (INDEX (FileNameList, ROW ()-2), "")
```
Перетягніть це вниз, і він дасть вам список усіх назв файлів у папці

Хочете витягти файли з певним розширенням ??

Якщо ви хочете отримати всі файли з певним розширенням, просто змініть зірочку з цим розширенням. Наприклад, якщо вам потрібні лише файли Excel, ви можете використовувати * xls * замість *

Отже, адреса папки, яку потрібно використовувати, буде такою C: \ Users \ Sumit \ Desktop \ Test Folder \*xls*

Аналогічно для файлів документів Word використовуйте *doc *

Як це працює?

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

У формулі INDEX ми вказали імена файлів як масив, і ми повертаємо перші, другі, треті імена файлів тощо за допомогою функції ROW.

Зверніть увагу, що я користувався РЯД ()-2, як ми починали з третього ряду. Отже, ROW ()-2 буде 1 для першого екземпляра, 2 для другого екземпляра, коли номер рядка 4, і так далі тощо.

Перегляд відео - Отримайте список імен файлів з папки в Excel

За допомогою VBA Отримайте список усіх імен файлів з папки

Тепер я повинен сказати, що вищезгаданий метод є трохи складним (з кількома кроками).

Однак це набагато краще, ніж робити це вручну.

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

Перевага використання a Uсер Dуточнено Function (UDF) полягає в тому, що ви можете зберегти функцію в особистій книзі макросів та легко використовувати її повторно, не повторюючи кроків знову і знову. Ви також можете створити надбудову та поділитися цією функцією з іншими.

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

Функція GetFileNames (ByVal FolderPath As String) Як варіант Dim Dim Результат As Variant Dim i As Integer Dim MyFile As Object Dim MyFSO як Object Dim MyFolder як Object Dim MyFiles As Object Set MyFSO = CreateObject ("Scripting.FileSystemObject") Set MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = MyFolder = GetFolder (FolderPath) Встановити MyFiles = MyFolder.Files Результат ReDim (1 до MyFiles.Count) i = 1 Для кожного MyFile у MyFiles Результат (i) = MyFile.Name i = i + 1 Наступний MyFile GetFileNames = Результат кінцевої функції

Наведений вище код створить функцію GetFileNames, яку можна використовувати на робочих аркушах (так само, як звичайні функції).

Де розмістити цей код?

Виконайте наведені нижче кроки, щоб скопіювати цей код у редакторі VB.

Перейдіть на вкладку Розробник.
Натисніть кнопку Visual Basic. Відкриється редактор VB.
У редакторі VB клацніть правою кнопкою миші будь-який об’єкт робочої книги, над якою ви працюєте, перейдіть до пункту Вставити та натисніть Модуль. Якщо ви не бачите Провідник проектів, скористайтесь комбінацією клавіш Control + R (утримуйте клавішу управління і натисніть клавішу «R»).
Двічі клацніть на об’єкті модуля та скопіюйте та вставте вищевказаний код у вікно коду модуля.

Як користуватися цією функцією?

Нижче наведено кроки для використання цієї функції на аркуші:

У будь -якій клітинці введіть адресу папки, з якої потрібно перелічити назви файлів.
У клітинку, де потрібно список, введіть таку формулу (я вводжу її в клітинку А3):
```
= 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 як рядок, FileExt як рядок) Як варіант Dim Dim Результат як варіант Dim Dim як ціле число Dim MyFile як об'єкт Dim MyFSO як об'єкт Dim MyFolder як об'єкт Dim MyFiles як набір об'єктів MyFSO = CreateObject ("Scripting.FileSystemObject" MyFolder = MyFSO. .Name i = i + 1 End If Next MyFile ReDim Зберегти результат (1 To i - 1) GetFileNamesbyExt = Функція завершення результату

Наведений вище код створить функцію "GetFileNamesbyExt', Які можна використовувати на робочих аркушах (так само, як звичайні функції).

Ця функція бере два аргументи - розташування папки та ключове слово розширення. Він повертає масив імен файлів, які відповідають даному розширення. Якщо розширення або ключове слово не вказано, вони повернуть усі імена файлів у вказаній папці.

Синтаксис: = GetFileNamesbyExt (“Розташування папки”, “Розширення”)

Де розмістити цей код?

Виконайте наведені нижче кроки, щоб скопіювати цей код у редакторі VB.

Перейдіть на вкладку Розробник.
Натисніть кнопку Visual Basic. Відкриється редактор VB.
У редакторі VB клацніть правою кнопкою миші будь-який об’єкт робочої книги, над якою ви працюєте, перейдіть до пункту Вставити та натисніть Модуль. Якщо ви не бачите Провідник проектів, скористайтесь комбінацією клавіш Control + R (утримуйте клавішу управління і натисніть клавішу «R»).
Двічі клацніть на об’єкті модуля та скопіюйте та вставте вищевказаний код у вікно коду модуля.

Як користуватися цією функцією?

Нижче наведено кроки для використання цієї функції на аркуші:

У будь -якій клітинці введіть адресу папки, з якої потрібно перерахувати назви файлів. Я ввів це в клітинку А1.
У клітинці введіть розширення (або ключове слово), для якого вам потрібні всі назви файлів. Я ввів це в клітинку В1.
У клітинку, де потрібно список, введіть таку формулу (я вводжу її в клітинку А3):
```
= IFERROR (INDEX (GetFileNamesbyExt ($ A $ 1, $ B $ 1), ROW ()-2), ""))
```
Скопіюйте та вставте формулу в клітинки нижче, щоб отримати список усіх файлів.