title: Extract string from multiple files using vba
body: <p>I need to extract data from a set of text files inside a folder. I tried several times without success, I hope that someone can help me.</p>
  <p>All the files I have to read are inside the folder <code>C:/test</code>. The data I need to extract from the text files is located after the key word <em>Read BRT Luminance</em>.
  The data should be placed in an excel file, every data extracted from a single text file inside a different cell.</p>
  <p>I tried with this macro, but it doesn't work:</p>
  <pre><code>Dim myFile As String, myFolder As String, text As String, textline As String, originatorName As String, entryDescription As String, amount As Long
  
  Sub Button1_Click()
  
      Dim fs, f, f1, fc
      Dim cella
  
      cella = A2
  
      'Add column headers
      Range(&quot;A1&quot;).Value = &quot;Brightness&quot;
  
      Set fs = CreateObject(&quot;Scripting.FileSystemObject&quot;)
      Set f = fs.GetFolder(&quot;C:\test&quot;)
      Set fc = f.Files
      For Each f1 In fc
          If InStr(1, f1.Name, &quot;.txt&quot;) Then
  
              'Open file
              Open f1 For Input As #1
  
              Do Until EOF(1)
                  Line Input #1, textline
                  text = text &amp; textline
              Loop
  
              'Close file
              Close #1
  
              ReadBRTLuminance = InStr(text, &quot;Read BRT Luminance&quot;)
  
              ActiveCell.Offset(cella, 1).Value = Mid(text, ReadBRTLuminance + 31, 9)
              cella = cella + 1
  
          End If
      Next
  End Sub
  </code></pre>
  <p>My macro to extract the data I need from a single file works fine:</p>
  <pre><code>Dim myFile As String, myFolder As String, text As String, textline As String, originatorName As String, entryDescription As String
  Dim amount As Long
  
  Sub Button1_Click()
      'Add column headers
      Range(&quot;A1&quot;).Value = &quot;Brightness&quot;
  
      'Show open file dialog box
      myFile = Application.GetOpenFilename()
  
      'Open file
      Open myFile For Input As #1
  
      Do Until EOF(1)
          Line Input #1, textline
          text = text &amp; textline
      Loop
  
      'Close file
      Close #1
  
      ReadBRTLuminance = InStr(text, &quot;Read BRT Luminance&quot;)
  
      Range(&quot;A2&quot;).Value = Mid(text, ReadBRTLuminance + 31, 9)
  
  End Sub
  </code></pre>