Как скопировать названия файлов из папки в эксель

Обновлено: 04.07.2024

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

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

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

Дело за малым. Надо каким-то образом забрать данные из текстового файла в файл электронной таблицы.

Как скопировать имена файлов из выбранной папки, директории или каталога в MS Excel

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

Этот файл с именем list.txt будет содержать все имена файлов выбранного каталога, папки или директории.

Для импорта откроем файл с именем list.txt в MS Excel командой Открыть. Появится специальное окно как на Рис.1.

Рис.1. Окно мастера текстов, предназначенное для импорта данных из текстовых файлов

Если кодировка не установилась по умолчанию, то вручную выбираем нужную кодировку, как это показано на рисунках Рис.1. и Рис.2.

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

Рис.2. Окно мастера текстов, настроенного на импорт с 8 строки

Выбрав начальную строку, фиксированную ширину и требуемую кодировку, нажимаем кнопку Далее и Мастер переходит к окну Разбора данных.

На Рис.3. показан разбор данных при импорте каталога с первой строки, а на Рис.4. показан разбор данных при импорте каталога с 8 строки.

Рис.3. Разбор данных при импорте с 1 строки

В действительности можно импортировать данные и с первой строки, а затем удалить ненужные средствами электронной таблицы. Однако элегантнее (и разумнее при больших объёмах информации) забирать из файла только нужные данные.

Рис.4. Разбор данных при импорте с 8 строки

Как видим на Рис.4. импорт происходит с нужной строки и излишние данные не импортируются, а, следовательно, нет нужды в дополнительной обработке данных после импорта.

На рисунках Рис.5. и Рис.6. показано, как можно задать формат данных для того или иного столбца. В данном примере задаётся формат данных первого столбца. Это формат Даты в виде ДД.ММ.ГГГГ

Рис.5. Задание формата даты для первого столбца при импорте с 1 строки

Рис.6. Задание формата даты для первого столбца при импорте с 8 строки

По окончании импорта нажимаем кнопку Готово. В результате получаем таблицу с нужными данными, а именно – таблицу с именами файлов (размерами, датой и временем создания) из выбранного каталога см.Рис.7.

Рис.7. Таблица с именами файлов выбранного каталога или папки

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

Самый простой способ создания списка файлов, расположенных в папке.

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

Шаг 1. Войти в папку список файлов из которой вы хотите сформировать.

Шаг 2. Кликнуть правой кнопкой по пустому полю в папке (не по файлу)

Создать текстовый документ

Шаг 3. Из выпадающего мену выбрать пункт «Создать»>> «Текстовый документ»

Шаг 4. Открыть появившийся текстовый документ и записать в него три строчки с кодом:

Шаг 5. Закрыть с сохранением документ.

Закрыть документ с сохранением

Шаг 6. Переименовать расширение файла с «TXT» на «BAT» и нажать кнопку «Enter»

Переименовать файл в BAT

Шаг 7. Запустить полученный файл.

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

Готовый список файлов

Перенос списка в программу Excel.

Шаг 1. Открыть текстовый документ.

Шаг 2. Выделить левой кнопкой мыши список.

Выделение списка

Шаг 3. Нажав правой кнопкой мыши по выделению выбрать пункт «Копировать»

Копирование перечня

Шаг 4. Открыть документ Excel.

Вставка в Excel

Шаг 5 Кликнуть правой кнопкой мыши в любую ячейку и выбрать из выпадающего меню пункт «Вставить»

Резултат копирования списка

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

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

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

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

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

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

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

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

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

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

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

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

=ИНДЕКС(Мои_файлы; ЧСТРОК($B$3:B3) )

= ЕСЛИОШИБКА( ИНДЕКС(Мои_файлы; ЧСТРОК($B$3:B3)) ; "")

Важное примечание : формально функция ФАЙЛЫ относится к макро-функциям, поэтому необходимо будет сохранить ваш файл в формате с поддержкой макросов (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, где мы увидим список всех наших файлов в виде таблицы:

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

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

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

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

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

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

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

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

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

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

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

Надстройка, позволяющая загрузить из выбранной папки список файлов на лист Excel.

Автор: VictorM

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

Основой для надстройки загрузки списка файлов послужила функция FilenamesCollection

Вы также можете посмотреть другие примеры загрузки списка файлов на лист Excel

Для запуска главной формы надстройки запустите прикреплённый к статье файл,
и нажмите в Excel комбинацию клавиш Ctrl + Alt + S

Далее, выберите папку, в которой будет производиться поиск файлов,
задайте маску поиска (если маска не задана, в результат будут выведены все файлы в папке),
и укажите глубину поиска в подпапках (если глубина = 0, производится поиск во всех вложенных подпапках)

Не выводит в список файлы, в названии которых есть символы ° и Ø, функция Dir(ПутьКФайлу) возвращает пустое значение, хотя значение переменной ПутьКФайлу не пустое и если присвоить переменной ИмяФайла значение переменной ПутьКФайлу в списке отображаются пути с правильными названиями файлов.

Подскажите начинающему познавать excel.

Как скачать надстройку и куда ее сохранить, чтобы ей пользоваться.

Ребят, помогите (дуб я в макросах): есть файл с тучей гиперссылок на папки и PDF файлы, но пришлось всю базу проектов перелопатить и все ссылки похерились.
Руками исправлять ссылки (не формульные =ГИПЕРССЫЛКА()) я умру. Для вас пара пустяков написать макрос, т.к. все эти фичи есть в разных макросах, но я даже объединить их не могу. Суть: брать существующую ссылку, откинуть всё до последнего знака "\" (т.к. ссылки не только на файлы но и на папки), по оставшемуся делать поиск в заданной папке и заменять ссылку на найденное.
Пример:
старая ссылка - 3G/0001 АТС-35/БС/2-34-0001-БС (АТС-35).pdf
берем из неё - 2-34-0001-БС(АТС-35).pdf
ищем в папке d:\Мои документы\!ПРОЕКТЫ\
находится - d:\Мои документы\!ПРОЕКТЫ\001_АТС-35\2-34-0001 АТС-35\БС\2-34-0001-БС (АТС-35).pdf
меняем ссылку на - 001_АТС-35\2-34-0001 АТС-35\БС\2-34-0001-БС (АТС-35).pdf

Ведь понимаю, что просто, но реализовать не могу (

надстройка перестала работать в excel 2010. имена файлов, дата создания и размерфайла пустые значения. :( а ведь в 2003 все работало)

Ребята спасибо за надстроечку :)
Очень пригодилоась для естренной разовой работы с перебором каталогов. Самому уже поздно что либо программировать.
Еще раз СПАСИБО.

Игорь, доброго времени суток.
Посмотрел Вашу надстройку.
Есть баг: в коде формы в процедуре Private Sub cb_очистить_Click()
в последней строке опечатка : вместо Worksheets.Count написано woksheets.Count , что, естественно, вызывает ошибку, но из-за On Error Resume Next она игнорируется и в результате после удаления листа "Поиск файлов" ещё и закрывается книга, в которую его вставила надстройка.
Есть вопросы:
1. Не понятно зачем сделан двухступенчатый вызов cb_выбрать_Click -> FilenamesCollection -> GetAllFileNamesUsingFSO когда вполне можно было напрямую вызывать cb_выбрать_Click -> GetAllFileNamesUsingFSO, т.к. в FilenamesCollection практически ничего не делается?
Но это практически не тормозит работу.
2. А вот то, что Вы выводите на лист не "в одно движение" из массива, а по группам ячеек при большом числе файлов сильно тормозит работу.
3. DoEvents наверное следует поставить не в код формы, а в сам код рекурсивной функции. Иначе при долгих рекурсиях прервать их будет невозможно.

P.S. Я сделал свою процедуру "по мотивам" этой.
Но вместо коллекции использовал словарь (из него просто "одним махом" можно считать массив .Items )
А в сами элементы записывал не пути к найденным файлам, а массивы-строки, сформированные из параметров файла: (0)-№№ (= количеству элементов словаря+1), (1)-Name, (2)-Path, (3)-DateCreated, (4)-Size, (5)-DateLastModified

Получилось вполне элегантно. Спасибо за пример рекурсии по FileSystemObject

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

Вообще-то типичное положение вещей. Возникнуть оно может из-за неоднозначных имен процедур и функций.
Просто из-за того, что добавляя модули и формы из разных проектов, надстроек могут продублироваться эти самые имена.
В частности, у меня была такая ситуация, когда я делал собственную надстройку из "кусков" кодов и надстроек автора сайта.
Настоятельно рекомендуется, при таком вот добавлении, каждый раз запускать Debug - Compile VBAProject.

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

И вообще, кому это надо - мне, или вам?
Если вам - то почему экономите слова (даже не говоря номер и описание ошибки)?
Мне надоело играть в телепата, и строить предположения, не видя файла.
Обращайтесь на форумы по Excel, выкладывайте там свою надстройку, - спецы помогут разобраться в причинах.

Excel

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

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

Импортируйте и получите список имен файлов в лист Excel

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

Перейдите в каталог или папку в проводнике Windows, который вы хотите отслеживать. Здесь я хочу отслеживать мои файлы и папки папки «Документы». Скопируйте путь этого каталога.

Теперь откройте любой веб-браузер по вашему выбору и вставьте скопированный путь (путь к папке, которую вы только что скопировали на предыдущем шаге) в адресную строку браузера. Я использовал Google Chrome здесь. Просто добавьте к URL-адресу префикс file:///, и его содержимое будет отображаться на веб-странице.

Чтобы сохранить эту веб-страницу как автономную копию, нажмите CTRL + S или щелкните правой кнопкой мыши веб-страницу и выберите «Сохранить страницу как». Выберите место назначения, дайте ему имя и сохраните веб-страницу.

Теперь с помощью проводника Windows перейдите в папку, в которой вы сохранили автономную веб-страницу, и скопируйте путь. Чтобы открыть лист Excel, нажмите вкладку Данные и нажмите Из Интернета. Откроется окно, в адресной строке вставьте скопированный путь и нажмите кнопку «Перейти». Он загрузит все содержимое веб-страницы.

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

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

Заключение

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

Читайте также: