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 –
- Declare the variable for path, original string and replacement string
- Set the values for original string and replacement string
- Check if the folder or directory exists
- Loop through each of the file in the folder
- Check for file extension of opened file , if it is xlsx, then replace the string [e.g. QRCode] with “QR Code” in entire sheet1
- Save the file and close it
- Open the next file, do the same process
- 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