VBA Copying Brazilian Standard Date in American Standard

Question

VBA Copying Brazilian Standard Date in American Standard

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I have the code below in a macro, to copy a separate TXT file with semicolons 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 a date like this:

12/11/18 21:21:42

But this same line in Excel looks like this:

11/12/2018 21:21

In addition, in Excel the dates are in different format, as below:

I deleted the column in Excel and created another, but it did not solve. Any suggestions?

vba excel-vba

asked by anonymous 23.11.2018 / 11:36

2 answers

0

Try to edit your computer's date patterns 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"

23.11.2018 / 12:13

Do not re-enter a user with an existing login Problem inserting firebird data into ubuntu

score 0 · Accepted Answer

If somebody goes through this problem (TexttoColumns changing the format of the date), the solution follows. I changed the way I copied the CSV and it worked. I changed the function call, but that had not 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