Hi all,
I am trying to pull information from a text (.txt) file and input it into an excel spreadsheet. With the "instr" and "mid" statements, I can pull any information from the line that the searched text is on, however, I want to pull information from the two or three text lines after the searched line. The reason I want the lines after the search string line is because the search string line is always the same, but the information on the lines afterwards are never the same. Here's an example of one series of lines in the text file:
Case 1005
1 35 66 90
4 22 5 99
What I want to do is search the file for "Case" and then extract the two lines of data beneath it and place them on individual cells in the excel file. Here's what I have so far for searching the text file and extracting data.
'find Case number
Counter = 1
Open filename For Input As #1
Do While Not EOF(1)
Line Input #1, txtline ' Read line into variable.
txtloc = InStr(1, txtline, "Case")
If txtloc <> 0 Then
Output = Mid(txtline, txtloc + 4, 5)
ActiveCell.Offset(Counter, 0).Value = Output
Counter = Counter + 1
End If
Loop
Close #1
The results of this code is a list of all the case numbers (i.e. 1005, 1006, 1007). How do I obtain the two lines of data after the search string line Is there an offset function of some kind to use
Thanks
Text file search, return data from line other than search string line
NorthHaven
Here is one way of doing it. I have chosen to put all the data into an array and then process it, then you are able to access the next few lines.
If a case exists more than once it will list the relevant info for each occurence.
If there are more than two data lines after each Case, it will handle that too.
If your .txt file will have more than 1000 lines then just adjust the Dim statement
ChasAA
[Code]
Sub findCases()
Dim fileName As String
Dim lineArray(1000) As String
Dim lineCounter As Integer
Dim counter As Integer
Dim off As Integer
Dim caseNbr As String
Dim searchString As String
fileName = "C:\Chasdev\Cases.Txt"
Open fileName For Input As #1
lineCounter = 1
Do While Not EOF(1)
Line Input #1, lineArray(lineCounter)
lineCounter = lineCounter + 1
Loop
Close #1
lineCounter = lineCounter - 1
caseNbr = "1011"
' this has been hard coded for test purposes
' you will need to set this variable somehow
' write results to excel sheet
Range("A1").Select
off = 1
For counter = 1 To lineCounter
searchString = "Case " & caseNbr
z = InStr(lineArray(counter), searchString)
If z > 0 Then ' if the case number is found
Selection.Offset(off, 0) = lineArray(counter)
counter = counter + 1
off = off + 1
z = InStr(lineArray(counter), "Case")
Do While z = 0 And counter <= lineCounter ' write all lines until the next case or end of array is reached
Selection.Offset(off, 0) = lineArray(counter)
counter = counter + 1
off = off + 1
z = InStr(lineArray(counter), "Case")
Loop
End If
Next counter
End Sub
Abhayc
The following should give want you want:
Sub findcaseno()
'find Case number
counter = 1
Open fileName For Input As #1
Do While Not EOF(1)
Line Input #1, txtline ' Read line into variable.
If InStr(1, txtline, "Case") <> 0 Then
For i = 0 To 1 'for 2 lines of data
Line Input #1, txtline 'read data
a = Split(txtline, " ") 'data into array
For n = 0 To UBound(a) 'array to cells
ActiveCell.Offset(counter + i, n).Value = a(n)
Next
Next
counter = counter + 2
End If
Loop
Close #1
End Sub