Find and replace strings in file names, folder name and subfolders

The following two macros FindReplace() and Recursive() let you rename files and folders recursively based on a search string. The file or folder is renamed if the name contains the search string.

The search string in the file name is replaced by another string you specify. A dialog box appears asking if you want to rename the found file name that matches the search string.

1. VBA macros

'Webpage: https://www.get-digital-help.com/find-and-replace-a-text-string-in-file-names-folder-name-and-subfolders/
'Name macro
Sub FindReplace()

'Dimension variables and declare data types
Dim myfolder As Variant
Dim Fnd As String, Rplc As String

'Display input box and ask for a string to search for
Fnd = Application.InputBox(prompt:="Find string:", Title:="Rename files and folders", Type:=2)

'Display another input box and ask for a string to replace with
Rplc = Application.InputBox(prompt:="Replace with:", Title:="Rename files and folders", Type:=2)

'Ask for a folder to search
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
myfolder = .SelectedItems(1) & "\"
End With

'Start macro Recursive with parameters myfolder, Fnd, and Rplc
Call Recursive(myfolder, Fnd, Rplc)
End Sub

'Name macro and parameters
Sub Recursive(FolderPath As Variant, Fnd As String, Rplc As String)

'Dimension variables and declare data types
Dim Value As String, Folders() As String, Fname As String, Fext As String, Mtxt As String
Dim x As Integer
Dim Folder As Variant, a As Long

'Redimension array variable Folders 
ReDim Folders(0)

'If ... Then statement
'Check if two last characters equals \\
If Right(FolderPath, 2) = "\\" Then Exit Sub

'Dir function returns a String representing the name of a file, directory, or folder that matches a specified pattern
Value = Dir(FolderPath, &H1F)

'Do Until ... Loop statement
Do Until Value = ""

'Check if Value is equal to . or ..
If Value = "." Or Value = ".." Then

'Continue here if Value is not equal to . or ..
Else

'Check if Value is a folder
If GetAttr(FolderPath & Value) = 16 Or GetAttr(FolderPath & Value) = 48 Then

'Enable error handling
On Error Resume Next

'Concatenate text with value in variable Value and show the new file name
Mtxt = "Rename folder " &  Value & " to " & WorksheetFunction.Substitute(Value, Fnd, Rplc) & "?"

'Show message box containing buttons Yes, No, and Cancel
x = MsgBox(Mtxt, vbYesNoCancel)

'End macro If user press with left mouse button ons Cancel button
If x = vbCancel Then Exit Sub

'Check if user press with left mouse button ons Yes
If x = vbYes Then

'Rename file if user press with left mouse button ons yes
Name FolderPath & Value As FolderPath &  WorksheetFunction.Substitute(Value, Fnd, Rplc)
End If

'Substitute string specified in Fnd with string in Rplc and save to variable Value
Value = WorksheetFunction.Substitute(Value, Fnd, Rplc)

'Check if an error has occurred
If Err <> 0 Then

'Show message box containing text "Error"
MsgBox "Error"

'Stop macro
Exit Sub
End If

'Disable error handling
On Error GoTo 0

Save string in variable Value to array variable Folders
Folders(UBound(Folders)) = Value

'Add another container to array variable Folders
ReDim Preserve Folders(UBound(Folders) + 1)

'Continue here if an error has not occurred
Else

'Enable error handling
On Error Resume Next

'Save extension text to variable Fext
Fext = Split(Value, ".")(UBound(Split(Value, ".")))

'Save file name without extension to variable Fname
Fname = Left(Value, Len(Value) - Len(Split(Value, ".")(UBound(Split(Value, ".")))) - 1)

'Replace search string (Fnd) with replace string (Rplc) in variable Fname and save to Fname
Fname = WorksheetFunction.Substitute(Fname, Fnd, Rplc)

'Check if value is not equal to Fname, dot and Ftext
If Value <> (Fname &  "." & Fext) Then

'Create text string and save to variable Mtxt
Mtxt = "Rename file " & Value & " to " & Fname & "." & Fext &  "?"

'Show message box containing text in variable Mtxt with buttons Yes, No, and Cancel. Save result to variable x
x = MsgBox(Mtxt, vbYesNoCancel)

'Check if variable x is equal to vbCancel meaning the user press with left mouse button oned Cancel button, stop macro if true
If x = vbCancel Then Exit Sub

'Check if user press with left mouse button on Yes
If x = vbYes Then

'Rename file
Name FolderPath &  Value As FolderPath & Fname &  "." & Fext
End If
End If

'Check if an error has occurred
If Err <> 0 Then

'Display message box containing text Error
MsgBox "Error"

'Stop macro
Exit Sub
End If

'Disable error handling
On Error GoTo 0
End If
End If

'Save a new item in current Folder to variable Value
Value = Dir

'Go back to Do Until, see above
Loop

'Iterate through all folders in array variable Folders
For Each Folder In Folders

'Start macro Recursive using folder name and path
Call Recursive(FolderPath &  Folder &  "\", Fnd, Rplc)
Next Folder
End Sub

Back to top

2. Where to put the code?

1. Press Alt + F11 to open the Visual Basic Editor (VBE), see image above.
2. Press with left mouse button on "Insert" on the top menu, a popup menu appears.
3. Press with left mouse button on "Module". A new module is created in your workbook.
4. Copy VBA code above and paste to code window, see image above.
5. Exit VBE and return to Excel.

Back to top

3. How to start the macro?

1. Press Alt + F8, a dialog box appears. See the image above.
2. Press with mouse on "FindReplace" to select it.
3. Press with left mouse button on "Run" button.

Back to top

4. What happens when you run macro?

1. Type a text string you want to find in the dialog box.
   
2. In the next dialog box, type a text sting you want to replace it with.
   
3. Select a folder, press with left mouse button on OK.
4. The macro prompts you each time it finds a file name or folder with the text string you are looking for and ask if you want to rename it.
5. Your options are: Yes, No, and Cancel. Cancel exits the macro.
6. The macro continues until all files and folders are processed.

The macro does NOT change the file extension name.

Back to top

Get the Excel file

Find-and-replace-a-character-in-file-names-folder-names-and-subfoldersv2.xlsm

Back to top

Files and folders category

Compare file names in two different folder locations and their sub folders
Today I would like to share a macro that compares the content in two different folders and their subfolders. It […]

Copy data from workbooks in folder and subfolders
I will in this article demonstrate a macro that automatically opens all workbooks in a folder and subfolders, one by […]

Filter duplicate files in a folder and subfolders
This is a follow up to my last post Compare file names in two different folder locations and their subfolders, the obvious […]

Excel categories

Comments (8)