excel - 如何在 Excel 中的文本文件中搜索特定行

Question

我有一个如下所示的文本文件

Job Number - 1234  
Job Name - Vaxation  
Start Time - 29-Apr-2013 10:30   
Finish Time - 29-Mar-2013 4:00 PM  
Job Number - 2349  
Job Name - Immunity  
Start Time - 29-Apr-2013 11:30  
Finish Time - 29-Mar-2013 2:00 PM  

不同的数据将重复相同的操作。

我正在使用工作编号进行搜索，如果找到，则将开始时间和完成时间输入到 Excel 表中。

我尝试了下面的代码

Public Function ReadTxtFile()
    Dim TxtLine As String
    Dim CntRow As Integer
    Dim CntRow1 As Integer
    Dim CntRow2 As Integer

    FNum = FreeFile()

    Open TxtFile For Input As #FNum

    CntRow1 = 0

    While Not EOF(FNum)
        Line Input #FNum, TxtLine
        If InStr(1, TxtLine, "1234", vbTextCompare) = 0 Then
            CntRow1 = CntRow1 + 1
        Else
            CntRow2 = CntRow1 + 1
        End If
    Wend

    Call Getdat
End Function

Function Getdat()
    Seek FNum, CntRow2

    While Not EOF(FNum)
        Line Input #FNum, TxtLine

        If InStr(1, TxtLine, "StartTime", vbTextCompare) <> 0 Then
            GLRow = ws_sh.Range("B:B").Cells.Find(Format(Now() - 1, DatFormat1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
            ws_sh.Range(Col1 & GLRow) = Format(Now() - 1, DatFormat1)
            ws_sh.Range(Col2 & GLRow) = Mid(TxtLine, 33, 5)
            Exit Function
        End If
    Wend
End Function

Answer 1

这是一种更快的方法，然后循环遍历文本文件的每一行。您可以轻松地将其转换为函数:)

请参阅此示例。

Sub Sample()
    Dim MyData As String, strData() As String
    Dim i As Long
    Dim Jno As Long, JnoToCheck
    Dim strStart As String, strFinish As String

    '~~> Read the entire text file in one GO
    Open "C:\Sample.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1 '<~~ Close the text file
    strData() = Split(MyData, vbCrLf)

    Jno = 2349

    '~~> Loop through the array
    For i = LBound(strData) To UBound(strData)
        '~~>. Check if it has "Job Number"
        If InStr(1, strData(i), "Job Number", vbTextCompare) Then
            '~~> Split the string to get the job no
            JnoToCheck = Val(Trim(Split(strData(i), "Job Number - ")(1)))
            '~~> Match it with the required number
            If Jno = JnoToCheck Then
                '~~> Get the relevant lines
                strStart = strData(i + 2)
                strFinish = strData(i + 3)

                Debug.Print Split(strStart, "Start Time - ")(1)
                Debug.Print Split(strFinish, "Finish Time - ")(1)                   
                Exit For
            End If
        End If
    Next i
End Sub

截图