0

I use amacro to convert txt files into xls but I have the problem that the fields with dates are not converting in the right format. For example I have the date 4/11/2014 (dd/mm/yyyy) and when the file is converted into xls, I have the date shown like this :11/4/2014.

How can I fix it?

code:

Public Sub Read_TXT()
Dim Dir_p As String, File_to_Open As String
Dir_p = ActiveWorkbook.path
File_to_Open = Dir(Dir_p & "\")
While File_to_Open <> ""
  If InStr(1, Right(File_to_Open, 3), "txt", vbTextCompare) <> 0 Then
    Workbooks.OpenText Filename:=Dir_p & "\" & File_to_Open, _
        Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, _
        TrailingMinusNumbers:=True
    ActiveWorkbook.SaveAs Filename:=Dir_p & "\" & Left(File_to_Open, Len(File_to_Open) - 3) & "xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWorkbook.Close
  End If
  File_to_Open = Dir
Wend
End Sub
user3333321
  • 23
  • 6

1 Answers1

1

There's two methods that you can use here, the short way - which doesn't always work - or the long way, which will work, but will take a while to run.

The short way:

Columns("E, H, S, V, AB, AF, AJ, AL, AO, AS, AY, BE, BH").EntireColumn.NumberFormat = "dd/mm/yyyy"

The long way:

Dim bigRange As Range, rCell As Range

'// If your data has headers, change the 1 to a 2 in all the lines below.

Set bigRange = Union(Range("E1", Range("E" & Rows.Count).End(xlUp)), _
    Range("H1", Range("H" & Rows.Count).End(xlUp)), _
    Range("S1", Range("S" & Rows.Count).End(xlUp)), _
    Range("V1", Range("V" & Rows.Count).End(xlUp)), _
    Range("AB1", Range("AB" & Rows.Count).End(xlUp)), _
    Range("AF1", Range("AF" & Rows.Count).End(xlUp)), _
    Range("AJ1", Range("AJ" & Rows.Count).End(xlUp)), _
    Range("AL1", Range("AL" & Rows.Count).End(xlUp)), _
    Range("AO1", Range("AO" & Rows.Count).End(xlUp)), _
    Range("AS1", Range("AS" & Rows.Count).End(xlUp)), _
    Range("AY1", Range("AY" & Rows.Count).End(xlUp)), _
    Range("BE1", Range("BE" & Rows.Count).End(xlUp)), _
    Range("BH1", Range("BH" & Rows.Count).End(xlUp)))

For Each rCell In bigRange.Cells
    With rCell
         .Value = Format(CDate(.Value2), "dd/mm/yyyy")
    End With
Next rCell
SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • i will add this in my code (Read_TXT) OR use it in new Sub ? – user3333321 Nov 26 '14 at 13:05
  • You could technically do either, but if you're not that familiar with VBA I would advise running this as a separate procedure after the `Read_TXT()` sub – SierraOscar Nov 26 '14 at 13:07
  • in any way it seems that it doesn't change the format but it understand it in the wrong way. ex. date 4/11/2014, after convertion will shown as 11/4/2014 BUT date 28/11/2014 will be shown right as 28/11/2014. – user3333321 Nov 26 '14 at 13:08
  • Yes, this is because Excel is trying to read it as an American date format. If the day is 01 - 12 it will assume this is a month instead, and use the mm/dd/yyyy format. If the day is greater than 12, then it clearly isn't a month and Excel will assume the dd/mm/yyyy format. – SierraOscar Nov 26 '14 at 13:10
  • Excel saves dates as a Double data type, and so the code above reads this coded value, and then forces it do be displayed in the preferred date format of "dd/mm/yyyy" – SierraOscar Nov 26 '14 at 13:11
  • it stops at this point --> .Value = Format(CDate(.Value2), "dd/mm/yyyy") – user3333321 Nov 26 '14 at 13:24
  • I added a comment in the post about headers, if your data has headers you will need to change `1` to `2` on each line of the `Union()` function. – SierraOscar Nov 26 '14 at 13:29
  • If I want to use the first-easy method, where should I post it in my code? – user3333321 Nov 26 '14 at 13:33
  • Before the line `ActiveWorkbook.SaveAs ...` Be aware though that the easy method probably won't work due to the way the text is being read by Excel. – SierraOscar Nov 26 '14 at 13:35