Extract UID value from text files

[swaggerbox]

I have multiple text files in C:\Reports and I want to extract the value of each UID from these files. There can be multiple UID value in a single text file. How can I do this and extract the information to an excel file beginning row 1 column 1? Attached is a sample text file.

[Bob Phillips]

Just open it and filter the data for <> XUID, then delete all visible rows.

[swaggerbox]

Could this not be done via VBA? Because I need to process more than a thousand text files.

[Bob Phillips]

Do it with one file with the macro recorder on, show us the created code, and we will modify it to make it generic for you.

[swaggerbox]

ok here it is. Change this code to make it applicable to all text files located at C:\My Reports. Need to change the filter range also depending on the total # rows.




Sub Macro1()
'
ChDir "C:\My Reports"
Workbooks.OpenText Filename:="N:\sample.txt", Origin:=437, StartRow:=1, _
DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter _
:=True, Tab:=True, Semicolon:=False, Comma:=False, Space:=True, Other _
:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array( _
5, 1), Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.AutoFilter
ActiveSheet.Range("$A$2:$A$15").AutoFilter Field:=1, Criteria1:="<UID>"
Range("B23").Select
End Sub

[Bob Phillips]

Sub Macro1()
Dim wb As Workbook
Dim rng As Range
Dim LastRow As Long

Workbooks.OpenText _
Filename:="C:\sample.txt", _
Origin:=437, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, _
Tab:=True, _
Semicolon:=False, _
Comma:=False, _
Space:=True, _
Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1)), _
TrailingMinusNumbers:=True

With ActiveWorkbook.Worksheets(1)

.Rows(1).Insert
.Range("A1").Value = "temp"
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

Set rng = .Range("A1").Resize(LastRow)
rng.AutoFilter
rng.AutoFilter Field:=1, Criteria1:="<>*<UID>*"
If Not rng Is Nothing Then

rng.SpecialCells (xlCellTypeVisible)
If Not rng Is Nothing Then rng.Delete
End If
End With
End Sub