如何在VBA中使用FileSystemObject替换文本文件行的字符串?

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。

2 个解决方案

#1

Not sure if this is the most efficient method but one idea would be to use the CreateTextFile method of the FileSystemObject, to create another file you can write to.

不确定这是否是最有效的方法，但一个想法是使用FileSystemObject的CreateTextFile方法创建另一个可以写入的文件。

I've tested this on a small file and appears to be working as expected.

我已经在一个小文件上测试了这个，并且看起来像预期的那样工作。

Modified after answer accepted to avoid .ReadLine and .WriteLine loops

修改后的答案被接受以避免。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

#2

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 strData Simply loop though the array and find the text that you want to replace and write it back to the SAME file.

所有文本文件数据现在都在数组strData中，只需循环遍历数组并找到要替换的文本并将其写回相同的文件。

#1