Go Back   TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



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

MS Office Support


Reply
 
Thread Tools Search this Thread
  #1  
Old 24-02-2012
Member
 
Join Date: Feb 2012
Posts: 9
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

Reply With Quote
  #2  
Old 24-02-2012
Member
 
Join Date: Aug 2011
Posts: 561
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
Reply With Quote
Reply

  TechArena Community > Technical Support > Computer Help > Office Help > MS Office Support


Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Need help to convert a Text file to Excel via VBA Macro"
Thread Thread Starter Forum Replies Last Post
VB Script or batch file to run Excel Macro 2010 Fakhry Software Development 2 19-06-2012 01:23 PM
How to convert number to text in excel Jevin Software Development 9 17-05-2012 07:42 PM
How to add commas after each text in each cell using Excel Macro Raju Chacha Tips & Tweaks 2 06-01-2012 04:15 PM
How to convert image text into a text file Wadee Windows Software 5 02-12-2010 06:33 PM
Macro to convert file into PDF and then send via email MAGALY Software Development 5 09-12-2009 05:00 PM


All times are GMT +5.5. The time now is 12:13 AM.