Fellow Forum Members,
I need help developing a VBA script to batch text replace 120 TEXT files driven by a control list setup in Excel 2007. I need the VBA to perform as outlined below:
1) Open text file (by referencing path and filename data listed in Columns A & B)
2) Replace text within text file (by referencing data listed in Columns C & D)
3) Save changes done to text file
4) Close the text file.
5) Move on to next text file on list until end of list is reached.
In short, I’m seeking to utilize Excel as text replacer app to batch edit 120 NotePad text files all controlled from a batch list I have setup within Microsoft Excel 2007 with columns named: “PATH”, “FILENAME”, “ORIGINAL TEXT”, “REPLACEMENT TEXT” (see attachment). In the attached sample worksheet I have included a button named "Make Changes Now". By pressing on this button I would like the script to start running and make all changes to the files in the list.
Below is a VBA script that does not perform 100% the way I would like. However, I'm including it so that anyone out there willing to help me does not have to start from scratch with the coding. I hope the code below helps and I would be grateful if someone out there can modify it so that it performs as I described above.
[VBA]
Sub FindAndReplaceText()
Dim FileName As String
Dim FolderPath As String
Dim FSO As Object
Dim I As Integer
Dim SearchForWords As Variant
Dim SubstituteWords As Variant
Dim Text As String
Dim TextFile As Object
'Change these arrays to word you want to find and replace
SearchForWords = Array("TEST1", "TEST2", "TEST3")
SubstituteWords = Array("DONE1", "DONE2", "Done3")
'Change the folder path to where your text files are.
FolderPath = "C:\Root\Test"
Set FSO = CreateObject("Scripting.FileSystemObject")
FolderPath = IIf(Right(FolderPath, 1) <> "\", FolderPath & "\", FolderPath)
FileName = Dir(FolderPath & "\*.txt")
Do While FileName <> ""
Filespec = FolderPath & FileName
'Read all the file's text into a string variable.
Set TextFile = FSO.OpenTextFile(Filespec, 1, False)
Text = TextFile.ReadAll
TextFile.Close
'Scan the string for words to replace and write the string back to the file.
Set TextFile = FSO.OpenTextFile(Filespec, 2, False)
For I = 0 To UBound(SearchForWords)
Replace Text, SearchForWords(I), SubstituteWords(I)
Next I
TextFile.Write Text
TextFile.Close
FileName = Dir()
Loop
End Sub
[/VBA]
Any help greatly appreciated. Thanks.