Extract values from Text files to Excel

[swaggerbox]

I have several text files in C:\My Report folder. I would like to extract the value of "<ID>" tags inside these files to Sheet 1, begining at range A1. Inside these text files are multiple datasets with several "<ID>" values.

I have attached a sample text file for this purpose.

The desired output is shown below:

Range A1: TP201883011-U
Range A2: TP201883011-U
Range A3: TP201883089-U
Range A4: TP201883091-U
Range A5: TP201883093-U
Range A6: TP201883095-U
Range A7: TP201883097-U

Can anyone help me get started?

[p45cal]

[vba]Sub IDTagsDownload()
FF = FreeFile
Open "C:\wherever your report folder is\sample.txt" For Input As #FF
Do While Not EOF(1)
Line Input #FF, textline
If Left(textline, 4) = "<ID>" Then Cells(Rows.Count, 1).End(xlUp).Offset(1).Value = Application.trim(mid(textline,5,len(textline)))
Loop
Close #FF
End Sub
[/vba]

[GTO]

Hi there,

Try this with the workbook in the same folder as the textfiles.
[VBA]Option Explicit

Sub exa3()
Dim FSO As Object '<-- FileSystemObject
Dim fsoTStream As Object '<-- TextStream
Dim fsoFile As Object '<-- File
Dim strLine As String

Set FSO = CreateObject("Scripting.FileSystemObject")
For Each fsoFile In FSO.GetFolder(ThisWorkbook.Path & "\").Files
If fsoFile.Type = "Text Document" Then
Set fsoTStream = FSO.OpenTextFile(fsoFile.Path, 1, False, &HFFFFFFFE)
Do While Not fsoTStream.AtEndOfStream
strLine = fsoTStream.ReadLine
If Left(strLine, 4) = "<ID>" Then
'// Note I used the sheet's codename//
Sheet1.Cells(Sheet1.Rows.count, 1).End(xlUp).Offset(1).Value = _
Trim(Replace(strLine, "<ID>", vbNullString))
End If
Loop
fsoTStream.Close
End If
Next
End Sub[/VBA]
Hope that helps,

Mark

[swaggerbox]

great! thanks guys!