Date changes to US format when copied using VBA

Question

Date changes to US format when copied using VBA

JP 6

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,

the date number and the month number are swapped around.
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

0 comments

2 answers

Your answer

Answer 1

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:

do the calculations with "date"-functions such as DateAdd in VBA when needed
convert the values using CDBL (if the time is relevant) or CLNG (if only the date is relevant)
move the converted values to Excel, (I store the array in a variant and write that to a Range)
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".

Answer 2

Nothing Left To Lose 396

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

JP 6 Reputation points

2021-01-22T18:58:58.317+00:00

Thanks NLTL,

How do I set the date column in csv files to "General"?
Those csv files are exported from an app via citrix, and i do not change anything in them. I usually don't even open them. :-(
Nothing Left To Lose 396 Reputation points

2021-01-23T02:01:49.99+00:00

re: number format question

I was referring to the worksheet column not the CSV files.
That suggestion was from an abundance of caution; people reuse worksheets.

Excel will automatically format data it thinks is a Date. That works best on a general number format.
'---

An aside: How do you get notified of a response to a question in this forum?
'---

NLtL
JP 6 Reputation points

2021-01-23T03:18:31.607+00:00

This is what happens when the date column is set to "General".
So it still reads the numbers up to 12 (incl.) as month, and then numbers greater than or equal to 13 as text, although when the csv file is opened manually (not via VBA), everything in the date columns is correctly displayed (i.e. DD/MM/YYYY).

As for notifications, try "follow" the question.

Share via

Date changes to US format when copied using VBA

2 answers

Your answer