Parse Text File

[sgolder]

Hi,

I have a .txt file with a list of job numbers and job names etc...

I want to add a textbox to a userform with a command button, and when the command button is pressed the code will search through the text file and grab the line of information subject to the value in the textbox..

I.E.. Textbox1.value = "Job Test"

For Instance the Project "Job Test" Might be on line 500 of the text file. I want to be able to grabe this number and add it too a textbox..if it finds more that one occurance of "Job test" then it will add all the line numbers to the listbox...

Can anyone help?

Thanks...

Stuart

 

[Andrew Poulsom]

Can you post up a sample of your text file?

 

[sgolder]

Andrew,

test file set up as follows:

256 WESTON FAVELL Tesco
387 MILTON KEYNES Tesco
461 RYDE I.O.W. Tesco
478 HEREFORD Tesco
483 COVENTRY Tesco
502 WESTON SUPER MARE Tesco
549 HEREFORD Tesco
557 CONFERENCE SUITE Esso
573 CAMBRIDGE Tesco
602 ISLE OF WIGHT PETROL FILLING STATION Tesco
627 AVELEY Tesco
651 WESTON SUPER MARE Tesco
654 FERNDOWN Tesco

I want to say search for "Hereford" and it will display the line number in a textbox... but it needs to include for incomplete names i.e. "Heref" and still display the correct line number...if theres more that one it puts both of them in...

 

[Andrew Poulsom]

Try something like this:

Private Sub CommandButton1_Click()
'   *** Change path and file name to suit ***
    Const FileName As String = "C:\Temp\Jobs.txt"
    Dim FileNum As Integer
    Dim First As Boolean
    Dim Txt As String
    Dim Data As String
    FileNum = FreeFile
    First = True
    Open FileName For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, Data
        If InStr(1, UCase(Data), UCase(TextBox1.Text)) Then
            If First = True Then
                Txt = Data
                First = False
            Else
                Txt = Txt & vbCrLf & Data
            End If
        End If
    Wend
    Close #FileNum
    If Txt = "" Then
        MsgBox TextBox1.Text & " not found"
    Else
        TextBox2.Text = Txt
    End If
End Sub

 

[sgolder]

Cheers Andrew, that code works fine!

How do i change the code so that it inserts the results into a listbox with a new line in the listbox added for every result found?

Regards,

Stuart

 

[Andrew Poulsom]

Like this?

Private Sub CommandButton1_Click()
'   *** Change path and file name to suit ***
    Const FileName As String = "C:\Temp\Jobs.txt"
    Dim FileNum As Integer
    Dim First As Boolean
    Dim Data As String
    FileNum = FreeFile
    First = True
    Open FileName For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, Data
        If InStr(1, UCase(Data), UCase(TextBox1.Text)) Then
            If First = True Then
                ListBox1.Clear
                ListBox1.AddItem Data
                First = False
            Else
                ListBox1.AddItem Data
            End If
        End If
    Wend
    Close #FileNum
    If First = True Then
        MsgBox TextBox1.Text & " not found"
    End If
End Sub