Replace a string in all the excel files in a folder using VBA

Objective

To replace a string in all the excel files in a folder using VBA.

Approach

In the code below we have replaced a particular string in all the excel files in a folder. Herein we open each of the excel file in a folder one by one, then replace the string in that file, and then save the file, and at last close the excel file. Following the same pattern, we open the next excel file and then apply the same string replacement process.

Steps

Below shown are the steps which have been used in the code –

1. Declare the variable for path, original string and replacement string
2. Set the values for original string and replacement string
3. Check if the folder or directory exists
4. Loop through each of the file in the folder
5. Check for file extension of opened file , if it is xlsx, then replace the string [e.g. QRCode] with “QR Code” in entire sheet1
6. Save the file and close it
7. Open the next file, do the same process
8. If folder does not exist, then display the message box and exit code.

In the code below we are replacing  the string “QRCode” with “QR Code” from multiple excel files in a folder.

Sub ReplaceStringInExcelFiles()
Dim MyFolderPath As String
Application.DisplayAlerts = False
'Path where the excel files are
Mypath = "C:\Users\yourusername\Desktop\macro\Excel\test\"
'Variables for replacement
Original_String = "QRCode"
New_Replacement_String = "QR Code"
'Check if the folder exists
If Dir(Mypath, vbDirectory) <> "" Then
FileToOpen = Dir(Mypath)
 'Looping through each file
Do While FileToOpen <> ""
'Checking for the file type (xlsx)
FileTypeCheck = InStr(FileToOpen, ".xlsx")
If FileTypeCheck > 0 Then
Workbooks.Open Mypath & FileToOpen
'Replace all the occurence of a sring in entire sheet
 Sheets("sheet1").Cells.Replace what:=Original_String, Replacement:=New_Replacement_String, lookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
    SearchFormat:=False, ReplaceFormat:=False
ActiveWorkbook.Save
ActiveWorkbook.Close
End If
FileToOpen = Dir
Loop
Else
MsgBox "Folder does not exist"
End If
End Sub

 

Note

Same process can be customized for other file types like csv etc. We can also select a folder using dialogue box, by doing so we can make the code more dynamic. we can also use input box for the strings to be replaced and string which will replace the original string.

Post you may like

Create and format a shape in worksheet by using VBA Excel