For Input Line As

[alienscript]

Hi Bob and the VBA community!

I was able to print line by line from the delimited text file into Excel sheet. My problem is the last 2 columns of data format though look alright in the "dd-mm-yyyy" format, but actually it isn't when I tested them with MONTH and DAY formula.

I had earlier pre-formatted the last 2 columns in "dd-mm-yyyy" data format. What had I done wrong? Could anyone help me on this please.

I am using XL 2000. Thanks very much.

 
Option Explicit
Dim Item As String, OrdQty, DelvQty, BalQty, OrderDate, PlnDelvDate
Dim xlrw As Long, data As String, i As Integer, strLine, fileToOpen
' am using Excel 2000
Sub OutstandingSO()
    xlrw = 2
    fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    Open fileToOpen For Input As #1
    ActiveWorkbook.Worksheets("Sheet1").Activate
    Range("A2:F10").ClearContents
    Columns("A:A").NumberFormat = "@"
    Columns("B:D").NumberFormat = "0.00"
    Columns("E:F").NumberFormat = "DD-MM-YYYY"
    
    Do While Not EOF(1)
    Line Input #1, strLine
    
        If Left(strLine, 1) <> "" And Left(strLine, 2) <> "  " _
          And Left(strLine, 4) <> "Date" And Left(strLine, 4) <> "Item" _
          And Left(strLine, 2) <> "--" Then
            Item = Trim(Left(strLine, 13))
            OrdQty = Trim(Mid(strLine, 17, 8))
            DelvQty = Trim(Mid(strLine, 26, 10))
            BalQty = Trim(Mid(strLine, 37, 10))
            OrderDate = Trim(Mid(strLine, 48, 10))
            PlnDelvDate = Trim(Mid(strLine, 59, 10))
            
            Cells(xlrw, 1) = Trim(Item)
            Cells(xlrw, 2) = Val(OrdQty)
            Cells(xlrw, 3) = Val(DelvQty)
            Cells(xlrw, 4) = Val(BalQty)
            Cells(xlrw, 5) = Trim(OrderDate)
            Cells(xlrw, 6) = Trim(PlnDelvDate)
            xlrw = xlrw + 1
        End If
    Loop
    Close #1
End Sub

[Bob Phillips]

See if this helps

[vba]

Cells(xlrw, 5) = CDate(Trim(OrderDate))
Cells(xlrw, 6) = CDate(Trim(PlnDelvDate))
[/vba]

[alienscript]

Thanks a lot