如何在 VBA 中使用 FileSystemObject 替换文本文件行中的字符串？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19371990/
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
How do I Replace a String in a Line of a Text File Using FileSystemObject in VBA?
提问by aCarella
I am trying to use FileSystemObject methods to find a specific line in a text file, and within that line replace a specific string. I am relatively new to this, as my current code has excel open the text file and replace what I need it to replace then save and close it. This way is no longer an option, as having excel open the text file takes too long and holds up the file.
我正在尝试使用 FileSystemObject 方法在文本文件中查找特定行，并在该行中替换特定字符串。我对此比较陌生，因为我当前的代码具有 excel 打开文本文件并替换我需要替换的内容，然后保存并关闭它。这种方式不再是一种选择，因为让 excel 打开文本文件需要很长时间并阻止文件。
This is how far I have gotten so far.
这是到目前为止我已经走了多远。
-
——
Sub FindLines()
Const ForReading = 1
Set FSO = CreateObject("Scripting.FileSystemObject")
Set objFSO = FSO.OpenTextFile("C:\Users\Carella Home\Desktop\boomboom.txt", ForReading, False)
Do Until objFSO.AtEndOfStream = True
go = objFSO.ReadLine
If InStr(1, go, "ant", vbTextCompare) > 0 Then
bo = Replace(go, "t", "wow")
End If
Loop
objFSO.Close
Set objFSO = FSO.OpenTextFile("C:\Users\Carella Home\Desktop\boomboom.txt", 2)
End Sub
-
——
The best I can do is open the file up to write, but I have no idea how to find the line and replace it with the line that I need to replace it with.
我能做的最好的事情就是打开文件进行写入，但我不知道如何找到该行并将其替换为我需要替换的行。
Please let me know if, in the event that you are willing to help/guide me in the correct direction, you need more information. I have searched a lot and have seen people suggest other ways of doing this. I need to learn how to edit lines this way. Can someone please help me?
如果您愿意在正确的方向上帮助/指导我，请告诉我您是否需要更多信息。我已经搜索了很多，并且看到人们建议其他方法来做到这一点。我需要学习如何以这种方式编辑线条。有人可以帮帮我吗？
Thanks in advance!
提前致谢！
-Anthony C.
- 安东尼 C.
回答by David Zemens
Not sure if this is the most efficient method but one idea would be to use the CreateTextFilemethod of the FileSystemObject, to create another file you can write to.
不确定这是否是最有效的方法，但一个想法是使用 , 的CreateTextFile方法FileSystemObject来创建另一个可以写入的文件。
I've tested this on a small file and appears to be working as expected.
我已经在一个小文件上对此进行了测试，并且似乎按预期工作。
Modified after answer accepted to avoid .ReadLineand .WriteLineloops
接受答案后修改以避免.ReadLine和.WriteLine循环
Sub FindLines()
'Declare ALL of your variables :)
Const ForReading = 1 '
Const fileToRead As String = "C:\Users\david_zemens\Desktop\test.txt" ' the path of the file to read
Const fileToWrite As String = "C:\Users\david_zemens\Desktop\test_NEW.txt" ' the path of a new file
Dim FSO As Object
Dim readFile As Object 'the file you will READ
Dim writeFile As Object 'the file you will CREATE
Dim repLine As Variant 'the array of lines you will WRITE
Dim ln As Variant
Dim l As Long
Set FSO = CreateObject("Scripting.FileSystemObject")
Set readFile = FSO.OpenTextFile(fileToRead, ForReading, False)
Set writeFile = FSO.CreateTextFile(fileToWrite, True, False)
'# Read entire file into an array & close it
repLine = Split(readFile.ReadAll, vbNewLine)
readFile.Close
'# iterate the array and do the replacement line by line
For Each ln In repLine
ln = IIf(InStr(1, ln, "ant", vbTextCompare) > 0, Replace(ln, "t", "wow"), ln)
repLine(l) = ln
l = l + 1
Next
'# Write to the array items to the file
writeFile.Write Join(repLine, vbNewLine)
writeFile.Close
'# clean up
Set readFile = Nothing
Set writeFile = Nothing
Set FSO = Nothing
End Sub
Then, depending on whether you want to get rid of the "original" file you could do something like:
然后，根据您是否想删除“原始”文件，您可以执行以下操作：
'# clean up
Set readFile = Nothing
Set writeFile = Nothing
'# Get rid of the "old" file and replace/rename it with only the new one
Kill fileToRead
Name fileToWrite As fileToRead
End Sub
回答by Siddharth Rout
Here is a much faster and shorter method as compared to the FileSystemObject
与以下方法相比，这是一种更快、更短的方法 FileSystemObject
Sub Sample()
Dim MyData As String, strData() As String
'~~> Read the file in one go!
Open "C:\Users\Carella Home\Desktop\boomboom.txt" For Binary As #1
MyData = Space$(LOF(1))
Get #1, , MyData
Close #1
strData() = Split(MyData, vbCrLf)
End Sub
All your text file data is now in the array strDataSimply loop though the array and find the text that you want to replace and write it back to the SAMEfile.
您所有的文本文件数据现在都在数组中strData只需循环遍历数组并找到要替换的文本并将其写回相同的文件。
