title: Extracting data from large number of text files to excel
body: <p>I came across the VBA code (linked below) that I found very useful in importing data from text files into separate cells by defining a separator.
  The current code allows a single file to be specified and the data extracted from it. What I’m hoping to achieve is to extract data from a number of text files and adding the data from each file into a new row in excel. I’m having difficulties trying to add a loop into the code to achieve this. </p>
  
  <p>Would you be able to advise how this could be achieved?</p>
  
  <p><a href="http://www.cpearson.com/excel/ImpText.aspx" rel="nofollow noreferrer">http://www.cpearson.com/excel/ImpText.aspx</a></p>
  
  <pre><code>Sub ImportTextFile()
  
  Dim RowNdx As Long
  Dim ColNdx As Integer
  Dim TempVal As Variant
  Dim WholeLine As String
  Dim Pos As Integer
  Dim NextPos As Integer
  Dim SaveColNdx As Integer
  
  FName = "C:\Users\40044600\Documents\zdump\"
  MyFile = Dir(FName &amp; "*.txt")
  Sep = vbLf
  
  SaveColNdx = ActiveCell.Column
  RowNdx = ActiveCell.Row
  
  Do While MyFile &lt;&gt; ""
      Open (FName &amp; MyFile) For Input As #1
  
      While Not EOF(1)
          Line Input #1, WholeLine
          If Right(WholeLine, 1) &lt;&gt; Sep Then
              WholeLine = WholeLine &amp; Sep
          End If
          ColNdx = SaveColNdx
          Pos = 1
          NextPos = InStr(Pos, WholeLine, Sep)
          While NextPos &gt;= 1
              TempVal = Mid(WholeLine, Pos, NextPos - Pos)
              Cells(RowNdx, ColNdx).Value = TempVal
              Pos = NextPos + 1
              ColNdx = ColNdx + 1
              NextPos = InStr(Pos, WholeLine, Sep)
          Wend
          RowNdx = RowNdx + 1
      Wend
      Close #1
      MyFile = Dir()
      Debug.Print text
  Loop
  </code></pre>
  
  <p>EndMacro:
      On Error GoTo 0
      Application.ScreenUpdating = True
      Close #1
      ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
      ' END ImportTextFile
      ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
  End Sub</p>
  
  <p>Many thanks</p>