Copy specific positions from text file into excel

[excell+access]

Greetings, I am still so lost on VBA and would love some help how to copy or import specific data from long text files to build a smaller excel report.

Sample text files I believe are Tab Delimited
The file is long and has multiple unique records in each text file.
To identify each of the unique records it has a Control Number.
It looks like this: CTR_:_###########
Then below that is the main reference items to the Control Number
Below that is all of the events that were recorded against the Control Number
Then a new record will follow.

I want to be able to Extract out certain data elements from the record post into an excel document
then repeat till the end of the file.

Thoughts?

Sample File


CTR : 1234567890

F1 F2 F3(DTTM) F4 F5 F6 F7 F8 F9
-----------------------------------------------------------------------------------------------------------
Data1 Data2 DTTM Data4 Data5 Data6 Data7 Data8 Data9
F10 : Data10 F11 : Data11
F12 : Data12 F13 : Data13
------------------------------------------------------ ---------------------------------------------------
F14 : Data15 F16 : Data16
F17 : Data17 F18 : Data18
F19 : Data19 F20 : Data20
F21 : Data21 F22 : Data22
F23 : Data23 F23 : Data23
F24 : Data24 F25 : Data25
F26 : Data26 F27 : Data27
F28 : Data28 F29 : Data29
F29 : Data29 F30 : Data30
F31 : Data31

F32 F33 F34 F35 F36 F37 F38 F39 F40 F41
----------------------------------------------------------------------------------------------------------------------
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41
Data32 Data33 Data34 Data35 Data36 Data37 Data38 Data39 Data40 Data41


Then a new record

 

[mjbeam]

The code below will open a text file and read it in 1 line at a time, dumping the contents to the activesheet.

r = 1
Open "C:\YourFile.txt" for Input as 1
While Not EOF(1)
   Line Input #1, t
   c = 1
   Do
      If Instr(t, vbTab) > 0 Then
         s = Left(t, Instr(t, vbTab) - 1)
         t = Mid(t, Instr(t, vbTab) + 1)
      Else
         s = t
         t = ""
      End If
      Cells(r, c) = s
      c = c + 1
   Loop Until t = ""
   r = r + 1
Wend
Close 1

You can modify it to look for "CTR :" and only dump the data you are interested in into the worksheet