VBA text to column on a column with date

Question

I am trying to perform a text to column with VBA. However, when I do it, it turns the date format from DMY to MDY. I read it's a problem with VBA and I have tried every suggestion online.

Below is my code script. Assistance will be greatly appreciated.

Below is the code block which I have tried.

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 4), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

' Regular date format in column C
Dim lastRow As Long
lastRow = Range("C" & Rows.Count).End(xlUp).Row
If lastRow >= 2 Then
    Dim v, j As Long
    v = Range("C2:C" & lastRow).Value
    For j = 1 To UBound(v)
        If IsDate(v(j, 1)) Then v(j, 1) = CDate(v(j, 1))
    Next j
    Range("C2").Resize(UBound(v)).Value = v
End If

Answer 1

Here’s a modified version of your code that should help solve your problem. This code will change the system’s date format to “DMY”, perform the TextToColumns operation, and then revert the system’s date format back to its original setting.

    ' Store the current system date setting
Dim originalDateSetting As String
originalDateSetting = Application.International(xlDateOrder)

' Change system date setting to DMY
Application.International(xlDateOrder) = 1

' Perform TextToColumns operation
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 4), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1)), TrailingMinusNumbers:=True

' Regular date format in column C
Dim lastRow As Long
lastRow = Range("C" & Rows.Count).End(xlUp).Row
If lastRow >= 2 Then
    Dim v, j As Long
    v = Range("C2:C" & lastRow).Value
    For j = 1 To UBound(v)
        If IsDate(v(j, 1)) Then v(j, 1) = CDate(v(j, 1))
    Next j
    Range("C2").Resize(UBound(v)).Value = v
End If

' Revert system date setting back to original
Application.International(xlDateOrder) = originalDateSetting

Answer 2

I eventually resorted to macro to do a power query to import the file and when I saved it the dates in the date column were intact in the desired format I wanted DD.MM.YYYY.

Thanks for the input @all.