title: VBA/VBE: Looping through all files in a folder, pulling text and populating to spreadsheet
body: <p>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.</p>
  
  <p>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. </p>
  
  <p>Among other tinkering, I tried using FreeFile instead of explicitly numbering files, all to no avail.  Guidance much appreciated.</p>
  
  <p>Here's my code:</p>
  
  <pre><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 &amp; 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
  </code></pre>