Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
csv to xls date US/UK format
Good idea, thanks Tom
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
How do I convert dd/mm/yy date format to yyddd Julian date format | Excel Worksheet Functions | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
Convert date + time text format to date format | Excel Worksheet Functions | |||
Excel 2000 date format cannot be set to Australian date format | Excel Discussion (Misc queries) |