Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (14)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (38)
Баги и глюки Excel (2)

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

Иногда необходимо проделать однотипные операции с несколькими файлами, расположенными в одной папке. Можно открывать каждый по очереди:
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, 4 933 скачиваний)


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

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

Sub Get_All_drives()
    Dim objDrives As Object, objDrive As Object
 
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objDrives = objFSO.Drives
    For Each objDrive In objDrives
        If objDrive.IsReady Then
            GetSubFolders objDrive.DriveLetter & ":\"
        End If
    Next objDrive
End Sub

Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Обсуждение: 36 комментариев
  1. Александр:

    Дмитрий, Добрый час!
    Подскажите, пожалуйста, как сделать, чтобы макрос сканировал определенную папку и выводил сообщение, если в папку загрузились файлы?

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

  2. Георгий:

    Спасибо за код! Искал сначала на англоязычных сайтах - не нашел)

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

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

      If objFile.Name <> ThisWorkbook.Name 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
Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2018 Excel для всех   Войти