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

Sheet1.Cells(Sheet1.Rows.Count, 2).End(xlUp).Offset(1).Value = fsoFile.Name


after

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


which writes file names to column B

[swaggerbox]

thanks!

[mancubus]

you are welcome.