Поиск по эксель файлам в папке
Функция FilenamesCollection предназначена для получения списка файлов из папки, с учётом выбранной глубины поиска в подпапках.
Используется рекурсивный перебор папок, до заданного уровня вложенности.
В процессе перебора папок, пути у найденным файлам помещаются в коллекцию (объект типа Collection) для последующего перебора.
К статье прикреплено 2 примера файла с макросами на основе этой функции:
- Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки)
- Пример в файле FilenamesCollectionEx.xls более функционален - он, помимо списка файлов из папки, отображает размер файла, и дату его создания, а также формирует в ячейках гиперссылки на найденные файлы.
Вывод списка производится на лист запуска, параметры поиска файлов задаются в ячейках листа (см. скриншот)
Смотрите также расширенную версию макроса на базе этой функции:
Макрос FolderStructure выводит в таблицу Excel список файлов и подпапок с отображением структуры (вложенности файлов и подпапок)
ПРИМЕЧАНИЕ: Если вы выводите на лист список имен файлов картинок (изображений), то при помощи этой надстройки вы сможете вставить сами картинки в ячейки соседнего столбца (или в примечания к этим ячейкам)
Внимание: если требуется, чтобы поиск не зависел от регистра символов в маске файла
(к примеру, обнаруживались не только файлы .txt, но и .TXT и .Txt),
поставьте первой строкой в модуле директиву Option Compare Text
' Пример использования функции в макросе:
Этот код позволяет осуществить поиск нужных файлов в выбранной папке (включая подпапки), и выводит полученный список файлов на лист книги Excel:
Ещё один пример использования:
PS: Найти подходящие имена файлов в коллекции можно при помощи следующей функции:
Комментарии
Добрый день, а есть возможность применить свой макрос к полученному списку файлов?
Александр, нет таких полей у файлов произвольного формата, — потому, никак.
Посоветуйте пожалуйста как с помощью FSO получить доступ к полям Теги и Комментарии. Спасибо Alexander A. Rylov
Михаил, найдите в верхней части кода строку Option Explicit
и удалите её (эта строка требует объявлять переменные)
Подскажите. Почему excel может ругаться на
Set FSO = CreateObject("Scripting.FileSystemObject") ' создаём экземпляр FileSystemObject
Пишет что переменная не объявлена/не определена
Нужно выше дописать
Dim FSO As Object?
Или в настройках excel 2016 что-то не так? Притом ругается на все не объявленные переменные.
А переменные типа Filename$ вообще не воспринимает как переменные. В чем может быть дело?
Гуглинг пока не помог.
Здравствуйте.
DoEvents никак не влияет на правильность работы (и не может повлиять)
А количество активных гиперссылок на листе Excel ограничено, — никак не сделать, чтобы на одном листе было более 50 или 65 тысяч АКТИВНЫХ гиперссылок.
Доброго времени суток. Огромное спасибо за программу!
Добавлю от себя и задам вопрос.
При использовании "DoEvents" программа может не правильно работать, в том числе выводить не все значения. Я ее закомментировал.
При привышении 65532 строк гиперссылки прекращают формироваться. Как можно победить?
Здравствуйте.
Под заказ что угодно могу сделать (платно)
Здравствуйте. Для примера из файла "FilenamesCollectionEx.xls" - можете сделать, чтобы выводимый на лист Excel список файлов был отсортирован по размеру(по уменьшению)?
Здравствуйте.
Могу сделать под заказ
Оформляйте заказ на сайте, и обязательно прикрепляйте пример файла с примером результата.
Здравствуйте! Меня тоже интересует макрос по поиску файлов. Можете сделать так что бы в ячейках к примеру A1 задать имя файла, A2 задать тип файла и A3 путь к папке?
Огромное Вам спасибо! Столько времени мне съэкономили.
СПА-СИ-БО! :-)
Спасибо. Очень полезная вещь!
Добрый день
В случае если в именах файлов встречаются нестандартные символы (допустимые в Win) макрос выдает ошибку
Ошибка в строке ДатаСоздания = FileDateTime(ПутьКФайлу)
Можно добавить onError Resume Next но это пропуск ошибки будет а размер файла не будет определен. Есть ли варианты сделать определение размера файлов и для таких файлов тоже?
Игорь, подскажите, а можно ли в файл FilenamesCollectionEx.xls добавить маску имени подпапки, в которой производить поиск? Ситуация: файл с одинаковм именем может лежать в подпапках с разными именами. Я точно знаю, что нужная мне версия должна лежать в определенной подпапке. И проверять таким образом только их?
Так вроде и то и другое выводится
Код открыт ведь, — поменяйте как вам надо, если лишний столбец мешает.
Возможно я слепой или плохо читаю, но я не увидел что-то подобное в коментах. Поэтому мой вопрос следующий: можно как-то сделать так, чтобы выводило только название файла а не весь путь?
Отбой, разобрался. Виноват оказался не этот макрос, а тот, который его результаты использовал. Мораль — люди, не юзайте Dir, если вам нужно что-то сделать с папкой, к которой он обращается.
В моём макросе нет MoveFolder - так что мой макрос точно не виноват в вашей проблеме.
Проблема - либо в неверном использовании MoveFolder (не то или не туда перемещаете), либо нет прав доступа на перемещение в заданное место.
Игорь, всё это прекрасно. Непонятно только, что нужно сделать с Вашим макросом, чтобы после его вызова с папкой можно было бы ещё и что-нибудь сделать, например, переместить. Сейчас после вызова FSO.MoveFolder вылетает с ошибкой Access denied. Проверено, виноват именно Ваш макрос — если закомментировать ТОЛЬКО его вызов, FSO.MoveFolder отрабатывает нормально.
Спасибо, ОГРОМНОЕ.
Выручайте ребята! макрос в целом отличный, но для моих целе нужно немного переделать.
Нужно чтоб все файлы находящиеся в каждой папке были в одной ячейке через разделитель ( | )
Например:
C:\images\04-2016\10\32g.jpg|C:\images\04-2016\10\33g.jpg|C:\images\04-2016\10\33g.jpg
Да, сделал.
Высылайте на почту подробное задание (что и как должно выглядеть, для чего это вообще нужно, и т.д.)
Тогда озвучу сроки и стоимость
Добрый день!
Скажите, пожалуйста, сделали ли вы макрос для Александра?
Если да, то за сколько его можно приобрести?
Если нет, то какие сроки выполнения?
Спасибо!
Напишите на почту стоимость и сроки выполнения
Александр, в этом случае нужен более сложный макрос.
Могу сделать под заказ.
Здравствуйте, Макрос хороший. Всё отлично выводит. Но как сделать дерево? Имеется несколько папок, далее нажимаешь на папку или плюс или еще что-то, она открывается, появляется подпапки, опять жмешь на подпапку появляются подпапки и т.д.
Спасибо, отличный макрос
Ограничение на полное имя файла, включая расширение - 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"
Добрый день! Подскажите, возможно ли добавить столбцы "продолжительность" и "ширина кадра", которые имеются в данных файлов?
Здравствуйте, Елизавета.
Причин может быть несколько, навскидку:
- проблемный файл, или файл, к которому у вас нет доступа (ошибка 53 - файл не найден)
- слишком длинное имя папки (много уровней вложенности) и/или файла
- сбой в файловой системе
- ошибка в макросе (что-то в коде не учтено)
Игорь, огромное вам спасибо за эту работу!
Несколько лет использую ваш файл для классификации фильмов, но пару недель назад почему-то он перестал работать. Никакой критичности в этом нет, т.к. главное исправила благодаря обсуждениям тут, но мне непонятно и жутко интересно, почему так происходит. Может, это связано с активацией офиса(примерно в то же время было)? Офис 10й.
У меня 2 вкладки в этом файле, обновляю список на 2й, и затем новые позиции копирую в первую (накапливаю). При обновлении списка, после 60-70 позиций, макрос останавливается и сообщает об ошибке Run-time error 53 со сслыкой на строку ДатаСоздания = FileDateTime(ПутьКФайлу). Дело не файле, т.к. его удаление не помогло. Я добавила в скрипт "On Error Resume Next", список обновляется до конца, но перестают запускаться фильмы по гиперссылке в 1й вкладке "не удается открыть указанный файл" (во 2й работают), хотя файл и макросы одни и те же. Знаете, в чем может быть причина?
Функция FilenamesCollection предназначена для получения списка файлов из папки, с учётом выбранной глубины поиска в подпапках.
Используется рекурсивный перебор папок, до заданного уровня вложенности.
В процессе перебора папок, пути у найденным файлам помещаются в коллекцию (объект типа Collection) для последующего перебора.
К статье прикреплено 2 примера файла с макросами на основе этой функции:
- Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки)
- Пример в файле FilenamesCollectionEx.xls более функционален - он, помимо списка файлов из папки, отображает размер файла, и дату его создания, а также формирует в ячейках гиперссылки на найденные файлы.
Вывод списка производится на лист запуска, параметры поиска файлов задаются в ячейках листа (см. скриншот)
Смотрите также расширенную версию макроса на базе этой функции:
Макрос FolderStructure выводит в таблицу Excel список файлов и подпапок с отображением структуры (вложенности файлов и подпапок)
ПРИМЕЧАНИЕ: Если вы выводите на лист список имен файлов картинок (изображений), то при помощи этой надстройки вы сможете вставить сами картинки в ячейки соседнего столбца (или в примечания к этим ячейкам)
Внимание: если требуется, чтобы поиск не зависел от регистра символов в маске файла
(к примеру, обнаруживались не только файлы .txt, но и .TXT и .Txt),
поставьте первой строкой в модуле директиву Option Compare Text
' Пример использования функции в макросе:
Этот код позволяет осуществить поиск нужных файлов в выбранной папке (включая подпапки), и выводит полученный список файлов на лист книги Excel:
Ещё один пример использования:
PS: Найти подходящие имена файлов в коллекции можно при помощи следующей функции:
Комментарии
Макрос обрабатывает папки с любым количеством файлов, - и десятки тысяч файлов не проблема (не говоря уж о 200 файлах)
Если не работает, - вы что-то не так в коде прописали.
Здравствуйте, Андрей.
С сетевыми путями (UNC) - тоже все работает.
Вот так написал - и получил список файлов
Спасибо. Очень полезная функция. Но, к сожалению, не ищет в сетевых папках без буквы дисковода :-(
Т.е., если папка поиска указана как "\\NetComp1\dir1\dir2" - искать не будет. А не всегда есть возможность присвоить буковку.
Нельзя ли что-нибудь придумать, чтобы работало с сетевыми путями?
С уважением, Андрей.
Спасибо огромное, пользуюсь несколько лет, всё здорово работает,
НО:
обнаружилось неприятное свойство - не работает поиск в сетевых папках, если не назначена буква дисковода. Т.е. "\\Network\dir1\dir2" в пути начала поиска не прокатывает :-(. Нельзя ли что-нибудь "подкрутить", чтобы работало. А нельзя ли из VBA скрипта запускать поиск в стандартном окне Windows (то, что открывается по нажатию "Win+F"). Думается, во многих случаях это был бы идеальный вариант.
С уважением, Андрей.
Олег, надо внимательно все проверять. Была ли создана предварительно папка с нужным именем, правильно ли указан путь к ней (не написано ли "C:\" с русской буквой С, например), в том ли месте в макросе разместили указанные строки и т.д.
Если VBA даешь команду, он ее исполняет, значит не в том месте команда была дана или ошибку где-то допустили.
Антон, спасибо большое за ответ. Я проверил, но при одновременном выводе списка файлов в листе, копирования в указанную папку, тех файлов, которые отобразились в этом листе, не происходит.
Олег, внимательно смотрите приведенные примеры. Там уже всё есть.
' если нужна гиперссылка на файл во втором столбце
ActiveSheet.Hyperlinks.Add Range("b" & Rows.Count).End(xlUp), ПутьКФайлу, "", _
"Открыть файл" & vbNewLine & ИмяФайла
Для копирования файла добавьте в функцию GetAllFileNamesUsingFSO следующие строки
sNewFileName = "С:\папка для копий\Копия " & fil.Name 'имя файла
FileCopy fil, sNewFileName 'копируем файл
При этом папка для копий файлов уже должна быть создана
Возможно ли в этом открывшимся листе Excel, каждому имени файла из списка присвоить гиперссылку на найденный файл?
Подскажите пожалуйста, а как сделать чтобы найденные файлы еще копировались в другую указанную папку?
Отличный сайт, полезные функции. Спасибо автору.
Артем, замените строку
Подскажите, пожалуйста, как сделать, чтобы поиск происходил по диску С:, а не по рабочему столу?
Большое спасибо. Всё очень хорошо работает. Донесено до народа творчески и аккуратно. Главное идеи и подходы, а детали мы уже сами. Удачи.
Здравствуйте, Allanian.
Для вашей задачи макрос нужно дорабатывать, - можем сделать под заказ.
Оформляйте заказ на сайте, прикрепляйте примеры файлов, и подробно описывайте, что и как должно работать.
здравствуйте, подскажите
Есть например, 10 файлов в названии которых написано имя компьютера, и в самом файле на 2 строчке есть это же имя компьютера.
Как можно собрать названия файлов в эксель в 1 столбик или собрать имена компьютеров из 2 строчки в столбик в экселе. Помогите плис. на работе завал вручную переписывать
При обращении к файлам содержащим немецкие символы типа "умлаут" выдает пустую строку "" в поле 2 - "гиперссылка".
Здравствуйте, Владимир.
Да, дописать макрос возможно.
Доброго времени суток ВСЕМ! Подскажите возможно ли дописать макрос, что бы при задании периода, в "Список файлов в папке" попали только файлы "по дате создания". Спасибо.
Спасибо большое завтра попробую на работе проверить.
Роман, ваш макрос будет выглядеть так:
если несколько расширений надо исключить, - то так:
Здравствуйте!
Вопрос у меня такой, как сделать так, чтобы не показывались файлы с определенным разрешением
Выше вы приводили пример как сделать, чтобы не грузились системные файлы, но у меня не хватает ума переделать строчку, чтобы искались файлы не по полному имени, а по расширению.
Заранее спасибо автору.
ИмяФайла = Dir(ПутьКФайлу)
при обращении к файлам типа *.db, *.ini и т.п. выдает пустую строку ""
В данном случае лучше использовать строчку ниже, которая определяет имена любых файлов
ИмяФайла = FSO.GetFileName(ПутьКФайлу)
Никита, я вот сейчас в яндексе написал запрос «VBA дата создания файла»
и, о чудо, по первой же ссылке был нужный код.
Остается только правильно прикрутить этот код к имеющемуся макросу
PS: Если сами не разберетесь, - всегда можно оформить заказ на сайте
Игорь, здравствуйте! Очень полезный макрос, все отлично работает! Но никак не могу разобраться, мне требуется вытащить еще дату создания файла на диске, потому что у вас дата изменения,последнего, на самом-то деле:) Хотел бы вставить строчку вот сюда:
НомерФайла = i
ПутьКФайлу = coll(i)
ИмяФайла = Dir(ПутьКФайлу)
ДатаИзменения = FileDateTime(ПутьКФайлу)'у вас это как ДатаСоздания' было
ДатаСоздания.
РазмерФайла = FileLen(ПутьКФайлу)
Пробовал несколько методов, поискал в интернете, дебагер ошибку выдает постоянно. Количество столбцов в массиве увеличивал конечно же.
Заранее спасибо за помощь!
Игорь, здравствуйте. Написал Вам на e-mail - там все старался пояснить. и файл приложил. Возможно Вы видите другое решение - подскажите. По всем другим нюансам - по электронной почте, а лучше по скайпу. С уважением, Владислав.
Владислав, да мне несложно помочь, - но я не вижу файла с макросом
Исправлять ошибку - 10 секунд, а вот выяснять у вас все подробности - намного больше времени займёт.
Игорь, здравствуйте. Пытался у Вас просить помощи - а Вы сразу за деньги. Но "надежда умирает последней" - применил корректировку указанных строк и при запуске макроса выскочила ошибка "Runtime error '5'" - через "Debug" выкидывает окно макроса с выделением строки откорректированной строки. Поможете?
Заранее благодарю.
Здравствуйте, Оксана
Это уже сложнее, - значение указанного свойства можно извлечь только из тех файлов, где оно есть
(из произвольного файла, например, текстового, или Excel, такие данные не получить)
В файлах Word в свойствах есть такое
Если из открытого файла считывать, - то так:
Доброго дня!!
Вы написали ОЧЕНЬ полезный макрос и от меня Вам БОЛЬШОЕ СПАСИБО.
Я не сильна в написании макросов и поэтому у меня к Вам просьба - подскажите как можно в дополнительной колонке написать данные "Знаков и пробелов" из свойств каждого файла
ошибка - "Sub or function not defined" - возникает потому, что вы скопировали в свой файл только пример использования функции, а саму функцию - нет.
Посмотрите пример в прикреплённом файле - там есть все необходимые макросы и функции
как отключить поиск скрытых файлов таких как Thumbs.db если я просто делаю список файлов?
Возникла точно такая же ошибка - "Sub or function not defined". Скачала Ваш макрос - тоже ошибку выдает. Excel- 2010. В чем же может быть дело?
Прошу прощение за беспокойство, уже решил свою проблему.
Здравствуйте, Сергей
Ошибка из-за неправильного использования функции
Покажите свой кусок кода с поиском файла - подскажу, как написать правильно.
Здравствуйте, спасибо за функцию, мне очень пригодилась.
Я функцию использую для поиска конкретного файла. В случае если искомого названия нет, выскакивает ошибка Invalid procedure call or argument. Возможно ли ее как-то локализовать?
слова "не можете" не сильно профессионально звучат из ваших уст )))
все настройки у мня по вашим рекомендациям
и если не открывает, то не НЕ открываю я а прога не открывает
винда 7-ая, новый ноут
никаких сбоев в офисе за последнее время не обнаружено
говорю то что есть.. остальное лирика
с удовольствием выслушаю поправки, в каких случая такое возможно
давайте по сути
Функция FilenamesCollection успешно работает в сотнях моих макросов, на тысячах компов.
Если вы не можете её правильно использовать, - не надо грешить на мой код)
не знаю как у других, но у меня в папке до 2500 фото имена которых мне нужно вытащить (абсолютно все .jpg), а используя FilenamesCollectionEx.xls находит от силы 20-30 не сильно рабочая штука )))
Работаю в Excel 2007. Нужно, чтобы из конкретной папки (из сети) выводило список всех, находящихся в ней фалов (имена). Файлы в формате .xlsx. Изменил всё, что было необходимо, но выводит только чистый лист с заголовками "№", "Имя файла", "Полный путь".
В чем может быть ошибка?
Спасибо.
P.S. я не особо силён в написании макросов.
И в Word работать будет, - только вывод результатов в документ надо будет делать иначе.
А сама функция, никак не привязана к Excel
А в Ворде Function FilenamesCollection() работать будет? И вообше какому злу понадобилось убирать FILESEARCH из 2007
Нашёл отличную бесплатную программу TagScanner
Там во вкладке List Maker в настройках экспорта нужно выбрать шаблон csv-excel который можно изменить
вставив например следующий код:
$file_name TrackList.csv
$file_notes Excel-friendly comma-separated text
$file_encoding utf-8
$file_writebom 1
$document_open
"№";"путь к файлу";"имя файла";"длительность";"название композиции";"год";"обьём"
$select %_index%,0
"%_counter%";"%filepath%";"%filenameext%";"%_totallength%";"%title%";"%year%";"%_filesize%"
$endselect
"Общее количество файлов: %_totalfiles%"
"Общий обьём файлов: %_totalsize%"
"Общая длительность файлов: %_totallength%"
Пишу это потому что потратил много времени на поиск нужного решения (сканирования и экспорт в csv mp3 файлов).
И может быть кому то это поможет быстрее решить подобный вопрос.
И ещё скажите пожалуйста каким должен быть код чтобы в полном пути к файлу не отображалось само название файла а только названия папок в которых файл находится ?
Это наверно где то в этом месте надо что то изменить.
выводим результаты на лист
For i = 1 To coll.Count ' перебираем все элементы коллекции, содержащей пути к файлам
sh.Range("a" & sh.Rows.Count).End(xlUp).Offset(1).Resize(, 3).Value = _
Array(i, coll(i), Dir(coll(i))) ' выводим на лист очередную строку
DoEvents ' временно передаём управление ОС
Иногда бывает необходимо заполучить на лист 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, где мы увидим список всех наших файлов в виде таблицы:
Дальше возможны несколько вариантов:
-
Если нужны только файлы определенного типа, то их можно легко отобрать с помощью фильтра по столбцу Extension:
После того, как необходимые файлы отобраны, можно смело удалить ненужные столбцы, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить (Remove column ) . Это, кстати, уже никак не повлияет на фильтрацию или сортировку нашего списка:
Если в будущем планируется подсчитывать количество файлов в каждой папке (например, для контроля поступивших заявок или подсчета статистики по заявкам), то имеет смысл дополнительно сделать ещё пару действий:
- Щелкните правой кнопкой мыши по столбцу Folder Path и выберите команду Дублировать столбец (Duplicate Column) .
- Выделите скопированный столбец и на вкладке Преобразование (Transform) выберите Разделить столбец - По разделителю (Split Column - By delimiter)
Мы получим рядом с нашими данными еще несколько столбцов, где будут продублированы имена вложенных папок - это пригодится нам чуть позже для подсчета статистики с помощью сводной таблицы:
Получившиеся столбцы можно переименовать (Диск, Папка1, Папка2 и т.д.), просто щёлкнув дважды по заголовку каждого.
И, наконец, когда список готов, то его можно выгрузить на лист с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close & Load - Close & Load to. ) :
И, само-собой, теперь можно построить по нашей таблице сводную (вкладка Вставка - Сводная таблица), чтобы легко подсчитать количество файлов в каждой папке:
Дополнительным бонусом можно сделать еще один столбец с функцией ГИПЕРССЫЛКА (HYPERLINK) , которая создаст красивые стрелочки-ссылки для моментального перехода к каждому файлу:
Мелочь, а приятно :)
И вдвойне приятно, что в будущем, при изменении содержимого исходной папки, достаточно будет просто щелкнуть мышью по нашей таблице и выбрать команду Обновить (Refresh) - и Power Query выполнит всю цепочку запрограммированных нами единожды действий уже автоматически, отобразив все изменения в составе папки.
Иногда бывает необходимо заполучить на лист 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, где мы увидим список всех наших файлов в виде таблицы:
Дальше возможны несколько вариантов:
-
Если нужны только файлы определенного типа, то их можно легко отобрать с помощью фильтра по столбцу Extension:
После того, как необходимые файлы отобраны, можно смело удалить ненужные столбцы, щелкнув по заголовку столбца правой кнопкой мыши и выбрав команду Удалить (Remove column ) . Это, кстати, уже никак не повлияет на фильтрацию или сортировку нашего списка:
Если в будущем планируется подсчитывать количество файлов в каждой папке (например, для контроля поступивших заявок или подсчета статистики по заявкам), то имеет смысл дополнительно сделать ещё пару действий:
- Щелкните правой кнопкой мыши по столбцу Folder Path и выберите команду Дублировать столбец (Duplicate Column) .
- Выделите скопированный столбец и на вкладке Преобразование (Transform) выберите Разделить столбец - По разделителю (Split Column - By delimiter)
Мы получим рядом с нашими данными еще несколько столбцов, где будут продублированы имена вложенных папок - это пригодится нам чуть позже для подсчета статистики с помощью сводной таблицы:
Получившиеся столбцы можно переименовать (Диск, Папка1, Папка2 и т.д.), просто щёлкнув дважды по заголовку каждого.
И, наконец, когда список готов, то его можно выгрузить на лист с помощью команды Главная - Закрыть и загрузить - Закрыть и загрузить в. (Home - Close & Load - Close & Load to. ) :
И, само-собой, теперь можно построить по нашей таблице сводную (вкладка Вставка - Сводная таблица), чтобы легко подсчитать количество файлов в каждой папке:
Дополнительным бонусом можно сделать еще один столбец с функцией ГИПЕРССЫЛКА (HYPERLINK) , которая создаст красивые стрелочки-ссылки для моментального перехода к каждому файлу:
Мелочь, а приятно :)
И вдвойне приятно, что в будущем, при изменении содержимого исходной папки, достаточно будет просто щелкнуть мышью по нашей таблице и выбрать команду Обновить (Refresh) - и Power Query выполнит всю цепочку запрограммированных нами единожды действий уже автоматически, отобразив все изменения в составе папки.
= Мир MS Excel/Поиск по файлам - Мир MS Excel
Войти через uID
Войти через uID
Добрый день! Подскажите пожалуйста, где я ошибаюсь. Задача следующаяя: имеются файлы, которые эксель определяет. В этих файлах хранится текстовая информация. В главном файле задается критерий для поиска и кноркой активируется поиск, после чего найденные записи по всем файлам выводятся на лист главного. Но при поиске он ищет почему-то только в самом себе, а не в нужных.
Private Sub CommandButton1_Click()
ТекстДляПоиска = "ант"
[c1] = "C:\Users\Администратор\Desktop\ГУН"
' Ищем файлы в заданной папке по заданной маске,
' и выводим на лист список их параметров.
' Просматриваются папки с заданной глубиной вложения.
Dim coll As Collection, FolderPath$, searchmask$, searchdepth%
On Error Resume Next
FolderPath$ = [c1] ' берм из ячейки c1
searchmask$ = "*.*xl*" ' берм из ячейки c2
searchdepth% = 1 ' берм из ячейки c3
If searchdepth% = 0 Then searchdepth% = 999 ' без ограничения по глубине
' считываем в колекцию coll нужные имена файлов
Set coll = FilenamesCollection(FolderPath$, searchmask$, searchdepth%)
Application.ScreenUpdating = False ' отключаем обновление экрана
' выводим результаты (список файлов, и их характеристик) на лист
For i = 1 To coll.Count ' перебираем все элементы коллекции, содержащей пути к файлам
filenumber = i
pathtothefile = coll(i)
Filename = Dir(pathtothefile)
creationdate = FileDateTime(pathtothefile)
filesize = FileLen(pathtothefile)
filesize = FileOrFolderSize(filesize)
'------------------------------------------------------------------
ТекстДляПоиска = "*" & "ант" & "*"
Set СписокНомеровНайденныхСтрок = New Collection
On Error Resume Next ' отключаем останов при ошибке
Workbooks.Open Filename:=pathtothefile
Workbooks(pathtothefile).Activate
With ThisWorkbook.Worksheets("Лист1")
'------------------------------------------------------------------
ПоследняяСтрокаБД = .Range("a" & .Rows.Count).End(xlUp).Row ' вычисляем номер последней строки
Dim РезультатПоиска As Range, АдресПервойНайденнойЯчейки As String
Set РезультатПоиска = Cells.Find(ТекстДляПоиска, LookAt:=xlPart) ' начинаем поиск
If Not РезультатПоиска Is Nothing Then ' если нашли хоть одну подходящую ячейку
АдресПервойНайденнойЯчейки = РезультатПоиска.Address ' запоминаем Адрес Первой Найденной Ячейки
НомерСтроки = РезультатПоиска.Row ' получаем номер строки, в которой найдена подходящая ячейка
СписокНомеровНайденныхСтрок.Add НомерСтроки, CStr(НомерСтроки) ' записываем номер строки в список
Do
' ищем следующую ячейку
Set РезультатПоиска = Cells.FindNext(РезультатПоиска)
If Not РезультатПоиска Is Nothing Then ' если нашли очередную подходящую ячейку
НомерСтроки = РезультатПоиска.Row ' получаем номер строки, в которой найдена подходящая ячейка
СписокНомеровНайденныхСтрок.Add НомерСтроки, CStr(НомерСтроки) ' записываем номер строки в список
End If
' повторяем поиск до тех пор, пока не дойдм до Первой Найденной Ячейки
Loop While РезультатПоиска.Address <> АдресПервойНайденнойЯчейки
End If
'------------------------------------------------------------------
End With
ActiveWorkbook.Close False
On Error GoTo 0 ' отключение режима пропуска ошибок
'------------------------------------------------------------------
Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 5).Value = _
Array(filenumber, Filename, pathtothefile, creationdate, filesize)
' если нужна гиперссылка на файл во втором столбце
ActiveSheet.Hyperlinks.Add Range("b" & Rows.Count).End(xlUp), pathtothefile, "", _
"Открыть файл" & vbNewLine & Filename
On Error GoTo 0
Range("a:e").EntireColumn.AutoFit ' автоподбор ширины столбцов
End Sub
Добрый день! Подскажите пожалуйста, где я ошибаюсь. Задача следующаяя: имеются файлы, которые эксель определяет. В этих файлах хранится текстовая информация. В главном файле задается критерий для поиска и кноркой активируется поиск, после чего найденные записи по всем файлам выводятся на лист главного. Но при поиске он ищет почему-то только в самом себе, а не в нужных.
Private Sub CommandButton1_Click()
ТекстДляПоиска = "ант"
[c1] = "C:\Users\Администратор\Desktop\ГУН"
' Ищем файлы в заданной папке по заданной маске,
' и выводим на лист список их параметров.
' Просматриваются папки с заданной глубиной вложения.
Dim coll As Collection, FolderPath$, searchmask$, searchdepth%
On Error Resume Next
FolderPath$ = [c1] ' берм из ячейки c1
searchmask$ = "*.*xl*" ' берм из ячейки c2
searchdepth% = 1 ' берм из ячейки c3
If searchdepth% = 0 Then searchdepth% = 999 ' без ограничения по глубине
' считываем в колекцию coll нужные имена файлов
Set coll = FilenamesCollection(FolderPath$, searchmask$, searchdepth%)
Application.ScreenUpdating = False ' отключаем обновление экрана
' выводим результаты (список файлов, и их характеристик) на лист
For i = 1 To coll.Count ' перебираем все элементы коллекции, содержащей пути к файлам
filenumber = i
pathtothefile = coll(i)
Filename = Dir(pathtothefile)
creationdate = FileDateTime(pathtothefile)
filesize = FileLen(pathtothefile)
filesize = FileOrFolderSize(filesize)
'------------------------------------------------------------------
ТекстДляПоиска = "*" & "ант" & "*"
Set СписокНомеровНайденныхСтрок = New Collection
On Error Resume Next ' отключаем останов при ошибке
Workbooks.Open Filename:=pathtothefile
Workbooks(pathtothefile).Activate
With ThisWorkbook.Worksheets("Лист1")
'------------------------------------------------------------------
ПоследняяСтрокаБД = .Range("a" & .Rows.Count).End(xlUp).Row ' вычисляем номер последней строки
Dim РезультатПоиска As Range, АдресПервойНайденнойЯчейки As String
Set РезультатПоиска = Cells.Find(ТекстДляПоиска, LookAt:=xlPart) ' начинаем поиск
If Not РезультатПоиска Is Nothing Then ' если нашли хоть одну подходящую ячейку
АдресПервойНайденнойЯчейки = РезультатПоиска.Address ' запоминаем Адрес Первой Найденной Ячейки
НомерСтроки = РезультатПоиска.Row ' получаем номер строки, в которой найдена подходящая ячейка
СписокНомеровНайденныхСтрок.Add НомерСтроки, CStr(НомерСтроки) ' записываем номер строки в список
Do
' ищем следующую ячейку
Set РезультатПоиска = Cells.FindNext(РезультатПоиска)
If Not РезультатПоиска Is Nothing Then ' если нашли очередную подходящую ячейку
НомерСтроки = РезультатПоиска.Row ' получаем номер строки, в которой найдена подходящая ячейка
СписокНомеровНайденныхСтрок.Add НомерСтроки, CStr(НомерСтроки) ' записываем номер строки в список
End If
' повторяем поиск до тех пор, пока не дойдм до Первой Найденной Ячейки
Loop While РезультатПоиска.Address <> АдресПервойНайденнойЯчейки
End If
'------------------------------------------------------------------
End With
ActiveWorkbook.Close False
On Error GoTo 0 ' отключение режима пропуска ошибок
'------------------------------------------------------------------
Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 5).Value = _
Array(filenumber, Filename, pathtothefile, creationdate, filesize)
' если нужна гиперссылка на файл во втором столбце
ActiveSheet.Hyperlinks.Add Range("b" & Rows.Count).End(xlUp), pathtothefile, "", _
"Открыть файл" & vbNewLine & Filename
On Error GoTo 0
Range("a:e").EntireColumn.AutoFit ' автоподбор ширины столбцов
End Sub
Private Sub CommandButton1_Click()
ТекстДляПоиска = "ант"
[c1] = "C:\Users\Администратор\Desktop\ГУН"
' Ищем файлы в заданной папке по заданной маске,
' и выводим на лист список их параметров.
' Просматриваются папки с заданной глубиной вложения.
Dim coll As Collection, FolderPath$, searchmask$, searchdepth%
On Error Resume Next
FolderPath$ = [c1] ' берм из ячейки c1
searchmask$ = "*.*xl*" ' берм из ячейки c2
searchdepth% = 1 ' берм из ячейки c3
If searchdepth% = 0 Then searchdepth% = 999 ' без ограничения по глубине
' считываем в колекцию coll нужные имена файлов
Set coll = FilenamesCollection(FolderPath$, searchmask$, searchdepth%)
Application.ScreenUpdating = False ' отключаем обновление экрана
' выводим результаты (список файлов, и их характеристик) на лист
For i = 1 To coll.Count ' перебираем все элементы коллекции, содержащей пути к файлам
filenumber = i
pathtothefile = coll(i)
Filename = Dir(pathtothefile)
creationdate = FileDateTime(pathtothefile)
filesize = FileLen(pathtothefile)
filesize = FileOrFolderSize(filesize)
'------------------------------------------------------------------
ТекстДляПоиска = "*" & "ант" & "*"
Set СписокНомеровНайденныхСтрок = New Collection
On Error Resume Next ' отключаем останов при ошибке
Workbooks.Open Filename:=pathtothefile
Workbooks(pathtothefile).Activate
With ThisWorkbook.Worksheets("Лист1")
'------------------------------------------------------------------
ПоследняяСтрокаБД = .Range("a" & .Rows.Count).End(xlUp).Row ' вычисляем номер последней строки
Dim РезультатПоиска As Range, АдресПервойНайденнойЯчейки As String
Set РезультатПоиска = Cells.Find(ТекстДляПоиска, LookAt:=xlPart) ' начинаем поиск
If Not РезультатПоиска Is Nothing Then ' если нашли хоть одну подходящую ячейку
АдресПервойНайденнойЯчейки = РезультатПоиска.Address ' запоминаем Адрес Первой Найденной Ячейки
НомерСтроки = РезультатПоиска.Row ' получаем номер строки, в которой найдена подходящая ячейка
СписокНомеровНайденныхСтрок.Add НомерСтроки, CStr(НомерСтроки) ' записываем номер строки в список
Do
' ищем следующую ячейку
Set РезультатПоиска = Cells.FindNext(РезультатПоиска)
If Not РезультатПоиска Is Nothing Then ' если нашли очередную подходящую ячейку
НомерСтроки = РезультатПоиска.Row ' получаем номер строки, в которой найдена подходящая ячейка
СписокНомеровНайденныхСтрок.Add НомерСтроки, CStr(НомерСтроки) ' записываем номер строки в список
End If
' повторяем поиск до тех пор, пока не дойдм до Первой Найденной Ячейки
Loop While РезультатПоиска.Address <> АдресПервойНайденнойЯчейки
End If
'------------------------------------------------------------------
End With
ActiveWorkbook.Close False
On Error GoTo 0 ' отключение режима пропуска ошибок
'------------------------------------------------------------------
Range("a" & Rows.Count).End(xlUp).Offset(1).Resize(, 5).Value = _
Array(filenumber, Filename, pathtothefile, creationdate, filesize)
' если нужна гиперссылка на файл во втором столбце
ActiveSheet.Hyperlinks.Add Range("b" & Rows.Count).End(xlUp), pathtothefile, "", _
"Открыть файл" & vbNewLine & Filename
On Error GoTo 0
Range("a:e").EntireColumn.AutoFit ' автоподбор ширины столбцов
End Sub
Читайте также: