VBA copying Brazilian standard date in American standard

Question

I have the code below in a macro, to copy a TXT file separated by semicolon to an Excel file:

Sub Atualizar_Dados()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    'Abre o arquivo do servidor (mudado para txt ao inves de csv)
    Workbooks.Open Filename:="http://pcn-sig.peccin.local/sig/ebsout/txt/pcnopmrelrefugos_mail.txt"
    Columns("A:U").Select
    Selection.Copy

    Windows("BASE ORACLE - Teste Hora.xlsm").Activate
    Sheets("BASE").Select
    Range("A1").Select

    'colar especial
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.RefreshAll

    'Seleciona a coluna com os dados
    Columns("A").Select
    'Ativa a função texto para coluna
    Selection.TextToColumns _
      Destination:=Range("A1"), _
      DataType:=xlDelimited, _
      TextQualifier:=xlDoubleQuote, _
      ConsecutiveDelimiter:=False, _
      Tab:=True, _
      Semicolon:=False, _
      Comma:=False, _
      Space:=False, _
      Other:=True, _
      OtherChar:=";"

    'Ativa o arquivo aberto do servidor
    Windows("pcnopmrelrefugos_mail.txt").Activate
    'Fecha o arquivo
    ActiveWorkbook.Close

    Sheets("CAPA").Select
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save

End Sub

The TXT file, for example, has the date like this:

;12/11/18 21:21:42

But that same line, in Excel, is like this:

11/12/2018 21:21

Even, in Excel the dates are with different formatting, as below:

I already deleted the column in Excel and created another, but did not solve. Some suggestion?

Answer 1

If by chance someone goes through this problem (Texttocolumns changing the format of the date), follows below the solution. I changed the way to copy the CSV and it worked. I changed the function call, but that hadn’t changed anything until I copied the CSV differently:

Sub Atualizar_Dados()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

    'Abre o arquivo do servidor (mudado para txt ao inves de csv)
    Workbooks.OpenText Filename:= _
    "http://pcn-sig.peccin.local/sig/ebsout/txt/pcnopmrelrefugos_mail.txt", DataType:=xlDelimited, Semicolon:=True, Local:=True
    Columns("A:U").Select
    Selection.Copy

    Windows("BASE ORACLE - Teste Hora.xlsm").Activate
    Range("A1").Select

    'colar especial
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.RefreshAll

    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :=";", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True

    'Ativa o arquivo aberto do servidor
    Windows("pcnopmrelrefugos_mail.txt").Activate
    'Fecha o arquivo
    ActiveWorkbook.Close

End Sub

Answer 2

Try to edit the date patterns of your computer in the control panel or in the code add the formatting of the data in the column with:

Sheets("CAPA").Select
Range("B2", "B50000").NumberFormat = "dd-mm-yyyy"