Find and replace string in all excel files in folder

Question

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

The very same trouble made me learn VBA 4 months ago from complete zero-level))) — Peter L.
– Peter L., Commented Feb 12, 2013 at 20:57
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
– Tim Williams, Commented Feb 12, 2013 at 21:54

brettdj · Accepted Answer · 2013-02-13 09:54:46Z

3

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

Something like this where

Dir is used to loop through all txt files in a specific folder.
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

answered Feb 13, 2013 at 9:54

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

gaurav kumar Over a year ago

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.

brettdj Over a year ago

@guaravkumar place it in a regular code module. Ensure your path name has been updated (from C;\macro\ above).

gaurav kumar Over a year ago

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 :)

brettdj Over a year ago

@gauravkumar suggest you ask a new question :)

gaurav kumar Over a year ago

I have asked a new question stackoverflow.com/questions/32744968/…

Collectives™ on Stack Overflow

Find and replace string in all excel files in folder

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related