Importing Text Format to Excel

[ahbyun]

Hi! I have a question regarding bringing a text file to excel using VB.

The text file has the following format:

===================================================
A B C D E
===================================================
1 Paul Watson 43212 NY
2 Dave Miller 41422 TX
3 Steven Kay 39399 CA
===================================================

etc.


I want to import that into excel in the following form

Sample Sequence LastName FirstName State ID
AAA 1 Watson Paul NY 43212
AAA 2 Miller Dave TX 41422
BBA 3 Kay Steven CA 39399

etc...


Basically, I tried opening the Text format in Excel
but the "============" won't go away. Plus I want to rearrange the order and add a column.

I'm trying to use VB but I can't seem to find a good starting point. If anyone can help, I really would appreciate it! THANKS!!

 

[PaulRD]

Here's a starting point for you. I wrote this for someone else but with some modifications, it should be able to do what you need. Give it a try and post back if you get stuck

Private Sub ImportTextFile()
  Dim myText As String   Dim RowNum As Integer 
  Const FName As String = "C:\TXTDATA.txt" '**set this to your file name**

  RowNum = 3 'set this to the start row
  Open FName For Input Access Read As #1 '
  While Not EOF(1)
    Line Input #1, myText
    If Left(myText, 9) = "  UTILITY" Then
      Line Input #1, myText
      While Trim(myText) <> "END OF REPORT"
        If Left(myText, 1) <> " " Then
          Cells(RowNum, 1).Value = Trim(Left(myText, 19))
          Cells(RowNum, 2).Value = Trim(Mid(myText, 20, 18))
          Cells(RowNum, 3).Value = Trim(Mid(myText, 38, 13))
          Cells(RowNum, 4).Value = Trim(Mid(myText, 51, 6))
          RowNum = RowNum + 1
        End If
        Line Input #1, myText
      Wend
'     Close #1
'     Exit Sub
    End If
  Wend
  Close #1
End Sub

Paul D
pimpin' aint easy