VBA for Find / Replace Operation

binar

Board Regular
Joined
Aug 20, 2006
Messages
71
Fellow Forum Members,
Can someone out there please help me out with a VBA scrpt that will do the following:

I have five TEXT files, and each of these TEXT files contains three different text entries. What I need is an EXCEL VBA script that will find and replace one set of text entries with three different text entries (ALL IN CAPS). Is it possible to write a script that will enable Excel to open each text file (located in specified path) then do a Find/Replace operation, and then close the TEXT file?

This is a basic example. In reality I have 50 text files each one containing three text entries I need changed to something else. I'm currently doing this find/replace work manually and I would like to automate it. Any help will be greatly appreciated. Thanks in advance.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hello

I suggest to use:

Code:
Sub ReplaceInTextFile()
    Open "C:\test.txt" For Input As #1
    c0 = Input(LOF(1), #1)
    Close #1

    Open "C:\test.txt" For Output As #1
    Print #1, Replace(c0, "OLD TEXT", "NEW TEXT")
    Close #1
End Sub
 
Upvote 0
Hello binar,

Here is another macro that allows you find and replace as many words as you want. You will need to change the file path and the words you will be using for the find and replace operation. These are marked in bold.
Rich (BB code):
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("this", "that", "from")
   SubstituteWords = Array("This", "That", "form")
 
  'Change the folder path to where your text files are.
   FolderPath = "C:\"
   
     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

Sincerely,
Leith Ross
 
Upvote 0
Leith,
Thanks a 1000 times for the code you posted. Just one question before I try it out. Is an "ARRAY" the same thing as a "Column" you see on a worksheet? This is the part of the code I'm referring to:


'Change these arrays to word you want to find and replace
SearchForWords = Array("this", "that", "from")
SubstituteWords = Array("This", "That", "form")



The reason I'm asking this is because I'm not too clear on how this Script needs the data setup. Logically I'm thinking the following takes place:

"Column A" contains the words Excel needs to FIND and then in "Column B" contains the words Excel needs to REPLACE, and then "COLUMN C" has the path and filename info. Evenmore, each row represents an individual FIND/REPLACE operation.

I will try the code out tomorrow and see if I understand how it works. Again, thanks a lot for sharing this code.
 
Last edited:
Upvote 0
Hello binar,

Here is another macro that allows you find and replace as many words as you want. You will need to change the file path and the words you will be using for the find and replace operation. These are marked in bold.
Rich (BB code):
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("this", "that", "from")
   SubstituteWords = Array("This", "That", "form")
 
  'Change the folder path to where your text files are.
   FolderPath = "C:\"
   
     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

Sincerely,
Leith Ross



Leith,
The script is not working and I think it's because I don't have the file names specified correctly.

Can you repost the script so that it shows the following information:

Filenames = File 1.txt, File 2.txt, File 3.txt, File 4.txt, File 5.txt
Path = C:\Root\Test

Each file contains the following three search words:
SearchForWords = TEST1 TEST2 TEST3

SubstituteWords = TEST1 changes to "DONE1", TEST2 changes to "DONE2", and TEST3 changes to "DONE3"

This way I can see what I am doing wrong. If you prefer to send me an Excel 2007 file instead, please send me a Personal Message and I'll give you an email. Thanks.
 
Upvote 0
Hello binar,

Here is updated macro. So you know, it will find all text files in the path "C:\Root\Test".
Code:
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
Sincerely,
Leith Ross
 
Upvote 0
Leith,
Can you give me some pointers on how to get this script to work. The part I don't understand is how to setup the ARRAY the scipt shows in the code below:


'Change these arrays to word you want to find and replace
SearchForWords = Array("TEST1", "TEST2", "TEST3")
SubstituteWords = Array("DONE1", "DONE2", "Done3")

I'm looking at Column A. Do I insert TEST1, TEST2 and TEST3 inside cells A1, A2, and A3, respectively. Where and how does this script read the data I want searched? I would appreciate it if you can explain what I need to do to get this to work.

Thanks.
 
Upvote 0
Hello Binar,

I think the quickiest way to solve this problem is for me to make the changes to your workbook code. You can post your workbook on a public file server, like Mediafire, or you can email a copy to me.

Sincerely,
Leith Ross
 
Upvote 0
Hello Binar,

I think the quickiest way to solve this problem is for me to make the changes to your workbook code. You can post your workbook on a public file server, like Mediafire, or you can email a copy to me.

Sincerely,
Leith Ross

Leith,
I have sent you a Private message with a mediafire link. Thanks for the help.
 
Upvote 0
Hello binar,


Hi Leith,

Thank you for this code. I couldn't get the replacement to work unfortunately. I do have a script that will convert the text properly, however this only works for 1 TEXT file at a time. Is there a way to convert this code to open multiple Text files and perform this replace ALL function? Any help will be greatly appreciated!!

Here is my code:
Code:
Sub looptexttest()




'remove extra characters from text file


Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As Variant


' Edit as needed
sFileName = Application.GetOpenFilename()


If sFileName = False Then
    MsgBox "No File Selected", vbExlamation
    Worksheets("Summary").Select
    Exit Sub
End If


iFileNum = FreeFile
Open sFileName For Input As iFileNum


Do Until EOF(iFileNum)
Line Input #iFileNum , sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum


sTemp = Replace(sTemp, ", ", "_")


'Save txt file as (if possible)


iFileNum = FreeFile
Open sFileName For Output As iFileNum


Print #iFileNum , sTemp


Close iFileNum


End Sub
 
Last edited by a moderator:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top