I am responding to this post to encourage you to dig in and start coding these tasks yourself. After I have attempted to code a solution, then I use the forum when there seems to be a roadblock that I can't get past.
There is always more than one solution, but my plan would be to read the text file (with code similar to the code below), and identify the line numbers that start with one of the targets STC*A3 or STC*A7, and the line number above each target found.
Let's say you find either of these values on line #2 and line #37. This tells you that the information you are looking for is in lines #1 and #2, and lines #36 and #37. These line numbers can be stored in a "working" sheet and used in a second reading of the text file.
During the second reading I would store just the target text lines in the worksheet. You use a counter in your loop, so that a function such as the one below named isTargetLine, used in conjunction with the results produced by the code at the bottom, will allow you to retrieve only those lines that you need.
What you will need to do, and this will develop your VBA skills, is write another function to read through the text file a second time, pull out just the target lines, parse out the fields you want to keep, and place the results in a destination worksheet.
In the code, I used Sheet2 to store line numbers in column A. I would then use column B to store each target line of text, and parse the 6 fields from 2 rows into columns C-H on just 1 row, so you might end up with something like this in columns C-H:
TRN*2 STC*A7 1110029255 I60238731 A3 400
then in code put all data in cells C-H, skipping over every other row which would not have data in columns C-H, into the destination worksheet.
All this assumes that data in the text file is consistent and as clean as the sample in your post.
Good luck in your coding.
Code:
Function isTargetLine(LineNo As Long) As Boolean
Dim k As Long, tmpBool As Boolean
k = 1
Do While Sheets("Sheet2").Cells(k, 1).Value <> ""
If Sheets("Sheet2").Cells(k, 1).Value = LineNo Then
tmpBool = True
Exit Do
End If
k = k + 1
Loop
isTargetLine = tmpBool
End Functio
Code:
Function Identify_LineNumbers()
'read text file and put target line numbers in Sheet2
'for subsequent reading of the line above and the current line
Dim textFile As String, targetText As String, lineText As String
Dim fNum
Dim LineNumber As Long, i As Long
textFile = "C:\Temp\EDIError.txt"
targetText = "STC*A3 STC*A7"
Worksheets("Sheet2").Columns("A:H").ClearContents 'clear a place to work
fNum = FreeFile 'fNum will be the next available number, usually 1
Open textFile For Binary Access Read As #fNum 'open the file
LineNumber = 1
i = 1
Do While Not EOF(fNum) 'loop until end of file
On Error GoTo errorHandler
Line Input #fNum, lineText 'read data into variable
If InStr(targetText, Mid(lineText, 1, 6)) > 0 Then
'one of the 2 targets found; identify the line number above this line
Sheets("Sheet2").Cells(i, 1).Value = (LineNumber - 1)
i = i + 1
Sheets("Sheet2").Cells(i, 1).Value = LineNumber
i = i + 1
End If
LineNumber = LineNumber + 1
Loop
Exit_Identify_LineNumbers:
Close #fNum
Exit Function
errorHandler:
If Err = 62 Then
'input past end of file--text file had no end-of-file marker
'all done, go to the exit
Else
MsgBox "An unexpected error occurred reading the file" _
& vbCrLf & textFile & vbCrLf & vbCrLf & _
"Error #" & Err & vbCrLf & "Error description: " & Error
End If
Resume Exit_Identify_LineNumbers
End Function
dBforums
>
Retrieving some columns from closed workbook to Active
10-21-11, 04:54
Linear Mode
