Date format messes up when importing multiple CSV files using VBA module

[ja12345]

Hi,

I have data from multiple CSV files that I want to compile into one sheet. I found a macro online for this:

Sub ImportCSVsWithReference()'UpdatebyKutoolsforExcel20151214
    Dim xSht  As Worksheet
    Dim xWb As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    On Error GoTo ErrHandler
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    Set xSht = ThisWorkbook.ActiveSheet
    If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
    Application.ScreenUpdating = False
    xFile = Dir(xStrPath & "\" & "*.csv")
    Do While xFile <> ""
        Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
        Columns(1).Insert xlShiftToRight
        Columns(1).SpecialCells(xlBlanks).Value = ActiveSheet.Name
        ActiveSheet.UsedRange.Copy xSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
        xWb.Close False
        xFile = Dir
    Loop
    Application.ScreenUpdating = True
    Exit Sub
ErrHandler:
    MsgBox "no files csv", , "Kutools for Excel"
End Sub

After using the macro, the problem is that some date formats becoming messed up. For example, the original files have some data like this:

12/10/2017 23:30 (custom format: dd/mm/yyyy hh:mmm)
13/10/2017 00:00 (custom format: dd/mm/yyyy hh:mmm)

After using the module, the result is:

10/12/2017 23:30 (custom format: dd/mm/yyyy hh:mmm)
13-10-2017 00:00 (general format)

How can I maintain the same date format?

Thanks,
J

 

[ParamRay]

In the Workbooks.Open method, add in the option Local:=True.

i.e.

Set xWb = Workbooks.Open(xStrPath & "\" & xFile, Local:=True)