Need help to convert a Text file to Excel via VBA Macro
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 :
Code:
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:
Code:
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
Re: Need help to convert a Text file to Excel via VBA Macro
Try the below code:
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