Search and copy data from text to Excel

[AjinkyaG]

Hi team,
I have one text file which contains data about list of errors.
I want to search only for ERROR word which have occurred multiple times in file and copy data quoted in '......' which is following the word ERROR=
Format is: ERROR = "short name of error.(number of words may vary,but the text following error is quoted in '...')" .
I tried this to open the file:-

Sub FileOpenDialogBox()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Filters.Add "Excel Files", " *.txt", 1
.Show
fullpath = .SelectedItems.Item(1)
End With
End Sub

And to search and copy data:-

Sub import()
Dim myFile As String, text As String, textline As String, posLat As Integer, posLong As Integer
myFile = "fullpath"
Open myFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1
posLat = InStr(text, "ERROR")

Range("A1").Value = Mid(text, posLat + 10, 5)

End Sub

with this script i am not able to copy each error from that text file and also data between '......' is not captured.
Can you please help me with it.

 

[Yongle]

Try this

Sub AjinkyaG()
    Const FindText = "ERROR  = "
    Dim fData As String, fPath As String, r As Long, lastB As Range, C As Range, colB As Range, itm As Range
    
    fPath = GetPath:    If fPath = "" Then GoTo TheEnd
    Set lastB = Range("B" & Rows.Count).End(xlUp)
    Set C = Range("C" & Rows.Count)
    Set colB = Range("B3", lastB)

    Open fPath For Input As #1
        Do Until EOF(1)
            Line Input #1, fData
            fData = LTrim(fData)
            r = WorksheetFunction.Max(lastB.Row, C.End(xlUp).Row) + 1
            If Left(fData, 9) = FindText Then
                fData = LTrim(Replace(Replace(fData, FindText, ""), "'", ""))
                For Each itm In colB
                    If itm.Offset(, 1) = vbNullString And InStr(1, fData, "Deal With " & itm, vbTextCompare) > 0 Then
                        r = itm.Row
                        Exit For
                    End If
                Next itm
                Range("C" & r) = fData
            End If
        Loop
    Close #1
Exit Sub
TheEnd:
MsgBox "file not selected", , ""
End Sub

For a more flexible match
replace
If itm.Offset(, 1) = vbNullString And InStr(1, fData, "Deal With " & itm, vbTextCompare) > 0 Then
with
If itm.Offset(, 1) = vbNullString And InStr(1, fData, itm, vbTextCompare) > 0 Then

 

[AjinkyaG]

r = WorksheetFunction.Max(lastB.Row, C.End(xlUp).Row) + 1

Can you please explain what the above code will do?
Because I think it will return the max value..is my thinking right?
Please reply.

 

[Yongle]

r = WorksheetFunction.Max(lastB.Row, C.End(xlUp).Row) + 1

Here Max is returning the biggest ROW number
(either the last used row in Column C or the last used row in column B)

"Rejects" go at the end of column C but must be BELOW end of column B
Max returns the correct last row to determine where the NEXT value is placed

lastB..Row is the last used ROW in column B
C.End(xlUp).Row is the last used ROW in column C


It is a more succinct method than

If C.End(xlUp).Row < lastB.Row Then
    r = lastB.Row + 1
Else
    r = C.End(xlUp).Row + 1
End If

 

[AjinkyaG]

Superb!!!!!!!!!!!!!!!Thanks A Lot...It worked..