Create Macro to replace text in .txt file from Excel sheet

[fOfOf]

Dear Community,

I am a beginner in excel macro, and hope to find an answer to my question in this great forum. I looked at the previous threads but couldn't find an answer, so I am posting my own thread. I would like to create a simple excel macro which modifies text in an existing file, using values located in an excel sheet.
I want the macro to open the text file (which is located in a specific folder), to find the text located in excel's cell A1, and to replace it by excel's cell B1. Then, it should find the text located in excel's cell A2, and to replace it by excel's cell B2, and so on.
Can anyone please help me with this issue, I've been trying it out myself but can not find a solution.
Thanks a lot for your help!

 

[Firefly2012]

Will there be multiple instances of the A1-"Description/" combination (ie so it will occur in the first two lines of the file and then maybe in line 51)? If so, do you wish to replace all such occurrences of Description/ or only the first?

 

[Gugan V]

Thanks for your effort..

"Description/" - this one will occur n number of times.

but I want to replace only the first occurrence.

Thanks again,
Gugan.

 

[Firefly2012]

So there could be multiple occurrences of the A column value in the text file but only the first occurrence do you want to run a replace on "Description/" value following the A column reference?

So in this example, only the red Description will need replacing and not the green?

RefName="CMSTDTCIT" xmlns="http://phaseforward.com/CSML/2009-06-11" “Description/” “CustomProperty”“Name”Item Required”/Name”“Value”true”/Value”“/CustomProperty”“CustomProperty”“Name”SDV Required”/Name”“Value”true”/Value”“/CustomProperty”“Title”CMSTDTCIT”/Title”“ "Description/"QuestionRef Id="c08c9e11-b53d-4203-b82c-66776a50aed3" /”“ShortQuestionRef Id="345ed9ff-48eb-4960-a51c-2498be976197" /”“Year Required="true" AllowUnknown="false"““Range Start="2010" End="2015" /”“/Year”“Month Required="true" AllowUnknown="false"/”“Day Required="true" AllowUnknown="true"/”“Hour Required="true" AllowUnknown="true"/”“Minute Required="true" AllowUnknown="true"/”“/DateTimeVariable”

 

[Gugan V]

Yep..your understanding correct.

Thanks.

 

[Firefly2012]

Give this a go:

Sub Replace_Text()
Dim strFile As String
Dim i As Integer
Dim strText As String
Dim cell As Range
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    strFile = .SelectedItems(1)
End With
i = FreeFile
strText = Space(FileLen(strFile))
With CreateObject("vbscript.regexp")
    .Global = False
    Open strFile For Binary Access Read Write As #i
        Get #i, , strText
        For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            .Pattern = "(" & Replace(Replace(Replace(Replace(cell.Value, "?", "\?"), "*", "\*"), "+", "\+"), ".", "\.") & _
                ".*?)(Description\/)"
            strText = .Replace(strText, "$1" & cell.Offset(, 1).Value)
        Next cell
        Put #i, 1, strText
    Close #i
End With
            
End Sub

 

[Gugan V]

Thanks you soo much..
This is working perfect

 

[Firefly2012]

You're welcome

 

[vineetnayan]

This is exactly what i was looking for. I just need one enhancement. Tried a lot but could not figure out.

I need to do a pattern replace. For example if i have "I*U" in A1 and "I hate U" in B1 then all the values in the text file like ""I love U, I miss U, I like U"" or any thing in this pattern should get replaced by I hate U.

PLease help..!!!

Here's one possibility:

Sub Replace_Text()
Dim strFile As String
Dim i As Integer
Dim strText As String
Dim cell As Range
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    strFile = .SelectedItems(1)
End With
i = FreeFile
strText = Space(FileLen(strFile))
With CreateObject("vbscript.regexp")
    .Global = True
    Open strFile For Binary Access Read Write As #i
        Get #i, , strText
        For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            .Pattern = Replace(Replace(Replace(Replace(cell.Value, "?", "\?"), "*", "\*"), "+", "\+"), ".", "\.")
            strText = .Replace(strText, cell.Offset(, 1).Value)
        Next cell
        Put #i, 1, strText
    Close #i
End With
            
End Sub

Run the macro and select the file and away it goes.

Note there will be an issue if the text to be replaced in A2 matches any part of the text already replaced in B1.

 

[Shivani9145]

Here's one possibility:

Sub Replace_Text()
Dim strFile As String
Dim i As Integer
Dim strText As String
Dim cell As Range
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = ThisWorkbook.Path
    If .Show <> -1 Then Exit Sub
    strFile = .SelectedItems(1)
End With
i = FreeFile
strText = Space(FileLen(strFile))
With CreateObject("vbscript.regexp")
    .Global = True
    Open strFile For Binary Access Read Write As #i
        Get #i, , strText
        For Each cell In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
            .Pattern = Replace(Replace(Replace(Replace(cell.Value, "?", "\?"), "*", "\*"), "+", "\+"), ".", "\.")
            strText = .Replace(strText, cell.Offset(, 1).Value)
        Next cell
        Put #i, 1, strText
    Close #i
End With
           
End Sub

Run the macro and select the file and away it goes.

Note there will be an issue if the text to be replaced in A2 matches any part of the text already replaced in B1.

Hi i am encountering same issue. Can you please help
Note there will be an issue if the text to be replaced in A2 matches any part of the text already replaced in B1.

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'). My code below (referred in one you post) 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.

 

[Joe4]

Hi i am encountering same issue. Can you please help
Note there will be an issue if the text to be replaced in A2 matches any part of the text already replaced in B1.

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'). My code below (referred in one you post) 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.

View attachment 107408

I think you would be better off posting your question to its own thread, instead of resurrecting a thread that is almost 10 years old (and none of the people involved in this thread have been active here in years!). It will have a much better chance of being seen if you create a new thread for it, as it will then appear in the "Unanswered threads" listing.