Incorrect date format when importing .CSV

[PaulBarter]

Using Excel 2000

I am writing a macro that will open a selected .csv file and extract the data I want into the current spreadsheet.

The problem is that the dates in the .csv file get formated incorrectly.

All the dates in the file are in UK format "dd/mm/yyyy" (including the quotes). If the file contains "25/11/2005" then it converts to 25th November correctly, however if it contains "04/11/2005" then it converts to 11th April 2005 (i.e. it assumed the first number is month if <= 12. How do I get it to treat the first 2 digits as day regardless.

It seems that if I manually open the .csv file then it reads it in correctly, however if a macro use a .Open method (or even .OpenText method) then it fails as above.

It seems the the .csv suffix is causing the problem as if I rename the file with .txt suffix and use the .OpenText method then all is OK.

For example run the following code

Sub ReadTextIn()
Dim strFole As String
strFile = "text.csv"
Workbooks.OpenText Filename:=strFile, _
    Origin:=xlWindows, _
    StartRow:=1, _
    DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=False, _
    Tab:=False, _
    Semicolon:=False, _
    Comma:=True, _
    Space:=False, _
    Other:=False, _
    FieldInfo:=Array(Array(1, 4), Array(2, 2))
End Sub

with an input file text.csv consisting of

Date, Name
"29/03/2006","Record 1"
"06/04/2006","Record 2"
"12/04/2006","Record 3"
"15/04/2006","Record 4"

and records 2 & 3 will be converted incorrectly. Rename the file to "text.txt" and change the second line of the macro accourdingly then all is OK.

Any ideas?

 

[PaulBarter]

Sorry, first line of macro should read

Dim strFile as String

Too many fingers!

 

[rmikesmith]

Try the following:

strFile = "text.csv"
Workbooks.Open Filename:=strFile, [b]Local:=True[/b]

From the Help file on Workbooks.Open method parameters:
Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).


Regards,
Mike

 

[HughLerwill]

Paul,

You may also consider/ try using a templated destination Book/ Sheet containing the required (column/ cell) formats and pulling the data from the csv into it using code like that in my contribution to thread707-955487.

regards Hugh,

 

[PaulBarter]

Mike

The Local argument does not seem to exist for the Workbooks.Open method in Excel 2000.

Thanks anyway.

The .Querytables.Add solution looks promising though, and it will probably be easier than copying the .csv file to a temporary .txt file and working from that.

Thanks

Paul