csv to xls date US/UK format

[Lawler]

The following code is designed to copy the whole contents of a small csv file into an excel sheet.
Workbooks.Open Filename:="csvfile.csv
Cells.Selec
Selection.Cop
Windows("xlsfile").Activat
Sheets("sheetone").Selec
Range("A1").Selec
ActiveSheet.Past
Windows("gre.csv").Activat
Application.CutCopyMode = Fals
ActiveWindow.Clos
It works fine except when there is a date that is OK in both UK and US formats e.g. 07/11/03 can be either 11th July (US) or 7th November (UK). VB routine defaults to the US format but it works fine doing the procedure manually. Does anybody know the code to default to UK format?

 

[Tom Ogilvy]

In Excel 2000 and earlier, there is not setting that will correct this. The
usual way is rename the file with a .txt extention. Then turn on the macro
recorder and open the file using File=>Open. Go through the text import
wizard choosing delimited and comma. In the last dialog, select the date
column an specify the order. Complete the import. Now turn off the
recorder and use the recorded code to bring in the file. You will need to
rename it in your code with a .txt extension as well before you use the
OpenText method or your settings will be ignored.

--
Regards,
Tom Ogilvy

The following code is designed to copy the whole contents of a small csv file into an excel sheet.
Workbooks.Open Filename:="csvfile.csv"
Cells.Select
Selection.Copy
Windows("xlsfile").Activate
Sheets("sheetone").Select
Range("A1").Select
ActiveSheet.Paste
Windows("gre.csv").Activate
Application.CutCopyMode = False
ActiveWindow.Close
It works fine except when there is a date that is OK in both UK and US

formats e.g. 07/11/03 can be either 11th July (US) or 7th November (UK). VB
routine defaults to the US format but it works fine doing the procedure
manually. Does anybody know the code to default to UK format?

 

[Lawler]

Good idea, thanks Tom

 

[Lawler]

Tom
Just to let you know there is no need to go through the txt route, so long as
"FieldInfo:=Array(1, 4)" is in the cod
"FieldInfo:=Array(1, 1)" is the default value and is US forma
Thanks again for your help.

 

[Tom Ogilvy]

I think it depends on which version of excel you are using - but that is
good to know.