Недавно я столкнулся с тем, что потребовалось распечатать большое количество строительных смет, но в последний момент оказалось, что номер договора в них указан неверно, потому что в начале месяца он поменялся.
Так появилась задача однотипной замены номера договора и его даты в каждом из *.xlsx файлов, которые были расположены в папках, названных по адресу объекта. Таких папок было множество и некоторые ещё имели вложенные подкаталоги.
Ручной способ, который состоит из использования сочетания клавиш Ctrl + H
хоть и позволял сделать замену во всей книге Microsoft Excel сразу, но подразумевал что придётся открыть каждый файл по отдельности, нажимать несколько раз (под замену не только номер, но и дата) сочетания клавиш для замены, сохранять книгу Экселя, закрывать книгу, открывать новую книгу. В общем совершать множество механических действий.
Я конечно же стал думать, как можно наиболее простым для себя способом сделать это множество автозамен. Выбор сразу пал на VBA - Visual Basic для приложений. Ведь при помощи VBA можно заставить Эксель делать то, чего он не умеет по умолчанию.
Так что в этой статье хочу показать пошаговое руководство по быстрой и массовой автозамены любых данных во множестве файлах Excel с помощью сценария VBA.
TL;DR
'
' Подробнее: 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 предлагает решение проблемы исправления ошибок и автозамены.
Пошаговое руководство, изложенное в этой статье, дает инструмент, позволяющий за минуты выполнить то, что в противном случае потребовало бы часов или даже дней для выполнения вручную.
Автор: Михаил Шардин,
17 апреля 2024 г.