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 5T review: Hands-on

Illustrator Andrés Lozano on his improv line work, brazen use of colours & hand sketching

iPhone X review

Comment envoyer gratuitement des gros fichiers ?