swaggerbox
06-24-2013, 10:41 PM
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
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