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>

 

[sagaddict]

Hi diddy! thank you for replying. there are more lines in my data file and yes I want it to be written in a sheet. hope you could help me further. thank you very much again.

 

[Rick Rothstein]

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!

You should start your own thread instead of breaking into and taking over someone else's thread (I think there may be a forum rule about that).

 

[Rick Rothstein]

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

In case you are interested, here is another macro that I think should work correctly for your indicated text file layout (of course, you need to change the path and text filename in the Open statement)...

Sub ConvertTextFile()
  Dim X As Long, FileNum As Long, TotalFile As String, Txt As String, Arr() As String
  FileNum = FreeFile
  Open "c:\temp\TestMe.txt" For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  TotalFile = Replace(Replace(TotalFile, "END-OF-FIELDS", "START-OF-FIELDS"), "END-OF-DATA", "START-OF-DATA")
  Arr = Split(Replace(Split(TotalFile, "START-OF-FIELDS" & vbNewLine)(1), vbNewLine, vbTab), vbTab)
  Range("A1").Resize(, 120) = Arr
  Arr = Split(Split(TotalFile, "START-OF-DATA" & vbNewLine)(1), vbNewLine)
  For X = 0 To UBound(Arr)
    Range("A2").Offset(X).Resize(, 120) = Split(Arr(X), "|")
  Next
End Sub

 

[an9elia]

I'm not sure if this will help but I converted a text file similar to your using vbscript. My data was like this though
20120202|FI|100|FUN|1512|FN|5|LN|250|TVN|1002|UTC|0|NSF|0|OTHER LETTERS|1|2870
I hope this helps somehow.
Code:
Dim oExcel
Const ForReading = 1
Set WshShell = WScript.CreateObject("WScript.Shell")

Set ObjFSODialog = CreateObject("UserAccounts.CommonDialog")

ObjFSODialog.Filter = "Text Documents|*.txt"

ObjFSODialog.FilterIndex = 3

ObjFSODialog.InitialDir = WshShell.CurrentDirectory

InitFSO = ObjFSODialog.ShowOpen

If InitFSO = False Then
    Msgbox "Error: Please select a file!"
    Wscript.Quit
Else
    inputFile = ObjFSODialog.FileName
End If
Set objExcel = CreateObject("excel.application")
Set objDialog = CreateObject( "SAFRCFileDlg.FileSave" )
Set objDialogOpen = CreateObject( "SAFRCFileDlg.FileOpen" )
Set objFSO = CreateObject("Scripting.FileSystemObject")

'path = WshShell.CurrentDirectory
'inputFile = path & "\input.txt"
Set objReadFile = objFSO.OpenTextFile(inputFile, ForReading)'they choose input file

Dim ExcelSheet
Set ExcelSheet = CreateObject("Excel.Sheet")

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = False

' Write the A Row
'ExcelSheet.ActiveSheet.Cells(2,1).Value = "Date"

ExcelSheet.ActiveSheet.Cells(3,1).Value = "FI "
ExcelSheet.ActiveSheet.Cells(4,1).Value = "FUN "
ExcelSheet.ActiveSheet.Cells(5,1).Value = "FN"
ExcelSheet.ActiveSheet.Cells(6,1).Value = "LN"
ExcelSheet.ActiveSheet.Cells(7,1).Value = "TVN"
ExcelSheet.ActiveSheet.Cells(8,1).Value = "UTC"
ExcelSheet.ActiveSheet.Cells(9,1).Value = "NSF"
ExcelSheet.ActiveSheet.Cells(10,1).Value = "OLC"
ExcelSheet.ActiveSheet.Cells(11,1).Value = "Total"

Set objReadFile = objFSO.OpenTextFile(inputFile, ForReading)

col = 2
Do Until objReadFile.AtEndOfStream
    strLine = objReadFile.ReadLine
    arrFields = Split(strLine, "|")
    strDate = Mid(arrFields(0),5,2)+ "/" + Right(arrFields(0),2) + "/" + Left(arrFields(0),4)
    Select Case Weekday(strDate)
        Case 1
            strWeekday = "Sunday"
        Case 2
            strWeekday = "Monday"
        Case 3
            strWeekday = "Tuesday"
        Case 4
            strWeekday = "Wednesday"
        Case 5
            strWeekday = "Thursday"
        Case 6
            strWeekday = "Friday"
        Case 7
            strWeekday = "Saturday"
    End Select

    strFICode = arrFields (1)
    strFICount = arrFields (2)
    strFUNCode = arrFields (3)
    strFUNCount = arrFields (4)
    strFNCode = arrFields (5)
    strFNCount = arrFields (6)
    strLNCode = arrFields (7)
    strLNCount = arrFields (8)
    strTVNCode = arrFields (9)
    strTVNCount = arrFields (10)
    strUTCCode = arrFields (11)
    strUTCCount = arrFields (12)
    strNSFCode = arrFields (13)
    strNSFCount = arrFields (14)
    strOtherLettersCode = arrFields (15)
    strOtherLettersCount = arrFields (16)
    strTotal = arrFields (17)
    
    ExcelSheet.ActiveSheet.Cells(1,col).Value = strWeekday
    
    ExcelSheet.ActiveSheet.Cells(2,col).Value = strDate
    ExcelSheet.ActiveSheet.Cells(2,col).NumberFormat = "d-mmm"
    
    ExcelSheet.ActiveSheet.Cells(3,col).Value = strFICount
    intFITotal = intFITotal + CInt(strFICount)

    ExcelSheet.ActiveSheet.Cells(4,col).Value = strFUNCount
    intFUNTotal = intFUNTotal + CInt(strFUNCount)
    
    ExcelSheet.ActiveSheet.Cells(5,col).Value = strFNCount
    intFNTotal = intFNTotal + CInt(strFNCount)
    
    ExcelSheet.ActiveSheet.Cells(6,col).Value = strLNCount
    intLNTotal = intLNTotal + CInt(strLNCount)
    
    ExcelSheet.ActiveSheet.Cells(7,col).Value = strTVNCount
    intTVNTotal = intTVNTotal + CInt(strTVNCount)
    
    ExcelSheet.ActiveSheet.Cells(8,col).Value = strUTCCount
    intUTCTotal = intUTCTotal + CInt(strUTCCount)
    
    ExcelSheet.ActiveSheet.Cells(9,col).Value = strNSFCount
    intNSFTotal = intNSFTotal + CInt(strNSFCount)
    
    ExcelSheet.ActiveSheet.Cells(10,col).Value = strOtherLettersCount
    intOtherLettersTotal = intOtherLettersTotal + CInt(strOtherLettersCount)

    ExcelSheet.ActiveSheet.Cells(11,col).Value = strTotal
    intTotal = intTotal + CInt(strTotal)
    col = col + 1
Loop
ExcelSheet.ActiveSheet.Cells(1,col).Value = "TOTALS"
'ExcelSheet.ActiveSheet.Cells(3,col).Value = intFUNTotal
    
'ExcelSheet.ActiveSheet.Cells(4,col).Value = intFNTotal
'ExcelSheet.ActiveSheet.Cells(5,col).Value = intLNTotal
'ExcelSheet.ActiveSheet.Cells(6,col).Value = intTVNTotal
'ExcelSheet.ActiveSheet.Cells(7,col).Value = intUTCTotal
'ExcelSheet.ActiveSheet.Cells(8,col).Value = intNSFTotal
'ExcelSheet.ActiveSheet.Cells(9,col).Value = intOtherLettersTotal
ExcelSheet.ActiveSheet.Cells(11,col).Value = intTotal

ExcelSheet.ActiveSheet.PageSetup.Orientation = 2
ExcelSheet.ActiveSheet.PageSetup.CenterHeader = "&F"

fileArray = Split(inputFile,".")
outputFile = fileArray(0) + ".xlsx"

objDialog.FileName = outputFile
objDialog.FileType = "Excel Application (*.xlsx)"

If objDialog.OpenFileSaveDlg Then
    strExcelPath = objDialog.FileName
End If

' Save the sheet.
ExcelSheet.Application.DisplayAlerts = False
ExcelSheet.SaveAs strExcelPath
ExcelSheet.Application.DisplayAlerts = True
' Close Excel with the Quit method on the Application object.
ExcelSheet.Application.Quit
' Release the object variable.

'Open spreadsheet for validation
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(strExcelPath)

Set ExcelSheet = Nothing
Set objExcel = Nothing
Set objDialog = Nothing
Set objDialogOpen = Nothing
Set WshShell = Nothing
Set objFSO = Nothing
Set ObjFSODialog = Nothing
Set objReadFile = Nothing
Set objWorkbook = Nothing
<!--EndFragment-->

 

[naresh3110]

PORT RCD ACTDAT MT EQPNO PORLOC EQOWNTP LOPORT STLIVREF STSELSCR DIPORT EQRUMAN VESSEL VOYAGE EQRUTYP DAMAGE COUNT COUNT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
AEJALTM N 2014-09-11 N 270541 AEJALTM SO AEJALTM N N SDPZUTM REEF F49 1411 REEF 1 0
2014-11-05 Y MAEU5821177 OWN OMRF7TM N Y AEJALTM CARRIER ERH 0014 69NT40-511-38 0 1
TRLU1882390 LT OMRF7TM N N AEJALTM CARRIER ERH 0014 69NT40-511-54 2 0 1
2014-11-06 N MCAU8564280 EGPSDTM OWN EGPSDTM Y N AEJALTM CARRIER 044 1411 69NT40-489-100 0 1 How to convert this saple text file to excel using macro ...