How to correct a changed contract number in several hundred Excel files in less than a minute

Recently I was faced with the need to print out a large number of construction estimates, but at the last moment it turned out that the contract number was indicated incorrectly because it had changed at the beginning of the month.

This is how the task of replacing the contract number and its date in each of the *.xlsx files, which were located in folders named by the address of the object, arose. There were many such folders and some also had nested subdirectories.

The manual method, which consists of using a keyboard shortcut Ctrl + H although it allowed you to make a replacement in the entire Microsoft Excel workbook at once, it meant that you would have to open each file separately, press several times (for replacement not only the number, but also the date) keyboard shortcuts for replacement, save the Excel workbook, close the workbook, open a new one book. In general, perform many mechanical actions.

Of course, I began to think about how to make this many autocorrects in the simplest way possible. The choice immediately fell on VBA – Visual Basic for Applications. After all, with the help of VBA you can force Excel to do what it cannot do by default.

So in this article I want to show a step-by-step guide on how to quickly and bulk autocorrect any data in multiple Excel files using a VBA script.

'
' Подробнее: https://habr.com/ru/articles/807961/
'

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

Preparing Microsoft Excel

Make sure Microsoft Excel is installed. Enabling the Developer tab in Excel allows you to access development-related tools and features, including macros, Visual Basic for Applications (VBA). Here's how to enable the Developer tab in Excel:

1. Launch Excel on your computer.

2. Go to settings. Depending on your version of Excel, the steps may vary slightly:
➡️ For Excel 2010 and later: Click the File tab in the upper left corner, then select Options at the bottom of the menu.
➡️ For Excel 2007: Click the round Office button in the upper left corner, then click Excel Options at the bottom of the menu.

3. In the Excel Options dialog box, you will see a list of categories on the left. Find and click Customize the Ribbon (for Excel 2010 and later) or Popular (for Excel 2007).

4. Find the Developer option in the list on the right under Main Tabs. Make sure the box next to “Developer” is checked.

4. With the Developer checkbox selected, click the OK button at the bottom of the Excel Options dialog box to save your changes and close the dialog box.

5. Once you have enabled the Developer tab, you should see it on the Excel ribbon at the top of the Excel window along with other tabs such as Home, Insert, etc.

VBA Script for AutoCorrect

Before mass autocorrect, I recommend creating backup copies of Excel files by simply copying directories.

To insert a VBA script into Excel and run it, follow these steps:

1. Enable the Developer tab (if it is not already enabled).

2. Access the Visual Basic Editor:
Click the Developer tab on the Excel ribbon.
In the Code group, click Visual Basic (or press ALT+F11).

3. Paste the script:
In the Visual Basic Editor window, make sure your workbook is selected in the Project Explorer window, usually named “VBAProject (file name).”
Right click on the project name or any existing module folder.
Choose Insert > Module from the context menu.
Paste the VBA code:

'
' Подробнее: https://habr.com/ru/articles/807961/
'

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

Lines 33 and 34 from the code above you can add or remove as needed.

4. Run the VBA script:
➡️Running a Macro You can run it by going to the Developer tab, clicking Macros, selecting the macro, and clicking Run.

➡️ Run from VB: You can run it directly from the Visual Basic Editor by placing the cursor inside the procedure and pressing F5 or choosing Run > Run Sub/UserForm from the menu.

Ready

Now you need to check the results and make sure that it performed the right actions in your Excel workbook.

And don’t forget to save the Excel workbook with macros enabled.

Results

Using VBA scripts to quickly replace any text in numerous Excel files offers a solution to the problem of error correction and autocorrect.

The step-by-step guide outlined in this article provides a tool that allows you to accomplish in minutes what would otherwise take hours or even days to do manually.

Author: Mikhail Shardin,

April 17, 2024