vba 在文件夹中的所有excel文件中查找和替换字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14840574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find and replace string in all excel files in folder
提问by user1570210
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"
例如 fofler ="C:\ifolder\"文件列表 ="*.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
回答by brettdj
As you actually have code that opens text files - not Excel files - I have followed the same approach
由于您实际上有打开文本文件的代码 - 而不是 Excel 文件 - 我遵循了相同的方法
Something like this where
像这样的地方
Diris used to loop through all txtfiles 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.
Dir用于遍历特定文件夹中的所有txt文件。- 使用 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
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
