Excel date from CSV

  The BB 12:13 17 Jul 05

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 05

Is Dt dimensioned as a Date?

  The BB 09:27 18 Jul 05

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.

Samsung Galaxy S8 review

1995-2015: How technology has changed the world in 20 years

What’s happening in VR right now: From psychedelic art to combat therapy

Siri not working? Try these troubleshooting fixes