PDA

View Full Version : VBA Find and Replace from Excel Range



swaggerbox
06-10-2016, 06:21 AM
Found this code on the web. This will find and replace certain strings in text files. How do I revise this so that the array values is taken from an Excel range instead of hardcoding this in the code, e.g. values in Range A1:A3 instead of TEST1, TEST2 and TEST3, and values in Range B1:B3 for DONE1, DONE2, and DONE3? Any help would be appreciated.



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

SamT
06-10-2016, 06:50 AM
SearchForWords = Range("A1:A3).Value
SubstituteWords = Range("B1:B3").

swaggerbox
06-10-2016, 06:53 AM
that simple?