2

I need help in creating find and replace string macro so that it can do find and replace string in all files in a folder.

For example fofler = "C:\ifolder\" files list = "*.xlsx"

so far I can only do it for one file, I need to do it for all file in a folder 

Sub ReplaceStringInFile()

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

' Edit as needed
sFileName = "C:\macro\test.txt"

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, "THIS", "THAT")

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub
Community
  • 1
  • 1
user1570210
  • 1,169
  • 12
  • 26
  • 37
  • 2
    please show what you tried and where you're stuck – scott Feb 12 '13 at 19:54
  • 1
    The very same trouble made me learn VBA 4 months ago from complete zero-level))) – Peter L. Feb 12 '13 at 20:57
  • 1
    You can help get an answer by providing more detail - what *exactly* are you having problems with? Do you know any VBA at all? Do you need to know how to loop though files in a folder? Open a workbook? – Tim Williams Feb 12 '13 at 21:54
  • 1
    http://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba – Sorceri Feb 12 '13 at 23:02

1 Answers1

3

As you actually have code that opens text files - not Excel files - I have followed the same approach

Something like this where

  1. Dir is used to loop through all files in a specific folder.
  2. Use the FileScriptingObject to read in all the text at once, make the replacement, then write over the file file with the updated text.

code

Sub  ReplaceStringInFile()

Dim objFSO As Object
Dim objFil As Object
Dim objFil2 As Object
Dim StrFileName As String
Dim StrFolder As String
Dim SstrAll As String

Set objFSO = CreateObject("scripting.filesystemobject")
StrFolder = "c:\macro\"
StrFileName = Dir(StrFolder & "*.txt")

Do While StrFileName <> vbNullString
    Set objFil = objFSO.opentextfile(StrFolder & StrFileName)
    strAll = objFil.readall
    objFil.Close
    Set objFil2 = objFSO.createtextfile(StrFolder & StrFileName)
    objFil2.Write Replace(strAll, "THIS", "THAT")
    objFil2.Close
    StrFileName = Dir
Loop
End Sub
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • dear thanks for the code. As, I am new to VBA can you please help me where to paste this code and run. I tried it by pasting in VBAProject->ThisWorkBook. It is not showing any error while running but at the same time it is not replacing i.e. not working. Can you please help. – gaurav kumar Sep 23 '15 at 07:21
  • 1
    @guaravkumar place it in a regular code module. Ensure your path name has been updated (from C;\macro\ above). – brettdj Sep 23 '15 at 09:26
  • Many thanks for the help. I am doing it for the excel files. I think this is the reason for the error: Input past end of file (Error 62). Can you please help me in writting the subsitute for opentextfile and createtextfile as used in the program. Many thanks in anticipation :) – gaurav kumar Sep 23 '15 at 11:17
  • @gauravkumar suggest you ask a new question :) – brettdj Sep 23 '15 at 14:36
  • I have asked a new question http://stackoverflow.com/questions/32744968/find-and-replace-string-in-all-excel-files-workbook-in-a-directory-using-vba – gaurav kumar Sep 23 '15 at 16:43