Read multiple .txt files and copy specific text to excel

[symplystyc]

Hello,
I have thousands of text files that I need to copy specific data from and put into columns in an excel spreadsheet. In the past I've used a variation of the code below and it has worked fine however the text files I now have have duplicate words that I need to search for. Sample Analysis Time/Date: Benezen etc.. What I would like to do is return a value for each searched term in a corresponding column.

current code

Row = 2
  'label columns
  ActiveSheet.Cells(Row, 1).Value = "Analysis Date/Time"
  ActiveSheet.Cells(Row, 2).Value = "Lims#"
  ActiveSheet.Cells(Row, 3).Value = "Benzene"
  ActiveSheet.Cells(Row, 4).Value = "Arom"
  ActiveSheet.Cells(Row, 5).Value = "RVP"
  ActiveSheet.Cells(Row, 6).Value = "Ron"
  ActiveSheet.Cells(Row, 7).Value = "Mon"
  ActiveSheet.Cells(Row, 8).Value = "R+M/2"
  ActiveSheet.Cells(Row, 9).Value = "T10"
  ActiveSheet.Cells(Row, 10).Value = "T50"
  ActiveSheet.Cells(Row, 11).Value = "T90"


  Set fso = CreateObject("Scripting.FileSystemObject")
  For Each f In fso.GetFolder("C:\Desktop\NIR\temp").Files
    If LCase(fso.GetExtensionName(f.Name)) = "txt" Then
    Row = Row + 1
      Set stream = f.OpenAsTextStream
      Do Until stream.AtEndOfStream
        Line = stream.ReadLine
        
        'search line for text, return value to right of text
        If Left(Line, 32) = "      Sample Analysis Time/Date:" Then
          ActiveSheet.Cells(Row, 1).Value = Right(Line, Len(Line) - 39)
        ElseIf Left(Line, 13) = "      Comment" Then
          ActiveSheet.Cells(Row, 2).Value = Right(Line, Len(Line) - 45)
          ElseIf Left(Line, 13) = "      Benzene" Then
          ActiveSheet.Cells(Row, 3).Value = Right(Line, Len(Line) - 39)
        'continue on for each search term
        
          
         Exit Do
        End If
      Loop
      stream.Close
    End If
    Next

Text file (could not upload this for some reason)

      Analyzer Description, Configuration 1       Configuration Name               CONFIGURATION 1         
      Model                            MB160D
      Serial Number                    SZM48067
      Sampling Accessory               Liquid Cell
      Sampling Device Serial Number    NONE
      Detector                         InAs
      Detector Serial Number           SZM480PK
####1
Analysis Results
.
      Sample Analysis Time/Date:       7:31 AM  1/1/14
      Spectrum:                        FAG35315
      Procedure:                        Reg 2009 
      Comment                          LIMS# 0939 
      Benzene (wt%)                    .61
      Arom. (GCMS vol.%)               16.44
      RVP (psi)                        8.88
      RON (Regular)                    91.13
      MON (Regular)                    83.76
      (R+M)/2 (Regular)200             87.32
      T10 (F)                          130.83
      T50 (F)                          213.62
      T90 (F)                          327.52
      End Point                        415.11
.               Total Samples:       1
.0 0 0 1 3825 1.00E+04 C:\AIRS\all\c2014_01\samples\FAg35315
      Sample Analysis Time/Date:       7:35 AM  1/1/14
      Spectrum:                        FAG35316
      Procedure:                        Reg 2009 
      Comment                          LIMS# 061
      Benzene (wt%)                    .84
      Arom. (GCMS vol.%)               15.17
      RVP (psi)                        14.74
      RON (Regular)                    88.59
      MON (Regular)                    80.41
      (R+M)/2 (Regular)200             84.63
      T10 (F)                          104.00
      T50 (F)                          173.95
      T90 (F)                          301.64
      End Point                        403.94
.               Total Samples:       2
.0 0 0 1 3825 1.00E+04 C:\AIRS\all\c2014_01\samples\FAg35316

Hoped for end result

Analysis Date/Time	Lims#	Benzene	Arom	RVP	Ron	Mon	R+M/2	T10	T50	T90
1/1/2014 7:31	939	0.61	16.44	8.88	91.13	83.76	87.32	130.83	213.62	327.52
1/1/2014 7:35	061	0.84	15.17	14.74	88.59	80.41	84.63	104	173.95	301.64

[SamT]

bump