Date changes to US format when copied using VBA

Question

Hi,
I'm combining multiple csv files into one excel sheet using VBA.
Dates in all my csv files are set to be DD/MM/YYYY in format
But when they are copied to the excel sheet,

1. the date number and the month number are swapped around.
2. and the dates that look the same as those in the csv files convert to text (no longer a date).

What am I doing wrong?? How do I make the dates stay the same as in the csv files?
Help please~~~

Example:

Picture 1.
Dates in csv file: 12/01/2021 = 12th January 2021

Picture 2.
Dates in excel after being copied: 1/12/2021 = 1st December 2021
And <13/01/2021>s are no longer a date.

Below is my code that combines csv files.

Sub Combine ()

Dim FolderPath As String
Dim FileName As String
Dim WB As Workbook

FolderPath = "P:\JAY\Downloads"
If FolderPath Like "*[!/]" Then
FolderPath = FolderPath & "/"
End If

FileName = Dir(FolderPath & "*.csv")

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Do While FileName <> vbNullString
Set WB = Workbooks.Open(FolderPath & FileName)
With WB.ActiveSheet.UsedRange
.Resize(.Rows.Count - 1, .Columns.Count).Offset(1, 0).Copy ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows(ThisWorkbook.Worksheets("Sheet1").UsedRange.Rows.Count).Offset(1) .Resize(1)
End With
WB.Close False
FileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Thank you!
Jay

Answer 1

Re: importing files with dates

The Windows regional settings determines the default date format used in Excel.
You might also set the number format for the Date Column to "General".

'---
NLtL
https://1drv.ms/u/s!Au8Lyt79SOuhZ_zj8wEtOjDcqAI?e=MrzfpA
Calculate Payments, Custom_Functions, Greatest Films, Professional_Compare

Answer 2

I had a similar issue:

• a chart-axis that would not accept VBA generated date-values, eg "21-aug-2020" caused the axis to default to text-mode
• a calculated array of date-values that looked good as long as the day-of-the-month was no bigger than 12

The problem occurs when VBA hands over "date"-values to Excel. I think it is a bug...

My solution is to avoid the "date" data-type for this hand-over:

1. do the calculations with "date"-functions such as DateAdd in VBA when needed
2. convert the values using CDBL (if the time is relevant) or CLNG (if only the date is relevant)
3. move the converted values to Excel, (I store the array in a variant and write that to a Range)
4. format the cells as "date" in Excel. This can be done in advance, or afterward!

This worked for both my problems.
With the Chart I used the Long-values to populate the category-axis and then set the axis-type to "date".