problem in reading new text file in VB script macro

The VBA Project is password protected.

Can you not go back to the original supplier of the code ... as identified on the closing form?

Is this a typical file? One record? One Container? One Invoice? Several line items?

The options would seem to be:
1. pre-process the new format file and produce an old format file
2. process the single record in the code and split it into sub records equivalent to the original

Each line in the original ends with a single quote "'" and those single quotes exist in the new format. It should be possible, therefore, to split the records.

I think, of the two options, I'd go for the first as it involves least (in fact, no) hacking of the original code.

I'll see if I can produce something tomorrow unless someone else provides a solution in the meantime.

If there are more complex examples, it might be worth sharing one now rather than later.

Regards

For the sake of simplicity, I have produced a free-standing conversion in Excel.

It works in much the same way as your current Excel application.

However, rather than taking the text file and converting it to Excel, it takes a "new format" text file and converts it to an "old format" text file. This can then be read by the original converter.

Sub EdiConversion()

Dim TxtFile As Variant
Dim TxtFileOut As Variant
Dim EdiRows As String
Dim Edirow As Variant
Dim i As Long

' Ask for the file name that has to be processed
TxtFile = Application.GetOpenFilename("Text Files (*.txt), *.txt")
If TxtFile = False Then
    MsgBox "No files selected, nothing to do. Bye."
    Exit Sub
End If

' Open the file and read rows processing them one by one
Open TxtFile For Input Access Read As #1
TxtFileOut = Left(TxtFile, Len(TxtFile) - 4) & "_OF.txt"
Open TxtFileOut For Output Access Write As #2

While Not EOF(1)
    Line Input #1, EdiRows
    Edirow = Split(EdiRows, "'")
    For i = LBound(Edirow) To UBound(Edirow)
        If Edirow(i) <> "" Then
            Print #2, Edirow(i) & "'"
       End If
    Next i
Wend

Close #1
Close #2

MsgBox "The file has been converted" & vbLf & vbLf & _
       "Input:      " & TxtFile & vbLf & _
       "Output:  " & TxtFileOut & vbLf, , _
       "Edi File Conversion"

End Sub

It probably wouldn't be too difficult to combine this with the original but I think it's better, at this stage, to "prove the theory" before hacking the original about.

I trust it will help.

Regards

Sorry I didn't see your message and replied by then. Thank you for the below help. It really works...

Can we add this script into original macro????

Yes there will several line items...please see attached example. There can be 100s invoice, container number and amount...

No there are no complex examples...only there can be several line item with the same ID such as RFF+IV, RFF+BM and so on...

Best Regards,
Kam.

I have attached an updated workbook. This will process the "new format" text file. It will, if you change a cell (G2) on the Config tab, still process "old format" text files.

It outputs to sheet1 as before.

Enjoy ;-)

Regards

:
:
Cells(1, 4) = "Currency"
Cells(1, 5) = "Line"

' #########################################################################
' New code to process "New Format" Text File                              #
' Author: ................                                                #
' http://www.excelforum.com/excel-programming/...                         #
'     ...761719-problem-in-reading-new-text-file-in-vb-script-macro.html  #
'                                                                       ' #
' Open the file and read rows processing them one by one                ' #
Dim TxtFileOut As Variant     ' Old Format Text File                    ' #
Dim vEdiRow As Variant        ' Array to store "individual" rows        ' #
Dim i As Long                 ' Loop counter                            ' #
'                                                                       ' #
' Check the Config tab to determine the format of the input file        ' #
' Range("TheFormat") = Sheets("Config").Range("G2")                     ' #
If LCase(Range("TheFormat").Value) <> "old" Then                        ' #
'                                                                       ' #
    Open TxtFile For Input Access Read As #1                            ' #
    TxtFileOut = Left(TxtFile, Len(TxtFile) - 4) & "_OF.txt"            ' #
    Open TxtFileOut For Output Access Write As #2                       ' #
                                                                        ' #
    While Not EOF(1)                                                    ' #
        Line Input #1, EdiRow                                           ' #
        vEdiRow = Split(EdiRow, "'")                                    ' #
        For i = LBound(vEdiRow) To UBound(vEdiRow)                      ' #
            If vEdiRow(i) <> "" Then                                    ' #
                Print #2, vEdiRow(i) & "'"                              ' #
           End If                                                       ' #
        Next 'i                                                         ' #
    Wend                                                                ' #
                                                                        ' #
    Close #1     ' Close the "new format" text file                     ' #
    Close #2     ' Close the "old format" text file                     ' #
                                                                        ' #
    TxtFile = TxtFileOut    ' set filename to be the "old format" file  ' #
                                                                        ' #
End If                                                                  ' #
'                                                                         #
' End of new code to process "New Format" Text File                       #
' #########################################################################

:
:
' Open the file and read rows processing them one by one according to the Identifier
Open TxtFile For Input Access Read As #1
OutXlsRow = 1
MoaSeq = 0

You're welcome. Thanks for the feedback.