Optimise this code Instr

[nabbster]

Hi guys i have the following code it works like this it parses a log file for certain info and returns the required data. the log has a set of info on every person logged but this may not be in order. this info i.e Height, Age, Hair colour, Sex. every line in the log has 2 references included in it so that all data relating to a person is not confused with another persons. eg

#1 a john is a man
#1 b Jackie is a woman
#1 c Peter is a man
#2 a Height of john is 5Ft
#2 b Height ofJackie is 5ft 3"
#3 c Jackie is Blond
#7 j peter is 39
#1 a John is 27

one small thing need to be concidered the refernce points in the log i.e #1,2,3
and a,b,c ect are formated different in some lines to others so i have had to split the reference points at spaces to #1, #2, #3 a,b,c these in the code are txtRefA and txtRefB

so now to the code it looks for Height, sex, haircolour ect based on the 2 reference points

so with a click i get all the details on one person.
i can change the reference points to bring details on any person i want in the log.

Open App.Path & "\00000712.log" For Input As #1
        Do Until EOF(1)
            Line Input #1, ln
              If InStr(1, ln, " Name") > 0 And InStr(1, ln, txtRefA) > 0 And InStr(1, ln, txtRefB) > 0 Then
                    pname = Mid(ln, InStr(1, ln, ": ") + 4)
                    txtPName = pname
                    uName = ln
                 End If
                If InStr(1, ln, "Hair") > 0 And InStr(1, ln, txtRefA) > 0 And InStr(1, ln, txtRefB) > 0 Then
                    Hcolour = Mid(ln, InStr(1, ln, "Hair"))
                    txtHcolour = Mid(Hcolour, 23)
                    Hcolour = ln
                End If
                If InStr(1, ln, " ShoeSize") > 0 And InStr(1, ln, txtRefA) > 0 And InStr(1, ln, txtRefB) Then
                    Size = Mid(ln, InStr(1, ln, "ShoeSize"))
                    txtSize = Mid(Size, 15)
                    Size = ln
                End If
                If InStr(1, ln, "Sex") > 0 And InStr(1, ln, txtRefA) > 0 And InStr(1, ln, txtRefB) > 0 Then
                    Sex = Mid(ln, InStr(1, ln, "Sex"))
                    Height = Split(Replace(Sex, ":", " "), " ")
                End If

the code works fine takes about 8 seconds to pull all info from 20mb log with ref (A) & (B). but to get next person info i change reference points and click again. to basically i am reloading the file and searching it. if i am not mistaken if i was to be able to just search the allready loaded file every time after opening it up once this would be faster but hey you tell me i really am no expert so if poss please keep any suggested changes to this code to a minimum.
my suggestion to improve it would be to open the file into the memory with one click and then just search it after changeing refA & refB for next lot of data. not sure if this is right or if so how to mod the code.

thanks in advnce

[MarkDorf]

You're using Line Input, which reads one line at a time, which is why you need to reload the file for each new search. Instead of Line Input, declare a string variable (for example, called strText) and do the following:

strText= Input(LOF(1), #1)

Then use InStr to parse out what you need from the strText variable.

[nabbster]

Thanks mark i tried the following and debug show nothing

Private Sub Command1_Click()
Dim strText As String
Open App.Path & "\00000712.log" For Input As #1
            strText = Input(LOF(1), #1)
                Debug.Print strText
                Close #1
End Sub

[nabbster]

Any takers to this problem?

[westconn1]

looks ok, post the log file

[nabbster]

i cant post the log but i can assure you its nothing to do with the log. maybe the info is loaded but its the way i write debug.print or some thing?
anyway that is not the reason for this post please anybody got any ideas on how to make this code faster?

[brucevde]

1) Each If statement has the exact same code (InStr(1, ln, txtRefA) > 0 And InStr(1, ln, txtRefB) > 0). Execute those statements once.

2) If I understand correctly, there will never be more than one type ("Name", "Hair","Shoesize") on a line. of that is correct use an If...ElseIf statement block instead.

Open App.Path & "\00000712.log" For Input As #1
        Do Until EOF(1)
            Line Input #1, ln
            If InStr(1, ln, txtRefA) > 0 Then 
               If InStr(1, ln, txtRefB) > 0 Then
                   ' line contains both txtRefA and txtRefB so get the data
 
                   If InStr(1, ln, " Name") > 0 Then
                       pname = Mid(ln, InStr(1, ln, ": ") + 4)
                       txtPName = pname
                       uName = ln
                   ElseIf InStr(1, ln, "Hair") > 0 Then
                       Hcolour = Mid(ln, InStr(1, ln, "Hair"))
                       txtHcolour = Mid(Hcolour, 23)
                       Hcolour = ln
                   ElseIf InStr(1, ln, " ShoeSize") > 0 Then
                       Size = Mid(ln, InStr(1, ln, "ShoeSize"))
                       txtSize = Mid(Size, 15)
                       Size = ln
                   ElseIf InStr(1, ln, "Sex") > 0 Then
                       Sex = Mid(ln, InStr(1, ln, "Sex"))
                       Height = Split(Replace(Sex, ":", " "), " ")
                   End If
 
               End If 
            End If