Convert date toregional setting on Laptop/PC

[howard]

When I import CSV files into Excel the date format changes from dd/mm/yyyy to mm/dd/yyyy


I have tried to amend my macro for importing files toimport the dates format the same as my regional settings on my Laptop/PC


It seems to do these for the majority of the dates but some dates , but not for all


It would be appreciated if someone could kindly assist me

 Sub Open_Workbook()
ChDir ("C:\Run Number Reports")
A:
Dim A     As Variant
Dim LR As Long
With Sheets("Data Import")
.UsedRange.ClearContents
End With
ChDir ("C:\Run Number Reports")
     A = Application.GetOpenFilename(MultiSelect:=True)
    If TypeName(A) = "Boolean" Then Exit Sub
    
    Dim file As Variant
    
    Application.ScreenUpdating = False
   For Each file In A
        With Workbooks.Open(file, Local:=True)
        

     
        With Sheets(1)
            .Range("a1", .Range("J" & Rows.Count).End(xlUp)).Copy _
                Destination:=ThisWorkbook.Sheets("Data Import").Range("A" & Rows.Count).End(xlUp).Offset(1)
        
         
        End With
  
        .Close SaveChanges:=False
    End With
 Next
 Application.ScreenUpdating = True
End Sub

 

[jpvh8]

Rather than using the Workbooks.Open method, try the Worksheet.QueryTables.Add method. That will give you full control over the formats used for the columns. You can use the Macro Recorder to get some sample code by opening one of the files with the From Text wizard on the Data ribbon.

 

[howard]

Thanks for your input