Replace different text/strings in multiple files in one folder

[Shivani9145]

Hi i am encountering same issue as shown here: Create Macro to replace text in .txt file from Excel sheet
Can you please help?

Facing an issue if the text to be replaced in FIND COLUMN matches any part of the text already replaced in REPLACE COLUMN.
B2=b10=b18=b26

I have copied 8 text files and renamed to required file name. Now need to edit their names inside 32 text files(copied) saved in folder. Original 8 files have different string to replace. Then repeating(See 'Find Column').


Now My code below is finding first 8 strings(from B2) in all file names and replacing them in all files( C2 in A2, C3 in A3, C4 in A4 so on till C9 in A9). So when code comes to replace, A10 filename(text inside file) is already replaced by C1. Issue is The code is reading all files and pasting all instances of string in B2 in all file names in range(A1:A32) with string in C2.
I want the code to open file A2, find string in B2 and replace with string in C2 and close file.
Then jump next to open file A3, find string in B3 and replace with string in C3 and close file.

Can some one please help?
File i am using to do make different change to each file:





the string i need to replace:





Code i am using

>>> use code - tags <<<
Code:
Sub ReplaceStringInFile()
Dim objFSO As Object
Dim objFil As Object
Dim objFil2 As Object
Dim regex As Object: Set regex = CreateObject("VBScript.RegExp")
Dim StrFileName As String
Dim StrFolder As String
Dim strAll As String
Dim FindStr As String, ReplaceStr As String
Set objFSO = CreateObject("scripting.filesystemobject") 'enable microsoft scripting runtime
StrFolder = "F:\Replace text\" 'choose folder to go through
StrFileName = Dir(StrFolder & "*.kdt") 'choose extension to find and replace

Do While StrFileName <> vbNullString
Set objFil = objFSpentextfile(StrFolder & StrFileName)
strAll = objFil.readall
objFil.Close
Set objFil2 = objFSO.createtextfile(StrFolder & StrFileName)
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row
With regex
.Pattern = Range("A" & i).Value
strAll = .Replace(strAll, Range("B" & i).Value)
End With
Next i
objFil2.Write strAll 'choose what to find and replace *case sensitive
objFil2.Close
StrFileName = Dir
Loop
Set regex = Nothing
End Sub

The macro runs fine till green part as all strings B column are different but when the code comes A10, and find for b10 , assuming it finds nothing to replace as Macro has already replaced strings all files (in A ) for B2.
Remember In B column(FIND)
B2=B10=B18=B26
B3=B11=B19=B27
B4=b12=B20=B28
so when macro starts it replacing B2 with C2 in A2,A10,A18,A26.

Issue in one line is that Macro replacing all instead of 1st instance. I tried .global=False also but not working.
so not sure whats wrong. Attached input file.

 

[Peter_SSs]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: make different changes to each of 32 text files in one folder using VBA
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Also, when posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details.