Excel date from CSV

  The BB 12:13 17 Jul 2005

Have a set of CSV files with dates in column 6 in format dd/mm/yyyy. If the date is 31/03/2005 it reads correctly; but if its 01/04/2005 it reads as a string and becomes 4-Jan-2005

My regional settings have short date as 17/07/2005

How can I always read the date as a date? (code sample follows, Host.xls with sheet Pbit has the code and csv file is "P")

Filename = "P.csv"

Workbooks.Open Filename

For r = 1 To 10

With Worksheets("P")

Dt = .Cells(r, 6)

MsgBox Dt, , r

End With


Worksheets("Pbit").Cells(r, 2) = Dt


Next r


Workbooks(Filename).Close SaveChanges:=False

Off to the pub to think!

  VoG II 14:41 17 Jul 2005

Is Dt dimensioned as a Date?

  The BB 09:27 18 Jul 2005

Tried setting Dt as a Variant, Integer, String etc. but no go; if look at it in debug, and don't DIM it, then the type changes depending on 01/04/2005 or 31/03/2005 - think Excel is being too clever for it's own good!
Current workaround is openning the file as a text file (OpenTextFile) using the readline property and manually parsing.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys

iPad Pro 10.5in (2017) review

Comment faire une capture d’écran sur un Mac ?