title: Extract data from Text File into Excel in vba
body: <p>I need to extract data from text file into Excel file. I once asked at <a href="https://stackoverflow.com/questions/66252338/vbscript-extract-data-from-text-file-into-excel">Vbscript extract data from Text File into Excel</a></p>
  <p>But after trying for few weeks and still no success so I use vba instead.
  Here what i have:</p>
  <pre><code>Sub ExtractData()
  
  Dim filename As String, nextrow As Long, MyFolder As String
  Dim MyFile As String, text As String, textline As String, filedate As String
  Dim filenum As Integer
  Dim idx%
  
  MyFolder = &quot;D:\Automation\VSWR\&quot;
  MyFile = Dir(MyFolder &amp; &quot;VSWR W51.txt&quot;)
  
  nextrow = ActiveSheet.Cells(Rows.Count, &quot;A&quot;).End(xlUp).Row + 1
  
  Cells(1, 1).Value = &quot;eNodeBName&quot;
  Cells(1, 2).Value = &quot;Time&quot;
  Cells(1, 3).Value = &quot;MML SN&quot;
  Cells(1, 4).Value = &quot;MML Command&quot;
  Cells(1, 5).Value = &quot;Retcode&quot;
  Cells(1, 6).Value = &quot;Explain_info&quot;
  Cells(1, 7).Value = &quot;Cabinet No.&quot;
  Cells(1, 8).Value = &quot;Subrack No.&quot;
  Cells(1, 9).Value = &quot;Slot No.&quot;
  Cells(1, 10).Value = &quot;TX Channel No.&quot;
  Cells(1, 11).Value = &quot;VSWR(0.01)&quot;
  'Columns(1).EntireColumn.AutoFit
  
  Do While MyFile &lt;&gt; &quot;&quot;
  
  Open (MyFolder &amp; MyFile) For Input As #1
  
      Do Until EOF(1)
          Line Input #1, textline 'read a line
          
          idx = InStr(textline, &quot;NE&quot;)
          If idx &gt; 0 Then
              'ActiveSheet.Cells(nextrow, &quot;A&quot;).Value = Mid(textline, idx + 5)
              ActiveSheet.Cells(nextrow, &quot;A&quot;).Value = Mid(textline, filenum + 5)
          End If
  
          idx = InStr(textline, &quot;Report&quot;)
          If idx &gt; 0 Then
              ActiveSheet.Cells(nextrow, &quot;B&quot;).Value = Right(textline, filenum + 19)
          End If
          
          idx = InStr(textline, &quot;O&amp;M&quot;)
          If idx &gt; 0 Then
              ActiveSheet.Cells(nextrow, &quot;C&quot;).Value = (&quot;O&amp;M&quot; &amp; Mid(textline, filenum + 4))
          End If
          
          
          idx = InStr(textline, &quot;MML Session&quot;)
          If idx &gt; 0 Then
              ActiveSheet.Cells(nextrow, &quot;D&quot;).Value = &quot;DSP VSWR:;&quot;
          End If
          
          
          idx = InStr(textline, &quot;RETCODE&quot;)
          If idx &gt; 0 Then
              ActiveSheet.Cells(nextrow, &quot;E&quot;).Value = &quot;0&quot;
          End If
             
          idx = InStr(textline, &quot;RETCODE&quot;)
          If idx &gt; 0 Then
              ActiveSheet.Cells(nextrow, &quot;F&quot;).Value = Mid(textline, filenum + 12)
  
              'nextrow = nextrow + 1 'now move to next row
          End If
                   
          idx = InStr(textline, &quot;Cabinet No.&quot;)
          If idx &gt; 0 Then
          
              Line Input #1, textline
              Line Input #1, textline
              ActiveSheet.Cells(nextrow, &quot;G&quot;).Value = Mid(textline, filenum + 1)
              
              nextrow = nextrow + 1 'now move to next row
          End If
      Loop  
  Close #1
  MyFile = Dir()
  
  Loop
  End Sub
  </code></pre>
  <p>Almost successful but the only problem is i can't seem to figure out how to make this line split the data into 5 separate columns.</p>
  <pre><code>idx = InStr(textline, &quot;Cabinet No.&quot;)
  If idx &gt; 0 Then
          
  Line Input #1, textline
  Line Input #1, textline
  ActiveSheet.Cells(nextrow, &quot;G&quot;).Value = Mid(textline, filenum + 1)
              
  nextrow = nextrow + 1 'now move to next row
  End If`
  </code></pre>
  <p>Sample input in text file <a href="https://i.stack.imgur.com/V3ePc.png" rel="nofollow noreferrer">Input</a></p>
  <p>And my desired output should be like this <a href="https://i.stack.imgur.com/r4sAD.png" rel="nofollow noreferrer">Output</a></p>
  <p>Thanks in advance and really appreciate.</p>