Originally Posted by
Potoroo
Hello all,
I have spent a lot of time reading posts about the way VBA uses the US date format in Excel but, apparently, I keep getting this wrong.
I extract data from a SAP application. The filename when extracted currently defaults to using a .xls extension but is, in fact, a CSV file. I have renamed the file to both .txt and .dat extensions based on suggestions for forcing Excel to allow the reformatting of the dates in dd/mm/yyyy format but I am still seeing mm/dd/yyyy in the end result. Note: in CSV format, the date delimiters are dots i.e. 1 September 2006 will show as 01.09.2006.
I recorded code when performing a manual opening of the file and then amended the Array "y" format for the date columns, added in the Local:=True bit and am certain the local format in my Excel is dd/mm/yyyy.
I turned on screen updating to watch what happens when I run the code. Part of the formatting involves replacing the dot delimiters with / delimiters and then applying the date format dd/mm/yyyy. It seems that this is where the dates swap around to show as mm/dd/yyyy.
Please help.
First bit of code:
Workbooks.OpenText Filename:="C:\MyFile.dat", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 4), Array(2, 1), _
Array(3, 4), Array(4, 4), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
Array(10, 4)), TrailingMinusNumbers:=True, Local:=True
Additional formatting bit of code:
Columns("C:D").Select
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.NumberFormat = "dd/mm/yyyy;@"
Many thanks.
Bookmarks