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



Cells(xlrw, 5) = CDate(Trim(OrderDate))
Cells(xlrw, 6) = CDate(Trim(PlnDelvDate))

[alienscript]

Thanks a lot :)