Auto-reading a txt file

[pichapple]

Hello,

How can I get Excel to copy a TXT file, line by line but start a new sheet when it reads a line starting with specific characters...

For example... here's the sequence I need it to follow

import a txt file, (ask user for file)
import line by line until it reaches a line begining with "Vessel: " (there is other text after this initial text marker)
when reached, begin a new sheet and follow same process from remaining file...

Basically, I have a HUGE text file which needs to be broken down into separate files/sheet, and need a way of doing this without doing it manually...

Thanks everyone!

 

[Ruddles]

Easy. Show us a sample of your data.

How would you like the worksheets named?

 

[snb_]

Sub snb()
  open "E:\import.txt" for input as #1
    sq=split(input(LOF(1),#1),vbcrlf & "Vessel")
  close #1
 
  for j=0 to ubound(sq)
    with thisworkbook.sheets.add
      sn=split(sq(j).vbcrLF)
      .cells(1).resize(ubound(sn)+1)=application.transpose(sn)
    end with
  next
End Sub

 

[Ruddles]

Looks like it's done!

 

[pichapple]

Thanks!

ok, so Im not quite an excel wizz, so excuse my questions. I pasted this into a macro, but it got stuck on the line

sn = Split(sq(j).vbCrLf)

If it's easier, I can post a data sample?

Thanks for your help everyone!

 

[schielrn]

That period should be a comma in that line:

sn = Split(sq(j),vbCrLf)

Hope that helps.