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

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

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

Ручной способ, который состоит из использования сочетания клавиш Ctrl + H хоть и позволял сделать замену во всей книге Microsoft Excel сразу, но подразумевал что придётся открыть каждый файл по отдельности, нажимать несколько раз (под замену не только номер, но и дата) сочетания клавиш для замены, сохранять книгу Экселя, закрывать книгу, открывать новую книгу. В общем совершать множество механических действий.

Я конечно же стал думать, как можно наиболее простым для себя способом сделать это множество автозамен. Выбор сразу пал на VBA - Visual Basic для приложений. Ведь при помощи VBA можно заставить Эксель делать то, чего он не умеет по умолчанию.

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

'
' Подробнее: https://habr.com/ru/articles/807961/
' Михаил Шардин https://shardin.name/
'

Sub ReplaceTextInFiles()
    Dim FileSystem  As Object
    Dim HostFolder  As String
    Dim FileName    As String
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Dim rng         As Range
    
    HostFolder = ThisWorkbook.Path
    
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    
    ProcessFiles HostFolder, FileSystem
    
    MsgBox "Автозамена произведена", vbInformation
End Sub

Sub ProcessFiles(ByVal folderPath As String, ByVal fs As Object)
    Dim subFolder   As Object
    Dim file        As Object
    
    For Each file In fs.GetFolder(folderPath).Files
        If LCase(file.Name Like "*.xlsx*") Then
            Dim wb  As Workbook
            Set wb = Workbooks.Open(file.Path)
            For Each ws In wb.Worksheets
                ' Начало - ниже все действия, которые необходимо сделать:
                Set rng = ws.UsedRange
                rng.Replace What:="СТАРОЕ ЗНАЧЕНИЕ", Replacement:="НОВОЕ ЗНАЧЕНИЕ", LookAt:=xlPart, MatchCase:=False
                rng.Replace What:="03.04.2024", Replacement:="17.04.2024", LookAt:=xlPart, MatchCase:=False
                
                ' Конец всех действий, которые необходимо было сделать
                
            Next ws
            wb.Save
            wb.Close
        End If
    Next file
    For Each subFolder In fs.GetFolder(folderPath).SubFolders
        ProcessFiles subFolder.Path, fs
    Next subFolder
End Sub

Подготовка Microsoft Excel

Убедитесь, что Microsoft Excel установлен. Включение вкладки «Разработчик» в Excel позволяет получить доступ к инструментам и функциям, связанным с разработкой, включая макросы, Visual Basic для приложений (VBA). Вот как включить вкладку «Разработчик» в Excel:

1. Запустите Excel на своем компьютере.

2. Перейдите к параметрам. В зависимости от вашей версии Excel действия могут незначительно отличаться:
➡️ Для Excel 2010 и более поздних версий: нажмите вкладку «Файл» в верхнем левом углу, затем выберите «Параметры» в нижней части меню.
➡️ Для Excel 2007: нажмите круглую кнопку Office в верхнем левом углу, затем нажмите «Параметры Excel» в нижней части меню.

3. В диалоговом окне «Параметры Excel» слева вы увидите список категорий. Найдите и нажмите «Настроить ленту» (для Excel 2010 и более поздних версий) или «Популярные» (для Excel 2007).

4. Найдите параметр «Разработчик» в списке справа в разделе «Основные вкладки». Убедитесь, что флажок рядом с «Разработчик» установлен.

4. Установив флажок «Разработчик», нажмите кнопку «ОК» в нижней части диалогового окна «Параметры Excel», чтобы сохранить изменения и закрыть диалоговое окно.

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

Сценарий VBA для автозамены

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

Чтобы вставить сценарий VBA в Excel и выполнить его, выполните следующие действия:

1. Включите вкладку «Разработчик» (если она еще не включена).

2. Пол��чите доступ к редактору Visual Basic:
Нажмите вкладку «Разработчик» на ленте Excel.
В группе «Код» нажмите «Visual Basic» (или нажмите ALT+F11).

3. Вставьте скрипт:
В окне редактора Visual Basic убедитесь, что ваша книга выбрана в окне Project Explorer обычно с именем «VBAProject (имя файла)».
Щелкните правой кнопкой мыши на имя проекта или любую существующую папку модуля.
Выберите «Вставка» > «Модуль» в контекстном меню.
Вставьте код VBA:

'
' Подробнее: https://habr.com/ru/articles/807961/
' Михаил Шардин https://shardin.name/
'

Sub ReplaceTextInFiles()
    Dim FileSystem  As Object
    Dim HostFolder  As String
    Dim FileName    As String
    Dim wb          As Workbook
    Dim ws          As Worksheet
    Dim rng         As Range
    
    HostFolder = ThisWorkbook.Path
    
    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    
    ProcessFiles HostFolder, FileSystem
    
    MsgBox "Автозамена произведена", vbInformation
End Sub

Sub ProcessFiles(ByVal folderPath As String, ByVal fs As Object)
    Dim subFolder   As Object
    Dim file        As Object
    
    For Each file In fs.GetFolder(folderPath).Files
        If LCase(file.Name Like "*.xlsx*") Then
            Dim wb  As Workbook
            Set wb = Workbooks.Open(file.Path)
            For Each ws In wb.Worksheets
                ' Начало - ниже все действия, которые необходимо сделать:
                Set rng = ws.UsedRange
                rng.Replace What:="СТАРОЕ ЗНАЧЕНИЕ", Replacement:="НОВОЕ ЗНАЧЕНИЕ", LookAt:=xlPart, MatchCase:=False
                rng.Replace What:="03.04.2024", Replacement:="17.04.2024", LookAt:=xlPart, MatchCase:=False
                
                ' Конец всех действий, которые необходимо было сделать
                
            Next ws
            wb.Save
            wb.Close
        End If
    Next file
    For Each subFolder In fs.GetFolder(folderPath).SubFolders
        ProcessFiles subFolder.Path, fs
    Next subFolder
End Sub

Строки 33 и 34 из кода выше вы можете добавлять или удалять по мере необходимости.

4. Запустите сценарий VBA:
➡️ Запуск макроса.Можете запустить его, перейдя на вкладку «Разработчик», нажав «Макросы», выбрав макрос и нажав «Выполнить».

➡️ Запуск из VB: Можете запустить ее непосредственно из редактора Visual Basic, поместив курсор внутри процедуры и нажав F5 или выбрав «Выполнить» > «Выполнить Sub/UserForm» из меню.

Готово

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

И ещё не забудьте сохранить книгу Excel с включенными макросами.

Итоги

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

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

Автор: Михаил Шардин
🔗 Моя онлайн-визитка
📢 Telegram «Умный Дом Инвестора»

17 апреля 2024 г.