Need help to convert a Text file to Excel via VBA Macro

[DEvOTeEL]

Sponsored Links

Dear all, I have got a text file containing the following sample data. I would like to convert some of the data into excel file. Basically i just want the field name and the value to be converted into the excel. Can someone help me on how to convert it into excel using excel macro.
Example :

START-OF-FILE
REFNUMBER=12345
DATEFORMAT=yyyymmdd

START-OF-FIELDS
FIELD1
FIELD2
FIELD3
FIELD4
.
.
.

FIELD120
END-OF-FIELDS

START-OF-DATA
VALUEA1| VALUEA2| VALUEA3| VALUEA4|. . . | VALUEA120
VALUEB1| VALUEB2| VALUEB3| VALUEB4|. . . | VALUEB120
.
.
.
VALUE(N)| VALUE(N)2| VALUE(N)3| VALUE(N)4|. . . | VALUE(N)120
END-OF-DATA

END-OF-FILE

Preferred excel format:

FIELD1 FIELD2 FIELD3 FIELD4 . . . . FIELD120
VALUEA1 VALUEA2 VALUEA3 VALUEA4 . . . . VALUEA120
VALUEB1 VALUEB2 VALUEB3 VALUEB4 . . . . VALUEB120
.
.
.
VALUE(N) VALUE(N)2 VALUE(N)3 VALUE(N)4. . . VALUE(N)120

[JyotiT]

Try the below code:

Sub Convert()
    Open "c:/myfile.txt" For Input As #1
    While Not EOF(1)
        Input #1, mydata
        If mydata = "START-OF-FIELDS" Then
            For col = 1 To 120
                Input #1, mydata
                Cells(1, col) = mydata
            Next col
        End If
        If mydata = "START-OF-DATA" Then
            For col = 1 To 120
                Input #1, mydata
                Cells(2, col) = mydata
            Next col
        End If
    Wend
    Close 1
End Sub