Vba excel список файлов в папке

мне нужно получить имена всех файлов Excel в папке, а затем вносить изменения в каждый файл. Я разобрался с частью "внести изменения". Есть ли способ получить список .xlsx файлы в одной папке, скажем D:Personal и сохраните его в строковом массиве.

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

на этой, однако, я не смог открыть файлы вызывают сохранение имен в .

короче говоря, как я могу использовать VBA для получения списка имен файлов Excel в определенной папке?

4 ответов

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

было бы неплохо, если бы мы могли просто получить доступ к файлам в объекте files по номеру индекса, но это, похоже, сломано в VBA по какой-либо причине (ошибка?).

вы можете использовать встроенную функцию Dir или FileSystemObject.

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

Если вы хотите intellisense и больше возможностей, помимо получения имени файла, я бы предложил FileSystemObject. Вы можете добавить ссылку на " Windows Script Host Object Model "(или" Windows Scripting Runtime") и объявить свои объекты следующим образом:

Если вы не хотите intellisense, вы можете сделать это, не устанавливая ссылку:

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

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

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

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

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

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

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

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

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

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

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

• 238922 просмотра

Комментарии

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

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

Отбой, разобрался. Виноват оказался не этот макрос, а тот, который его результаты использовал. Мораль — люди, не юзайте 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й работают), хотя файл и макросы одни и те же. Знаете, в чем может быть причина?

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

Пример в файле FilenamesCollection.xls выводит список файлов на чистый лист новой книги (формируя заголовки)

Пример в файле FilenamesCollectionEx.xls более функционален — он, помимо списка файлов из папки, отображает размер файла, и дату его создания, а также формирует в ячейках гиперссылки на найденные файлы .

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

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

Внимание: если требуется, чтобы поиск не зависел от регистра символов в маске файла
(к примеру, обнаруживались не только файлы .txt , но и .TXT и .Txt ),
поставьте первой строкой в модуле директиву Option Compare Text

Function FilenamesCollection( ByVal FolderPath As String , Optional ByVal Mask As String = "" , _

Optional ByVal SearchDeep As Long = 999) As Collection

‘ Получает в качестве параметра путь к папке FolderPath,

‘ маску имени искомых файлов Mask (будут отобраны только файлы с такой маской/расширением)

‘ и глубину поиска SearchDeep в подпапках (если SearchDeep=1, то подпапки не просматриваются).

‘ Возвращает коллекцию, содержащую полные пути найденных файлов

‘ (применяется рекурсивный вызов процедуры GetAllFileNamesUsingFSO)

Set FilenamesCollection = New Collection ‘ создаём пустую коллекцию

Set FSO = CreateObject( "Scripting.FileSystemObject" ) ‘ создаём экземпляр FileSystemObject

GetAllFileNamesUsingFSO FolderPath, Mask, FSO, FilenamesCollection, SearchDeep ‘ поиск

Set FSO = Nothing : Application.StatusBar = False ‘ очистка строки состояния Excel

Function GetAllFileNamesUsingFSO( ByVal FolderPath As String , ByVal Mask As String , ByRef FSO, _

ByRef FileNamesColl As Collection , ByVal SearchDeep As Long )

‘ перебирает все файлы и подпапки в папке FolderPath, используя объект FSO

‘ перебор папок осуществляется в том случае, если SearchDeep > 1

‘ добавляет пути найденных файлов в коллекцию FileNamesColl

On Error Resume Next : Set curfold = FSO.GetFolder(FolderPath)

If Not curfold Is Nothing Then ‘ если удалось получить доступ к папке

‘ раскомментируйте эту строку для вывода пути к просматриваемой

‘ в текущий момент папке в строку состояния Excel

‘ Application.StatusBar = "Поиск в папке: " & FolderPath

For Each fil In curfold.Files ‘ перебираем все файлы в папке FolderPath

If fil.Name Like "*" & Mask Then FileNamesColl.Add fil.Path

SearchDeep = SearchDeep — 1 ‘ уменьшаем глубину поиска в подпапках

If SearchDeep Then ‘ если надо искать глубже

For Each sfol In curfold.SubFolders ‘ перебираем все подпапки в папке FolderPath

GetAllFileNamesUsingFSO sfol.Path, Mask, FSO, FileNamesColl, SearchDeep

Set fil = Nothing : Set curfold = Nothing ‘ очищаем переменные

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

On Error Resume Next

Dim folder$, coll As Collection

folder$ = ThisWorkbook.Path & "Платежи"

If Dir(folder$, vbDirectory) = "" Then

MsgBox "Не найдена папка «" & folder$ & "»" , vbCritical, "Нет папки ПЛАТЕЖИ"

Exit Sub ‘ выход, если папка не найдена

Set coll = FilenamesCollection(folder$, "*.xls" ) ‘ получаем список файлов XLS из папки

If coll.Count = 0 Then

MsgBox "В папке «" & Split(folder$, "" )( UBound (Split(folder$, "" )) — 1) & "» нет ни одного подходящего файла!" , _

vbCritical, "Файлы для обработки не найдены"

Exit Sub ‘ выход, если нет файлов

‘ перебираем все найденные файлы

For Each file In coll

Debug. Print file ‘ выводим имя файла в окно Immediate

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

‘ Ищем на рабочем столе все файлы TXT, и выводим на лист список их имён.

‘ Просматриваются папки с глубиной вложения не более трёх.

Dim coll As Collection , ПутьКПапке As String

‘ получаем путь к папке РАБОЧИЙ СТОЛ

ПутьКПапке = CreateObject( "WScript.Shell" ).SpecialFolders( "Desktop" )

‘ считываем в колекцию coll нужные имена файлов

Set coll = FilenamesCollection(ПутьКПапке, ".txt" , 3)

Application.ScreenUpdating = False ‘ отключаем обновление экрана

‘ создаём новую книгу

Dim sh As Worksheet: Set sh = Workbooks.Add.Worksheets(1)

‘ формируем заголовки таблицы

With sh.Range( "a1" ).Resize(, 3)

.Value = Array( "№" , "Имя файла" , "Полный путь" )

.Font.Bold = True : .Interior.ColorIndex = 17

‘ выводим результаты на лист

For i = 1 To coll.Count ‘ перебираем все элементы коллекции, содержащей пути к файлам

sh.Range( "a" & sh.Rows.Count). End (xlUp).Offset(1).Resize(, 3).Value = _

Array(i, Dir(coll(i)), coll(i)) ‘ выводим на лист очередную строку

DoEvents ‘ временно передаём управление ОС

sh.Range( "a:c" ).EntireColumn.AutoFit ‘ автоподбор ширины столбцов

[a2].Activate: ActiveWindow.FreezePanes = True ‘ закрепляем первую строку листа

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

‘ Ищем файлы в заданной папке по заданной маске,

‘ и выводим на лист список их параметров.

‘ Просматриваются папки с заданной глубиной вложения.

Dim coll As Collection , ПутьКПапке$, МаскаПоиска$, ГлубинаПоиска%

ПутьКПапке$ = [c1] ‘ берём из ячейки c1

МаскаПоиска$ = [c2] ‘ берём из ячейки c2

ГлубинаПоиска% = Val([c3]) ‘ берём из ячейки c3

If ГлубинаПоиска% = 0 Then ГлубинаПоиска% = 999 ‘ без ограничения по глубине

‘ считываем в колекцию coll нужные имена файлов

Set coll = FilenamesCollection(ПутьКПапке$, МаскаПоиска$, ГлубинаПоиска%)

Application.ScreenUpdating = False ‘ отключаем обновление экрана

‘ выводим результаты (список файлов, и их характеристик) на лист

For i = 1 To coll.Count ‘ перебираем все элементы коллекции, содержащей пути к файлам

‘ выводим на лист очередную строку

Range( "a" & Rows.Count). End (xlUp).Offset(1).Resize(, 5).Value = _

Array(НомерФайла, ИмяФайла, ПутьКФайлу, ДатаСоздания, РазмерФайла)

‘ если нужна гиперссылка на файл во втором столбце

ActiveSheet.Hyperlinks.Add Range( "b" & Rows.Count). End (xlUp), ПутьКФайлу, "" , _

"Открыть файл" & vbNewLine & ИмяФайла

DoEvents ‘ временно передаём управление ОС

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

Function CollectionAutofilter( ByRef coll As Collection , ByVal filter$) As Collection

‘ Функция перебирает все элементы коллекции coll,

‘ оставляя лишь те, которые соответствуют маске filter$ (например, filter$="*некий текст*")

‘ Возвращает коллекцию, содержащую только подходящие элементы

‘ Если элементы не найдены — возвращается пустая коллекция (содержащая 0 элементов)

On Error Resume Next : Set CollectionAutofilter = New Collection