csv to xls date US/UK format
 

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?

csv to xls date US/UK format
 

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

"Lawler" wrote in message
...
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?

csv to xls date US/UK format
 

Good idea, thanks Tom

csv to xls date US/UK format
 

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.

csv to xls date US/UK format
 

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

--
Regards,
Tom Ogilvy


Lawler wrote in message
...
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 code
"FieldInfo:=Array(1, 1)" is the default value and is US format
Thanks again for your help.