How to convert text file into excel using macro vba

[tyme]

Dear all, i have 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?

Sample text file content:

START-OF-FILE
REFNUMBER=12345
DATEFORMAT=yyyymmdd<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

START-OF-FIELDS
FIELD1
<o>FIELD2
FIELD3
FIELD4
.
.
.

FIELD120
END-OF-FIELDS

START-OF-DATA<o></o>
<o>VALUE1</o>
<o>VALUE2</o>
<o>VALUE3</o>
<o>VALUE4</o>
<o>.</o>
<o>.</o>
<o>.</o>
<o>VALUE120</o>
<o>END-OF-DATA
END-OF-FILE

Preferred excel format:

FIELD1 FIELD2 FIELD3 FIELD4 . . . . FIELD120
<o>VALUE1 VALUE2 VALUE3 VALUE4 . . . . VALUE120
</o></o></o>

 

[tyme]

hi diddi,

thx for your code...i've ammended a bit bcoz that original code somehow didn't read all the lines in "START-OF-DATA" scope. I share my code below..again, many thx for all your responses...

Sub Convert()
Dim SplitData
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
MyRow = 1
If mydata = "START-OF-DATA" Then
Do
Line Input #1, mylongdata
MyRow = MyRow + 1

SplitData = Split(mylongdata, "|")
For col = 0 To 119
If SplitData(col) <> "END-OF-DATA" Then
Cells(MyRow, col + 1) = SplitData(col)
Else
mydata = "END-OF-DATA"
Exit For
End If
Next col

Loop While mydata <> "END-OF-DATA"
End If

Wend
Close 1
End Sub

 

[diddi]

thx. glad it worked out as you needed.

 

[tyme]

Hi Diddi,

I've a few text files with same format as mentioned in my earlier post but they have different field name and saved in different file name. So, if i want to convert all of these files into different excel file, how can i do that? My intention is to use only 1 excel macro to convert all the files using the code that i already have without having to create 1 excel macro to convert each text file (meaning i have to create many macro)..maybe in the code we just need to specify the source file path..the scenario is like this:

Text file: --> 1 excel macro to convert all files --> Output file:
1) ABC.txt 1) ABC.xls
2) DEF.txt 2) DEF.xls
3) GHI.txt 3) GHI.xls
4) JKL.txt 4) JKL.xls

 

[diddi]

is the content in the same format? if not i need to see sample content

 

[tyme]

Hi Diddi,

The content are in the same format..each file have the START-OF-FIELDS & START-OF-DATA..the different only on the field name and file name..TQ.

 

[diddi]

what is the difference in field name about?

 

[tyme]

hi diddi, i think i manage to get the solution..but need a little testing...

 

[diddi]

ok, thats great. post your code if you need another opinion.

 

[sagaddict]

hi! can somebody help me too.. I want to convert a text file to excel file using vba. Please see sample format below.

textfile fomat:
title1: visual
title2: basic
title3: access

title1: visual2
title2: basic2
title3: access2

title1: visual3
title2: basic3
title3: access3

the should be excel format:
title1 title2 title3
visual basic access
visual2 basic2 access2
visual3 basic3 access3

hope you can help me. thank you very much!

 

[diddi]

hi sagaddict, welcome to the board. the code used previously can be modified to suit your needs...
is that the whole data file or are there more lines? do you want the data written to a sheet?