Excel date from CSV

  The BB 12:13 17 Jul 05
Locked

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

Windows("HostFile").Activate

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

Windows(Filename).Activate

Next r

Windows(Filename).Activate

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

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

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…