Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem when opening a csv with dates in dd-mm-yyyy format.
When I open a csv with a date in one "cell" excel allways interprets the date
as mm/dd/yyyy if I do it in VBA, but if I open the CSV manually then excel interprets the date according to my system date settings. How can I tell excel how to interpret the date within VBA when opening a CSV? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem when opening a csv with dates in dd-mm-yyyy format.
Hi Jeroen,
I think this is a bit of a bug (foible may be a less controversial word) in XL2000 - I guess that's what you're using. I don't notice the same behaviour in XL2003 but I remember it being a real pain for me in XL2000. You can get round this easily by specifying, when opening, how you want each column to be interpreted. In the below example I've said columns 1 and 2 should be considered as DMY and column 3 should be MDY. Sub openfiledates() Dim myFieldInfo As Variant myFieldInfo = Array(Array(1, xlDMYFormat), _ Array(2, xlDMYFormat), _ Array(3, xlMDYFormat)) Workbooks.OpenText Filename:="c:\temp\temp.txt", _ DataType:=xlDelimited, _ Comma:=True, _ FieldInfo:=myFieldInfo End Sub I hope this helps. My advice is (I'm sure you know but I'll say it anyway) whenever you can, specify to any suppliers of text files (clients, other departments etc.) that they use a format such as DD/MMM/YYY i.e. where the month is written as text. That ensures avoid any nasty surprises further down the line!! cya, Gareth Jeroen Hofs wrote: When I open a csv with a date in one "cell" excel allways interprets the date as mm/dd/yyyy if I do it in VBA, but if I open the CSV manually then excel interprets the date according to my system date settings. How can I tell excel how to interpret the date within VBA when opening a CSV? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem when opening a csv with dates in dd-mm-yyyy format.
Just to add to Gareth's reply.
Notice he changed the file name to .txt first (Filename:="c:\temp\temp.txt"). If you leave the extension .CSV, then VBA will ignore your code and do what it wants (based on its USA centric rules). (I'd rename the file to .txt and record a macro to get all the fields defined correctly.) Then if I get files of the same format, I'd just rerun this macro. Gareth wrote: Hi Jeroen, I think this is a bit of a bug (foible may be a less controversial word) in XL2000 - I guess that's what you're using. I don't notice the same behaviour in XL2003 but I remember it being a real pain for me in XL2000. You can get round this easily by specifying, when opening, how you want each column to be interpreted. In the below example I've said columns 1 and 2 should be considered as DMY and column 3 should be MDY. Sub openfiledates() Dim myFieldInfo As Variant myFieldInfo = Array(Array(1, xlDMYFormat), _ Array(2, xlDMYFormat), _ Array(3, xlMDYFormat)) Workbooks.OpenText Filename:="c:\temp\temp.txt", _ DataType:=xlDelimited, _ Comma:=True, _ FieldInfo:=myFieldInfo End Sub I hope this helps. My advice is (I'm sure you know but I'll say it anyway) whenever you can, specify to any suppliers of text files (clients, other departments etc.) that they use a format such as DD/MMM/YYY i.e. where the month is written as text. That ensures avoid any nasty surprises further down the line!! cya, Gareth Jeroen Hofs wrote: When I open a csv with a date in one "cell" excel allways interprets the date as mm/dd/yyyy if I do it in VBA, but if I open the CSV manually then excel interprets the date according to my system date settings. How can I tell excel how to interpret the date within VBA when opening a CSV? -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem when opening a csv with dates in dd-mm-yyyy forma
Thanks a lot Gareth!
As I'm a beginner at this I would never have come up with this. Regards, Jeroen "Gareth" wrote: Hi Jeroen, I think this is a bit of a bug (foible may be a less controversial word) in XL2000 - I guess that's what you're using. I don't notice the same behaviour in XL2003 but I remember it being a real pain for me in XL2000. You can get round this easily by specifying, when opening, how you want each column to be interpreted. In the below example I've said columns 1 and 2 should be considered as DMY and column 3 should be MDY. Sub openfiledates() Dim myFieldInfo As Variant myFieldInfo = Array(Array(1, xlDMYFormat), _ Array(2, xlDMYFormat), _ Array(3, xlMDYFormat)) Workbooks.OpenText Filename:="c:\temp\temp.txt", _ DataType:=xlDelimited, _ Comma:=True, _ FieldInfo:=myFieldInfo End Sub I hope this helps. My advice is (I'm sure you know but I'll say it anyway) whenever you can, specify to any suppliers of text files (clients, other departments etc.) that they use a format such as DD/MMM/YYY i.e. where the month is written as text. That ensures avoid any nasty surprises further down the line!! cya, Gareth Jeroen Hofs wrote: When I open a csv with a date in one "cell" excel allways interprets the date as mm/dd/yyyy if I do it in VBA, but if I open the CSV manually then excel interprets the date according to my system date settings. How can I tell excel how to interpret the date within VBA when opening a CSV? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date problem when opening a csv with dates in dd-mm-yyyy forma
Thanks for clarifying Dave.
Regards, Jeroen "Dave Peterson" wrote: Just to add to Gareth's reply. Notice he changed the file name to .txt first (Filename:="c:\temp\temp.txt"). If you leave the extension .CSV, then VBA will ignore your code and do what it wants (based on its USA centric rules). (I'd rename the file to .txt and record a macro to get all the fields defined correctly.) Then if I get files of the same format, I'd just rerun this macro. Gareth wrote: Hi Jeroen, I think this is a bit of a bug (foible may be a less controversial word) in XL2000 - I guess that's what you're using. I don't notice the same behaviour in XL2003 but I remember it being a real pain for me in XL2000. You can get round this easily by specifying, when opening, how you want each column to be interpreted. In the below example I've said columns 1 and 2 should be considered as DMY and column 3 should be MDY. Sub openfiledates() Dim myFieldInfo As Variant myFieldInfo = Array(Array(1, xlDMYFormat), _ Array(2, xlDMYFormat), _ Array(3, xlMDYFormat)) Workbooks.OpenText Filename:="c:\temp\temp.txt", _ DataType:=xlDelimited, _ Comma:=True, _ FieldInfo:=myFieldInfo End Sub I hope this helps. My advice is (I'm sure you know but I'll say it anyway) whenever you can, specify to any suppliers of text files (clients, other departments etc.) that they use a format such as DD/MMM/YYY i.e. where the month is written as text. That ensures avoid any nasty surprises further down the line!! cya, Gareth Jeroen Hofs wrote: When I open a csv with a date in one "cell" excel allways interprets the date as mm/dd/yyyy if I do it in VBA, but if I open the CSV manually then excel interprets the date according to my system date settings. How can I tell excel how to interpret the date within VBA when opening a CSV? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change date format from dd/mm/yyyy to mm/yyyy | Excel Discussion (Misc queries) | |||
Help with date format mm/dd/yyyy vs. dd/mm/yyyy | Excel Discussion (Misc queries) | |||
how do I change date from mm/dd/yyyy to dd:mm:yyyy format in Excel | New Users to Excel | |||
opening excel file - date format problem: DD/MM/YYYY vs MM/DD/YYYY | Excel Programming | |||
Having problem getting date into format: yyyy/mm/dd | Excel Programming |