VBA solution to convert mixed UK/US date formats

Hi,

bad import as you forgot to define the column format !

Just activate the Macro Recorder, open the csv text file like a workbook then well answer to the Import Assistant
in particular for the column format (it's the same process if you still use the text to columns convert feature) …

Thanks for the reply

I have tried changing the way the data is pasted in from the source file but it is still changing dates such as 08/01/2020 to 01/08/2020.

Unfortunately the problem I have is that the Macro Recorder is not recording the column format. I need to know how I can add that manually.

Thanks

Upupload a sample csv with false data (to protect data confidentiality) but reliable/suitable/proper in terms of data layout.
Your computer's system settings are affecting the result of the data import and we have to somehow bypass it, but for this we need a data sample.

Thanks, below is more of the macro I use to import the data:

ChDir "C:\Export"
    Workbooks.Open Filename:="C:\Export\DEBTP.csv"
    Range("A1:L150000").Select
    Selection.Copy
    Windows(ThisWorkbook.Name).Activate
    Sheets("DEBTP").Visible = True
    Sheets("DEBTP").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Application.CommandBars("Office Clipboard").Visible = False
    Windows("DEBTP.csv").Activate
    ActiveWindow.Close
    Windows(ThisWorkbook.Name).Activate
    Sheets("DEBTP").Select
    Columns("C:C").Select
    Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 3), TrailingMinusNumbers:=True
    Range("A1").Select

Thanks Pepe, noted.

Thanks, I've tried that but, although it works manually, it doesn't seem to work in a macro.

As it works a treat on my side whatever operating manually or just using the procedure from the Macro Recorder
but I just notice your code does not well define the appropriate column format, so just activate the Macro Recorder and retry …

The Macro Recorder can not ignore anything so you just choosed a wrong format …

I don't know what the issue was - it worked fine when I recorded it but differently when run as a macro. There was nothing in VB to show what formatting I had set.

Thanks for your help anyway.

Wrong as the formatting belongs to the FieldInfo parameter of the TextToColumns method
as you can yourself check within the VBA inner help !

And before to validate the Assistant, just check the column, it's just visual so easy to compare with the original text file …