Date changes to US format when copied using VBA

JP 6 Reputation points
2021-01-22T09:34:53.037+00:00

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
59534-image.png

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

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 No comments
{count} vote

2 answers

Sort by: Most helpful
  1. Nothing Left To Lose 396 Reputation points
    2021-01-22T16:08:25.047+00:00

    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


  2. Paul Frenay 1 Reputation point
    2021-02-09T19:43:06.773+00:00

    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".

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.