如何从Excel中的文本文件中的特定行进行搜索 [英] How to do searching after a particular line in Text file from Excel

问题描述

我有一个如下文本文件

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  

使用不同的数据重复同样的操作。

The same will be repeated with different data.

我正在使用作业编号进行搜索，如果发现，则将开始时间和完成时间放在Excel表中。

I am doing the search with Job Number, if it is found then get the start time and Finish time into excel sheet.

我尝试过下面的代码

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

推荐答案

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

Here is a much faster method then looping though every line of the text file. You can easily convert this to a function :)

看到这个例子。

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

截图

这篇关于如何从Excel中的文本文件中的特定行进行搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！