Results 1 to 2 of 2

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

  1. #1
    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

  2. #2
    Join Date
    Aug 2011
    Posts
    565

    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

Similar Threads

  1. VB Script or batch file to run Excel Macro 2010
    By Fakhry in forum Software Development
    Replies: 2
    Last Post: 19-06-2012, 12:23 PM
  2. How to convert number to text in excel
    By Jevin in forum Software Development
    Replies: 9
    Last Post: 17-05-2012, 06:42 PM
  3. Replies: 2
    Last Post: 06-01-2012, 03:15 PM
  4. How to convert image text into a text file
    By Wadee in forum Windows Software
    Replies: 5
    Last Post: 02-12-2010, 05:33 PM
  5. Macro to convert file into PDF and then send via email
    By MAGALY in forum Software Development
    Replies: 5
    Last Post: 09-12-2009, 04:00 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •