Include Filename in the Modified Macro

[swaggerbox]

How do I modify the macro so that the result would include the filename of the file where the data was extracted?

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

Please visit the link where the macro was originally suggested.

http://www.vbaexpress.com/forum/showthread.php?t=38573

[mancubus]

add this line
[VBA]
Sheet1.Cells(Sheet1.Rows.Count, 2).End(xlUp).Offset(1).Value = fsoFile.Name
[/VBA]

after
[VBA]
Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Offset(1).Value = _
Trim(Replace(strLine, "<ID>", vbNullString))
[/VBA]

which writes file names to column B

[swaggerbox]

thanks!

[mancubus]

you are welcome.