VBA is changing my dates to mm/dd/yyyy hh:mm when importing

1
votes

So I am importing dates from a computer generated CSV file. In the CSV file, the dates are in the dd/mm/yyyy hh:mm format. However when I import it using VBA, VBA reads it as mm/dd/yyyy hh:mm. so VBA reads 01/05/2015 (1st of may) as 5th of jan.

I checked, and the CSV file is definietly in the dd/mm/yyyy hh:mm format.

any help in fixing this would be greatly appreciated.

So when I open the CSV file manually, the date is in the dd/mm/yyyy hh:mm format. Like 1st of december 2016 would be 01/12/2016 1:00. But when VBA opens it, it changes to 12/01/2016 1:00.

This is the entire code in question. Its nothing complex, and I cant figure out whats wrong with it.

Sub import()
    Dim calbook As Workbook
    Dim newwb As Workbook
    Dim destiwb As Workbook
    Dim directory As String
    Dim Filename As String
    directory = "C:\Users\winterco\Desktop\"
    Set calbook = Workbooks("Data_totaliser1.xlsm")
    Filename = Dir(directory & "*.CSV")
    Set newwb = Workbooks.Add
    Set destiwb = ActiveWorkbook
    Do While Filename <> ""
        Workbooks.OpenText (directory & Filename)
        Call Sort_Data(Filename, destiwb, directory, calbook)
        Filename = Dir()  
    Loop  
End Sub
2
vbaexceldatedatetime
Under Format Cells --> Number --> Date change the Locale to English (United Kingdom) – tlemaster
Hello! Thank you for responding. The CSV files are computer generated. I checked, and the date format is already set to English(New Zealand). – Shehan

2 Answers

0
votes

If you don't specify all of the format details, Excel uses the most recently used Text to Columns settings when opening text files.

If you're using the Workbooks.OpenText method (instead of Workbooks.Open method), you can specify all of those formats. Try recording a macro while opening a text file, to see how the formats are specified.

One of the formats you can provide is the order of Day, Month and Year. If you can't find a format that works, you can define that column as Text, and then parse the data using formulas in a subsequent process.

0
votes

It's a real nightmare... I never found a "simple" solution.

My workaround is that I always open CSV with VBA as strings to do the conversions myself, this way I don't have bad surprises. Or if you want to make it less work, do as Thunderframe suggested, open as text, this way no conversion, and do a loop with the code here under:

Dim z: z = VBA.Split(VBA.Replace(x, "-", "/"), "/") 'works with 01/01/2001 and 01-01-2001
answer = DateSerial(Left(z(2), 4), z(1), Left(z(0), 2)) 'left for the case DD/MM/YY 00:00:00, because unlike you I don't give a f* for time of day. If you want the time of the day, you'll need to use :

z=split(x,":")
answer =answer + TimeSerial(right(z(0),2)  , z(1), z(2) ) 'untested code