1

I'm trying to create a VBE program that will open all files in a folder, pull out text specific text that appears in predictable places in all the files, and populate the text variables to a spreadsheet.

My issue is that I can't get the text variables to update to reflect the content of each file. Instead, I get the text variable associated with File # 1, all the way down the column.

Among other tinkering, I tried using FreeFile instead of explicitly numbering files, all to no avail. Guidance much appreciated.

Here's my code:

Sub Looping()

Dim objFSO As Object, objFolder As Object, objFile As Object, i As Integer

Dim text As String, Dim textline As String, Dim Author As String, Dim AuthPos As Integer

Set objFSO = CreateObject("scripting.FileSystemObject")

Set objFolder = objFSO.GetFolder("C:\Users\User\Desktop\Files")

i = 1

For Each objFile In objFolder.Files

Open objFile For Input As #1
Do Until EOF(1)
Line Input #1, textline
text = text & textline
Loop
Close #1

Author = InStr(text, ", Author.")
Author = Mid(text, Author - 15, Author)
Author = Left(Author, 15)
AuthPos = InStr(Author, ".")
Author = Right(Author, Len(Author) - AuthPos)

Cells(i + 1, 1) = objFile.Name
Cells(i + 1, 2) = objFile.Path
Cells(i + 1, 3) = Author
i = i + 1

Next objFile

End Sub
ZygD
  • 22,092
  • 39
  • 79
  • 102
TGH
  • 11
  • 2
  • Apologies, all, I found an excellent answer here: http://stackoverflow.com/questions/28851974/extract-a-single-line-of-data-from-numerous-text-files-and-import-into-excel – TGH Mar 15 '15 at 20:15

1 Answers1

0

Your sub works!!!

Just add the following line before going for the next loop:

text = vbNullString

The text variable was not emptied, therefore it was always appended with data from other files, so the last file contained the data from all the files before it.

ZygD
  • 22,092
  • 39
  • 79
  • 102