Список файлов в папке в таблицу excel

Microsoft Excel

трюки • приёмы • решения

Как получить список имен файлов, содержащихся в папке, а затем отобразить этот список на листе Excel

В этой статье будет рассказано, как получить список имен файлов, содержащихся в каталоге, а затем отобразить этот список на листе. Данный способ связан с использованием макрофункции Excel 4 XLM в именованной формуле. Такой метод удобен, поскольку позволяет относительно легко выстроить на листе список имен файлов — обычно для этого требуются сложные макросы VBA.

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

  1. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
  2. Впишите в поле Имя название Список.
  3. Введите следующую формулу в поле Диапазон (рис. 8.1): =ФАЙЛЫ(Лист1!$A$1) .
  4. Нажмите ОК, чтобы закрыть окно Создание имени.

Рис. 8.1. Работа с диалоговым окном для создания именованной формулы

Обратите внимание: функция ФАЙЛЫ не относится к стандартным функциям рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для использования на специальном листе макросов. Она принимает один аргумент (путь к каталогу и описание файла) и возвращает массив имен файлов, содержащихся в данном каталоге и удовлетворяющих описанию файла. Обычные формулы рабочего листа не могут использовать подобные XLM-функции, а именованные формулы — могут.

Определив именованную формулу, введите в ячейку А1 путь к каталогу и описание файла. Например: C:Excelexpert.ru* *. Далее формула отображает первый найденный файл: =ИНДЕКС(Список;1) . Если изменить второй аргумент на 2, то будет отображен второй найденный файл и т. д. На рис. 8.2 приведен пример. Путь и описание файла указаны в ячейке А1. В ячейке А2 содержится следующая формула, скопированная далее по столбцу: =ИНДЕКС(Список;СТР0КА()-1) . Функция СТРОКА, используемая здесь, генерирует ряд последовательных натуральных чисел: 1,2,3 и т. д. Эти числа используются в качестве второго аргумента функции ИНДЕКС.

Рис. 8.2. Использование XLM-макроса в именованной формуле позволяет создать на листе список имен файлов

Обратите внимание: в ячейке А17 (и ниже) происходит ошибка. Дело в том, что в каталоге содержится всего 19 файлов и формула пытается отобразить имена несуществующих. Если изменить каталог или описание файла в ячейке А1, то формулы обновляются и выводят имена новых файлов.

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

Источник: excelexpert.ru

5 способов получить список файлов в папке

У некоторых пользователей, при работе на компьютере, возникает необходимость получить список файлов в папке. Такой список может быть нужен для разных целей: для каталогизации файлов в папках, для сравнения содержимого в папках, для того, чтобы составить список фильмов, музыкальных композиций, электронных книг и т. п.

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

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

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

Встречая в интернете какой-либо фильм, имея у себя список файлов в папке, я могу легко проверить, есть ли такой фильм у меня в коллекции, или я встретил новый фильм. Поэтому я приступил к поиску решения данного вопроса.

В итоге, я нашел несколько простых вариантов решения этой проблемы. Создать список файлов в папке можно разными способами: при помощи командной строки, при помощи файла, который имеет расширение «.bat», при помощи специальных программ, сохранить список в браузере.

Сохранение списка файлов с помощью командной строки

Вначале вам необходимо будет перейти к нужной папке, или диску, если вы хотите получить список файлов, которые находятся на диске. После открытия папки или диска, удерживая на клавиатуре кнопку «Shift», кликните правой кнопкой мыши внутри этой папки. В открывшемся контекстном меню выберите пункт «Открыть окно команд».

После этого откроется окно консоли, в которой уже будет указан путь к нужной директории (в вашем случае, будет к той папке, которую вы откроете). В этом примере, это папка «Программы» на Рабочем столе моего компьютера. В этой папке находятся другие папки, в которых находятся ярлыки программ.

Далее нужно будет ввести одну из этих команд:

После ввода «dir» должен быть пробел. Вместо «filelist» может написать свое название файла, только латинскими буквами. Не забудьте в конце добавить формат «.txt».

Эти команды отличаются друг от друга в использовании дополнительных критериев сохранения списка файлов:

  • /b — обозначает, что будут сохранены только имена файлов.
  • /s — обозначает, что дополнительно в список будут включены все файлы и подкаталоги (подпапки), которые находятся в этой папке.

Затем на клавиатуре нажмите на кнопку «Enter».

После этого вы можете открыть папку и увидеть, что в папке находится текстовый файл «filelist». Теперь можете открыть этот файл с помощью Блокнота.

Если в данной папке некоторые файлы имели русские имена, то вместо имен этих файлов, вы увидите непонятные символы (крякозябры). Дело в том, что программа Блокнот не понимает кодировку MS-DOS.

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

Кликните по текстовому файлу правой кнопкой мыши, а затем в контекстном меню выберите пункт «Открыть с помощью». Выберите для открытия файла программу Microsoft Office Word. В окне программы Word будет открыто окно «Преобразование текста», в котором нужно будет выбрать кодировку MS-DOS, которая позволит прочитать этот документ.

На образце вы увидите правильное отображение текста.

После нажатия на кнопку «ОК», данный текст будет открыт в программе Word. Вы можете провести редактирование документа, а потом сохранить его на компьютере в нужном формате, в том числе в формате «TXT». Проблем с кодировкой после сохранения файла уже не будет.

Получение списка файлов в папке с помощью bat файла

Один из самых доступных и простых способов получения списка файлов в папке: использование для этого специального bat файла. Существует несколько вариантов создания файла с расширением «.bat», с разным кодом.

Для создания bat файла вам нужно будет открыть Блокнот, а затем вставить туда этот код:

Сохраните этот файл, имя файлу можете дать любое, самому файлу нужно дать расширение «BAT». Параметры сохранения файла в Блокноте будут выглядеть примерно так:

  • Имя файла: filelist.bat
  • Тип файла: Все файлы

После этого нажмите на кнопку «Сохранить».

Далее помещаете созданный файл в ту папку, из которой вы хотите получить, список находящихся там файлов. После этого, запустите файл с расширением «.bat». В данной папке будет создан файл «filelist» в формате «.txt», в котором будет находиться список файлов.

Существует несколько вариантов содержимого файла с расширением «.bat». При использовании этого метода, проблем с кодировкой не возникнет.

Программа DirLister для получения списка файлов

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

Для запуска программы, откройте папку с программой, а потом кликните по приложению DirLister.

В окне программы DirLister, в разделе «Directory To Scan», при помощи кнопки «Open», вам нужно будет выбрать папку для сканирования.

В разделе «Output File», при помощи кнопки «Save As…», вам необходимо будет выбрать место для сохранения списка файлов.

Вы можете уточнить критерии поиска файлов по маске «*.*», выбрав нужное имя или тип файла («TXT», «Excel», «HTML» и т. д.). После выбора необходимых настроек, нажмите на кнопку «Make List».

Читайте также:  Как одновременно работать в excel нескольким людям

После завершения сканирования в левом нижнем углу программы будет выведена информация об успешном завершении создания списка файлов: «All file processed correctly».

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

Получаем список файлов в Total Commander

Получить список файлов в папке можно при помощи популярного файлового менеджера Total Commander. Для этого, открываете в окне программы нужную папку.

В меню «Выделение», сначала выбираете пункт «Выделить всё». После того, как все файлы были выделены, в этом же меню выбираете пункт «Копировать имена в буфер».

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

Вы можете в меню «Выделение» выбрать пункт «Сохранить выделение в файл…». В этом случае, вам нужно будет дать имя файлу, а затем сохранить его в формате «TXT».

Сохранение списка файлов в папке в браузере

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

Далее можете просто скопировать список файлов в текстовый редактор, сохранить список в HTML файл, или сохранить в PDF при помощи виртуального принтера.

Выводы статьи

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

3 способа получить список файлов в папке (видео)

Источник: vellisa.ru

Список файлов на листе Excel

В Excel существуют неочевидная возможность вывести на лист список имен файлов из заданной папки. [1] Способ связан с использованием макрофункции (подробнее см. Функция Получить.Ячейку). Этот метод существенно проще написания макроса VBA. Начните с создания именованной формулы. Пройдите по меню Формулы –> Определенные имена –> Присвоить имя, и в окне Создание имени введите данные, как указано на рисунке:

Рис. 1. Диалоговое окно создания именованной формулы

Скачать заметку в формате Word или pdf, примеры в формате Excel (с макросами)

Обратите внимание: функция =ФАЙЛЫ(Лист1!$A$1) не относится к стандартным функциям рабочего листа. Это макрофункция, написанная в старом XLM-стиле и предназначенная для использования на специальном листе макросов. Она принимает один аргумент – путь к каталогу и описание файла (или файловый шаблон) и возвращает массив имен файлов, содержащихся в данном каталоге и удовлетворяющих описанию файла (шаблону). Обычные формулы рабочего листа не могут использовать подобные XLM- функции, а определенные имена — могут.

Определив именованную формулу, введите в ячейку А1 путь к каталогу и описание файла. Например, D:Dropbox!Сайт7_Библиотека*.*

Следующая формула отобразит первый найденный файл: =ИНДЕКС(Список;1). Если изменить второй аргумент на 2, то будет отображен второй найденный файл и т.д. Следующая формула, введенная в ячейку В1 (рис. 2), и скопированная вдоль столбца, позволит вывести все файлы: =ИНДЕКС(Список;СТРОКА()).

Рис. 2. Использование xlm-макрофункции в именованной формуле позволяет создать на листе список имен файлов

Функция СТРОКА, используемая здесь, генерирует ряд последовательных натуральных чисел: 1, 2, 3 и т.д. Эти числа используются в качестве второго аргумента функции ИНДЕКС. Когда все файлы будут выведены на лист, формула начнет возвращать ошибку: #ССЫЛКА! Если изменить каталог или описание файла в ячейке А1, то формулы обновляются и выводят имена новых файлов.

Книгу Excel нужно сохранить как файл с поддержкой макросов (в формате *.xlsm).

[1] По материалам книги Джон Уокенбах. Excel 2013. Трюки и советы. – СПб.: Питер, 2014. – С. 44, 45

Источник: baguzin.ru

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

Функция FilenamesCollection предназначена для получения списка файлов из папки, с учётом выбранной глубины поиска в подпапках.

Используется рекурсивный перебор папок, до заданного уровня вложенности.
В процессе перебора папок, пути у найденным файлам помещаются в коллекцию (объект типа Collection) для последующего перебора.

К статье прикреплено 2 примера файла с макросами на основе этой функции:

  • Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки)
  • Пример в файле FilenamesCollectionEx.xls более функционален – он, помимо списка файлов из папки, отображает размер файла, и дату его создания, а также формирует в ячейках гиперссылки на найденные файлы.
    Вывод списка производится на лист запуска, параметры поиска файлов задаются в ячейках листа (см. скриншот)

Смотрите также расширенную версию макроса на базе этой функции:

Макрос FolderStructure выводит в таблицу Excel список файлов и подпапок с отображением структуры (вложенности файлов и подпапок)

ПРИМЕЧАНИЕ: Если вы выводите на лист список имен файлов картинок (изображений), то при помощи этой надстройки вы сможете вставить сами картинки в ячейки соседнего столбца (или в примечания к этим ячейкам)

‘ Пример использования функции в макросе:

Этот код позволяет осуществить поиск нужных файлов в выбранной папке (включая подпапки), и выводит полученный список файлов на лист книги Excel:

Ещё один пример использования:

PS: Найти подходящие имена файлов в коллекции можно при помощи следующей функции:

Вложение Размер Загрузки Последняя загрузка
FilenamesCollection.xls 35 КБ 14075 6 часов 44 минуты назад
FilenamesCollectionEx.xls 56 КБ 13460 4 часа 13 минут назад
  • 245241 просмотр

Комментарии

Игорь, подскажите, а можно ли в файл FilenamesCollectionEx.xls добавить маску имени подпапки, в которой производить поиск? Ситуация: файл с одинаковм именем может лежать в подпапках с разными именами. Я точно знаю, что нужная мне версия должна лежать в определенной подпапке. И проверять таким образом только их?

Так вроде и то и другое выводится
Код открыт ведь, — поменяйте как вам надо, если лишний столбец мешает.

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

Отбой, разобрался. Виноват оказался не этот макрос, а тот, который его результаты использовал. Мораль — люди, не юзайте Dir, если вам нужно что-то сделать с папкой, к которой он обращается.

В моём макросе нет MoveFolder – так что мой макрос точно не виноват в вашей проблеме.
Проблема – либо в неверном использовании MoveFolder (не то или не туда перемещаете), либо нет прав доступа на перемещение в заданное место.

Игорь, всё это прекрасно. Непонятно только, что нужно сделать с Вашим макросом, чтобы после его вызова с папкой можно было бы ещё и что-нибудь сделать, например, переместить. Сейчас после вызова FSO.MoveFolder вылетает с ошибкой Access denied. Проверено, виноват именно Ваш макрос — если закомментировать ТОЛЬКО его вызов, FSO.MoveFolder отрабатывает нормально.

Спасибо, ОГРОМНОЕ.
Выручайте ребята! макрос в целом отличный, но для моих целе нужно немного переделать.
Нужно чтоб все файлы находящиеся в каждой папке были в одной ячейке через разделитель ( | )
Например:
C:images4-20161032g.jpg|C:images4-20161033g.jpg|C:images4-20161033g.jpg

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

Добрый день!
Скажите, пожалуйста, сделали ли вы макрос для Александра?
Если да, то за сколько его можно приобрести?
Если нет, то какие сроки выполнения?
Спасибо!

Напишите на почту стоимость и сроки выполнения

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

Здравствуйте, Макрос хороший. Всё отлично выводит. Но как сделать дерево? Имеется несколько папок, далее нажимаешь на папку или плюс или еще что-то, она открывается, появляется подпапки, опять жмешь на подпапку появляются подпапки и т.д.

Спасибо, отличный макрос

В ответ на:
Андрей, 15 Мар 2018 – 15:13.#3
Добрый день.
файл 148 знаков (рус.буквы) не обрабатывается,
и сам файл на сервере (если файл на раб.столе то все работает)
какая максимальная длина имени и можно-ли ее обойти.

Ограничение на полное имя файла, включая расширение – 259 символов. Соответственно, все файлы, имеющие более длинное имя при выполнении
Set curfold = FSO.GetFolder(FolderPath)
будут проигнорированы. Тестировал на EX2010, W7 и MSServer 2008. У меня из 28 (curfold.Соunt) файлов реально в коллекции только 15 (curfold.items(1). curfold.items(15))

А как сделать макрос чтобы он мне показал только пустые папки?

Ограничений по длине имени файла, вроде как, нет (по крайней мере, за много лет использования этого кода на тысячах компов, с проблемами не сталкивался)

Добрый день.
файл 148 знаков (рус.буквы) не обрабатывается,
и сам файл на сервере (если файл на раб.столе то все работает)
какая максимальная длина имени и можно-ли ее обойти.

Адаптировал к access – все работает, спасибо, очень помогло

Ринат, посмотрите макрос обработки файлов из папки.
Там выводится диалоговое окно папки, и обрабатываются все файлы в ней (независимо от имён файлов)

Добрый день!
Такой вопрос, в отделе каждый месяц сотрудник ведет отчет по своей работе в табличной форме в ексель каждый в своем файле, а начальству необходимо данные отчеты ввести в свою итоговую таблицу для себя, то есть скопировать данные отчетов с файлов каждого сотрудника в свой отдельный файл. Я создал макрос, для скопирования данных с файлов каждого сотрудника в таблицу файла начальству указывая путь к каждому файлу. Но при этом возникает определенные неудобства, каждый месяц нужно пути к файлам прописывать заново, так как на следующий месяц создаются новые файлы по отчетам, и пути к ним необходимо обновлять. Подскажите пожалуйста, как можно сделать так, чтоб пути к файлам привязывались не по конкретному расположению файла, а например указыванием месяца и года можно было сформировать единый отчет на определенный месяц. Спасибо заранее!

Большое спасибо автору! Список использую для каталогизации архива сканов документов.

Да, можем сделать такой макрос под заказ.
Минимальная стоимость заказа 1500 руб.

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

или например на основе Вашего FilenamesCollectionEx.xls нашел все файлы на диске/папке нужные -нажимаешь на файл и ты нужен выбрать ячейку куда вписать имя файла
заранее спасибо

У меня почему-то размер файла в байтах выводится абсолютно иной, иногда даже с отрицательным значением.
Пример:
1.вес файла 3 840 327 Кб или 3,66 Гб, а таблица выдает “-362 472 675”
2.вес файла 5 082 087 Кб или 4,84 Гб, таблица выдает “909 089 137”

Василий, да, можно добавить.
Пример код можете здесь посмотреть:
http://excelvba.ru/code/MCI

Добрый день! Подскажите, возможно ли добавить столбцы “продолжительность” и “ширина кадра”, которые имеются в данных файлов?

Здравствуйте, Елизавета.
Причин может быть несколько, навскидку:
– проблемный файл, или файл, к которому у вас нет доступа (ошибка 53 – файл не найден)
– слишком длинное имя папки (много уровней вложенности) и/или файла
– сбой в файловой системе
– ошибка в макросе (что-то в коде не учтено)

Техподдержка по бесплатным макросам не предоставляется
Если готовы оплатить помощь, – звоните в скайп, могу подключиться к вашему компу и всё исправить.

Игорь, огромное вам спасибо за эту работу!
Несколько лет использую ваш файл для классификации фильмов, но пару недель назад почему-то он перестал работать. Никакой критичности в этом нет, т.к. главное исправила благодаря обсуждениям тут, но мне непонятно и жутко интересно, почему так происходит. Может, это связано с активацией офиса(примерно в то же время было)? Офис 10й.
У меня 2 вкладки в этом файле, обновляю список на 2й, и затем новые позиции копирую в первую (накапливаю). При обновлении списка, после 60-70 позиций, макрос останавливается и сообщает об ошибке Run-time error 53 со сслыкой на строку ДатаСоздания = FileDateTime(ПутьКФайлу). Дело не файле, т.к. его удаление не помогло. Я добавила в скрипт “On Error Resume Next”, список обновляется до конца, но перестают запускаться фильмы по гиперссылке в 1й вкладке “не удается открыть указанный файл” (во 2й работают), хотя файл и макросы одни и те же. Знаете, в чем может быть причина?

Источник: excelvba.ru

Список файлов в папке

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

  • перечислить в приложении к договору на проведение тренинга список файлов из раздаточных материалов для особо щепетильных юристов в некоторых компаниях
  • создать список файлов для ТЗ проекта
  • сравнить содержимое папок (оригинал и бэкап, например)

Для реализации подобной задачи можно использовать несколько способов.

Способ 1. Скелет из шкафа – функция ФАЙЛЫ

Этот способ использует древнюю функцию ФАЙЛЫ (FILES) , оставшуюся в Microsoft Excel с далеких девяностых. Вы не найдете эту функцию в общем списке функций, но для совместимости, она всё ещё остаётся внутри движка Excel, и мы вполне можем её использовать.

1. В любую ячейку листа (например, в А1) введём путь к папке, список файлов из которой мы хотим получить.

Обратите внимание, что путь должен оканчиваться шаблоном со звездочками:

  • *.* – любые файлы
  • *.xlsx – книги Excel (только с расширением xlsx)
  • *.xl* – любые файлы Excel
  • *отчет* – файлы, содержащие слово отчет в названии

2. Создадим именованный диапазон с помощью вкладки Формулы – далее кнопка Диспетчер имен – Создать (Formulas – Names Manger – Create) . В открывшемся окне введем любое имя без пробелов (например Мои_файлы) и в поле диапазона выражение:

После нажатия на ОК будет создан именованный диапазон с именем Мои_файлы, где хранится список всех файлов из указанной в А1 папки. Останется их оттуда только извлечь.

3. Чтобы извлечь имена отдельных файлов из созданной переменной, используем функцию ИНДЕКС (INDEX) , которая в Excel вытаскивает данные из массива по их номеру:

Если лениво делать отдельный столбец с нумерацией, то можно воспользоваться костылем в виде функции СТРОКИ (ROWS) , которая будет подсчитывать количество заполненных строк с начала списка автоматически:

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

Важное примечание : формально функция ФАЙЛЫ относится к макро-функциям, поэтому необходимо будет сохранить ваш файл в формате с поддержкой макросов (xlsm или xlsb).

Способ 2. Готовый макрос для ленивых

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

Для добавления макроса в вашу книгу нажмите сочетание клавиш Alt + F11 , или кнопку Visual Basic на вкладке Разработчик (Developer) , в открывшемся окне редактора Visual Basic вставьте новый модуль через меню Insert – Module и скопируйте туда текст этого макроса:

Для запуска макроса нажмите сочетание клавиш Alt + F8 ,или кнопку Макросы (Macros) на вкладке Разработчик (Developer) , выберите наш макрос FileList и нажмите кнопку Выполнить (Run) . В диалоговом окне выберите любую папку или диск и – вуаля!

Если захотите, чтобы вместо пути к файлу в столбце B выводилась живая гиперссылка, то замените 52-ю строку

Cells(r, 2).Formula = FileItem.Path

Cells(r, 2).Formula = “=HYPERLINK(“”” & FileItem.Path & “””)”

Способ 3. Мощь и красота – надстройка Power Query

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

Если у вас Excel 2016 или новее, то Power Query уже встроена в Excel по умолчанию, поэтому просто на вкладке Данные выберите команду Создать запрос / Получить данные – Из файла – Из папки (Create Query / Get Data – From file – From folder) . Если у вас Excel 2010-2013, то Power Query нужно будет скачать с сайта Microsoft и установить как отдельную надстройку и она появится у вас в Excel в виде отдельной вкладки Power Query. На ней будет аналогичная кнопка Из файла – Из папки (From file – From folder) .

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

Если внешний вид списка вас устраивает, то можно смело жать внизу кнопку Загрузить (Load) , чтобы залить эти данные на новый лист. Если же хочется дополнительно обработать список (удалить лишние столбцы, отобрать только нужные файлы и т.п.), то нужно выбрать команду Изменить / Преобразовать данные (Edit / Transform Data).

Поверх окна Excel откроется окно редактора Power Query, где мы увидим список всех наших файлов в виде таблицы:

Дальше возможны несколько вариантов:

    Если нужны только файлы определенного типа, то их можно легко отобрать с помощью фильтра по столбцу Extension:

Аналогичным образом фильтрами по столбцам Date accessed, Date modified или Date created можно отобрать файлы за нужный период (например, созданные только за последний месяц и т.п.):

Если нужно получить данные не из всех папок, то фильтруем по столбцу Folder Path, чтобы оставить только те строки, где путь содержит/не содержит нужные имена папок:

  • Там же можно выполнить сортировку файлов по любому столбцу, если требуется.
  • После того, как необходимые файлы отобраны, можно смело удалить ненужные столбцы, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить (Remove column ) . Это, кстати, уже никак не повлияет на фильтрацию или сортировку нашего списка:

    Если в будущем планируется подсчитывать количество файлов в каждой папке (например, для контроля поступивших заявок или подсчета статистики по заявкам), то имеет смысл дополнительно сделать ещё пару действий:

    • Щелкните правой кнопкой мыши по столбцу Folder Path и выберите команду Дублировать столбец (Duplicate Column) .
    • Выделите скопированный столбец и на вкладке Преобразование (Transform) выберите Разделить столбец – По разделителю (Split Column – By delimiter)

    Мы получим рядом с нашими данными еще несколько столбцов, где будут продублированы имена вложенных папок – это пригодится нам чуть позже для подсчета статистики с помощью сводной таблицы:

    Получившиеся столбцы можно переименовать (Диск, Папка1, Папка2 и т.д.), просто щёлкнув дважды по заголовку каждого.

    И, наконец, когда список готов, то его можно выгрузить на лист с помощью команды Главная – Закрыть и загрузить – Закрыть и загрузить в. (Home – Close & Load – Close & Load to. ) :

    И, само-собой, теперь можно построить по нашей таблице сводную (вкладка Вставка – Сводная таблица), чтобы легко подсчитать количество файлов в каждой папке:

    Дополнительным бонусом можно сделать еще один столбец с функцией ГИПЕРССЫЛКА (HYPERLINK) , которая создаст красивые стрелочки-ссылки для моментального перехода к каждому файлу:

    Мелочь, а приятно 🙂

    И вдвойне приятно, что в будущем, при изменении содержимого исходной папки, достаточно будет просто щелкнуть мышью по нашей таблице и выбрать команду Обновить (Refresh) – и Power Query выполнит всю цепочку запрограммированных нами единожды действий уже автоматически, отобразив все изменения в составе папки.

    Источник: www.planetaexcel.ru

    Просмотреть все файлы в папке

    Иногда необходимо проделать однотипные операции с несколькими файлами, расположенными в одной папке. Можно открывать каждый по очереди:
    Workbooks.Open “C:Новая папкаКнига1.xlsx”
    Workbooks.Open “C:Новая папкаКнига2.xlsx”
    и т.д.
    Но если файлов много и все с разными именами, то это не очень практично и уж точно не компактно. А т.к. немногие начинающие могут сразу найти желаемое, я решил выложить код, который перебирает все файлы в папке и открывает их:

    Sub Get_All_File_from_Folder() Dim sFolder As String, sFiles As String ‘диалог запроса выбора папки с файлами With Application.FileDialog(msoFileDialogFolderPicker) If .Show = False Then Exit Sub sFolder = .SelectedItems(1) End With sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, “”, Application.PathSeparator) ‘отключаем обновление экрана, чтобы наши действия не мелькали Application.ScreenUpdating = False sFiles = Dir(sFolder & “*.xls*”) Do While sFiles <> “” ‘открываем книгу Workbooks.Open sFolder & sFiles ‘действия с файлом ‘Запишем на первый лист книги в ячейку А1 – www.excel-vba.ru ActiveWorkbook.Sheets(1).Range(“A1”).Value = “www.excel-vba.ru” ‘Закрываем книгу с сохранением изменений ActiveWorkbook.Close True ‘если поставить False – книга будет закрыта без сохранения sFiles = Dir Loop ‘возвращаем ранее отключенное обновление экрана Application.ScreenUpdating = True End Sub

    sFiles = Dir(sFolder & “*.xls*”) – Строка отвечает за тип перебираемых файлов. В примере будут просмотрены любые файлы Excel. Звездочка на конце означает любой символ или набор символов. Т.е. если указать без неё – “*.xls”, то будут просмотрены только файлы с расширением xls, а если указать xlsx – то файлы с расширением xlsx и никакие другие.
    Если хотите перебрать файлы других форматов, а не Excel, то просто замените “*.xls” на нужное расширение. Например “*.doc”. Также, если хотите собрать только файлы с определенными символами/словами в имени, то можно указать так: sFiles = Dir(sFolder & “*отчет*.xls*”) . Будут просмотрены все файлы, содержащие в имени слово “отчет”(например “отчет за июнь.xls”, “отчет за июль.xls”, “сводный отчет.xls” и т.п.).

    Но есть и еще одна проблема: что если необходимо открыть файлы не только в указанной папке, но и во всех её подпапках? Указанные выше код не подойдет в данной ситуации. В версиях Excel 2003 и младше это решалось с помощью метода .FileSearch, но в старших версиях данный метод по каким-то причинам был заблокирован разработчиками Microsoft. И осталось действовать только через рекурсивный метод перебора папок. Ниже приведен код, который открывает все файлы Excel в указанной папке, включая все подпапки:

    Option Explicit Dim objFSO As Object, objFolder As Object, objFile As Object Sub Get_All_File_from_SubFolders() Dim sFolder As String With Application.FileDialog(msoFileDialogFolderPicker) If .Show = False Then Exit Sub sFolder = .SelectedItems(1) End With sFolder = sFolder & IIf(Right(sFolder, 1) = Application.PathSeparator, “”, Application.PathSeparator) Application.ScreenUpdating = False Set objFSO = CreateObject(“Scripting.FileSystemObject”) GetSubFolders sFolder Set objFolder = Nothing Set objFSO = Nothing Application.ScreenUpdating = True End Sub Private Sub GetSubFolders(sPath) Dim sPathSeparator As String, sObjName As String Set objFolder = objFSO.GetFolder(sPath) For Each objFile In objFolder.Files If Replace(objFile.Name, objFSO.GetBaseName(objFile), “”) Like “.xls*” Then ‘открываем книгу Workbooks.Open sPath & objFile.Name ‘действия с файлом ‘Запишем на первый лист книги в ячейку А1 – www.excel-vba.ru ActiveWorkbook.Sheets(1).Range(“A1”).Value = “www.excel-vba.ru” ActiveWorkbook.Close True End If Next For Each objFolder In objFolder.SubFolders GetSubFolders objFolder.Path & Application.PathSeparator Next End Sub

    If Replace(objFile.Name, objFSO.GetBaseName(objFile), “”) Like “.xls*” Then

    Строка отвечает за тип перебираемых файлов. В примере будут просмотрены любые файлы Excel. Звездочка на конце означает любой символ или набор символов. Т.е. если указать без неё – “*.xls”, то будут просмотрены только файлы с расширением xls, а если указать xlsx – то файлы с расширением xlsx и никакие другие.
    Если добавить условие: If objFSO.GetBaseName(objFile) Like “*книга*” Then
    то будут обработаны файлы, которые в имени содержать слово “книга”. При этом регистр букв имеет значение. Т.е. если файл содержит в имени слово “Книга”, то он не будет обработан.
    Думаю теперь Вы легко сможете проделать необходимые операции с множеством файлов.

    Tips_Macro_Get_All_Files_from_Folder.xls (61,5 KiB, 6 249 скачиваний)

    В примере я закомментировал строки, открывающие файл и вносящие изменения в ячейку А1 и заменил это созданием массива имен всех файлов в папках и подпапках. По окончании имена всех файлов заносятся в столбец “А”. Сделано для того, чтобы Вы случайно не повредили информацию в файлах.

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

    Источник: www.excel-vba.ru