从文本文件中搜索字符串并使用 VBA 返回行号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19908173/
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
Search a string from text file & Return the Line Number using VBA
提问by Solution Seeker
I have one text file that contains around 100K lines. Now I would like to search a string from the text file. If that string is present then I want to get the line number at which it's present. At the end I need all the occurrence of that string with line numbers from the text file.
我有一个包含大约 10 万行的文本文件。现在我想从文本文件中搜索一个字符串。如果该字符串存在，那么我想获取它所在的行号。最后，我需要文本文件中所有出现的带有行号的字符串。
* Ordinary Method Tried *We can read the whole text file line by line. Keep a counter variable that increases after every read. If I found my string then I will return the Counter Variable. The limitation of this method is, I have to traverse through all the 100K lines one by one to search the string. This will decrease the performance.
* 尝试过普通方法 *我们可以逐行读取整个文本文件。保留一个每次读取后都会增加的计数器变量。如果我找到了我的字符串，那么我将返回计数器变量。这种方法的局限性是，我必须逐行遍历所有 100K 行来搜索字符串。这会降低性能。
* Quick Method (HELP REQUIRED)*Is there any way that will directly take me to the line where my searchstring is present and if found I can return the line number where it's present.
* 快速方法（需要帮助）*有什么方法可以直接将我带到我的搜索字符串所在的行，如果找到，我可以返回它所在的行号。
* Example *
* 例子 *
Consider below data is present in text file. (say only 5 lines are present)
考虑以下数据存在于文本文件中。（假设只有 5 行）
Now I would like to search a string say "Pune". Now after search, it should return me Line number where string "pune" is present. Here in this case it's present in line 2. I should get "2" as an output. I would like to search all the occurrence of "pune" with their line numbers
现在我想搜索一个字符串说“Pune”。现在搜索后，它应该返回我存在字符串“pune”的行号。在这种情况下，它出现在第 2 行。我应该得到“2”作为输出。我想用他们的行号搜索所有出现的“pune”
回答by KeithG
I used a spin off of Me How's code example to go through a list of ~10,000 files searching for a string. Plus, since my html files have the potential to contain the string on several lines, and I wanted a staggered output, I changed it up a bit and added the cell insertion piece. I'm just learning, but this did exactly what I needed and I hope it can help others.
我使用了 Me How 的代码示例的衍生版本来浏览大约 10,000 个文件的列表以搜索字符串。另外，由于我的 html 文件有可能在多行中包含字符串，并且我想要一个交错的输出，我对其进行了一些更改并添加了单元格插入部分。我只是在学习，但这正是我所需要的，我希望它可以帮助其他人。
Public Sub ReadTxtFile()
Dim start As Date
start = Now
Dim oFSO As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Dim oFS As Object
Dim filePath As String
Dim a, b, c, d, e As Integer
a = 2
b = 2
c = 3
d = 2
e = 1
Dim arr() As String
Do While Cells(d, e) <> vbNullString
filePath = Cells(d, e)
ReDim arr(5000) As String
Dim i As Long
i = 0
If oFSO.FileExists(filePath) Then
On Error GoTo Err
Set oFS = oFSO.OpenTextFile(filePath)
Do While Not oFS.AtEndOfStream
arr(i) = oFS.ReadLine
i = i + 1
Loop
oFS.Close
Else
MsgBox "The file path is invalid.", vbCritical, vbNullString
Exit Sub
End If
For i = LBound(arr) To UBound(arr)
If InStr(1, arr(i), "Clipboard", vbTextCompare) Then
Debug.Print i + 1, arr(i)
Cells(a + 1, b - 1).Select
Selection.Insert Shift:=xlDown
Cells(a, b).Value = i + 1
Cells(a, c).Value = arr(i)
a = a + 1
d = d + 1
End If
Next
a = a + 1
d = d + 1
Loop
Debug.Print DateDiff("s", start, Now)
Exit Sub
Err:
MsgBox "Error while reading the file.", vbCritical, vbNullString
oFS.Close
Exit Sub
End Sub
回答by Ron Rosenfeld
Here's another method that should work fairly quickly. It uses the shell to execute the FINDSTR command. If you find the cmd box flickers, do an internet search for how to disable it. There are two options provided: one will return the line number followed by a colon and the text of the line containing the keyword. The other will just return the line number.
这是另一种应该很快起作用的方法。它使用 shell 来执行 FINDSTR 命令。如果您发现 cmd 框闪烁，请在互联网上搜索如何禁用它。提供了两个选项：一个将返回行号，后跟一个冒号和包含关键字的行的文本。另一个只会返回行号。
Not sure what you want to do with the results, so I just have them in a message box.
不确定你想对结果做什么，所以我只是把它们放在一个消息框中。
Option Explicit
'Set reference to Windows Script Host Object Model
Sub FindStrings()
Const FindStr As String = "Pune"
Const FN As String = "C:\users\ron\desktop\LineNumTest.txt"
Dim WSH As WshShell
Dim StdOut As Object
Dim S As String
Set WSH = New WshShell
Set StdOut = WSH.Exec("cmd /c findstr /N " & FindStr & Space(1) & FN).StdOut
Do Until StdOut.AtEndOfStream
S = S & vbCrLf & StdOut.ReadLine
'If you want ONLY the line number, then
'S = S & vbCrLf & Split(StdOut.ReadLine, ":")(0)
Loop
S = Mid(S, 2)
MsgBox (S)
End Sub
回答by joe
the following fragment could be repalaced like:
以下片段可以像这样重新放置：
Dim arr() As String
Dim i As Long
i = 0
If oFSO.FileExists(filePath) Then
On Error GoTo Err
Set oFS = oFSO.OpenTextFile(filePath)
Do While Not oFS.AtEndOfStream
ReDim Preserve arr(0 To i)
arr(i) = oFS.ReadLine 'to save line's content to array
'If Len(oFSfile.ReadLine) = 0 Then Exit Do 'to get number of lines only
i = i + 1
Loop
oFS.Close
Else
MsgBox "The file path is invalid.", vbCritical, vbNullString
Exit Sub
End If
