因为你实际上有打开文本文件的代码 - 而不是Excel文件 - 我遵循了相同的方法

这样的东西

1. 目录用于循环遍历所有 txt 文件的问题特定文件夹。


2. 使用FileScriptingObject一次读取所有文本，进行替换，然后使用更新的文本写入文件。

代码

 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 

设置objFSO = CreateObject（scripting.filesystemobject）
 StrFolder =c：\macro\
 StrFileName = Dir（StrFolder&* .txt）

尽管StrFileName<> vbNullString 
设置objFil = objFSO.opentextfile（StrFolder& StrFileName）
 strAll = objFil.readall 
 objFil.Close 
设置objFil2 = objFSO.createtextfile（StrFolder& StrFileName）
 objFil2.Write替换（strAll，THIS，THAT）
 objFil2.Close 
 StrFileName = Dir 
循环
 End Sub 

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

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 txt 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