VBA, Opening .CSV file from VBA mis-converts SOME dates

Opening .CSV file from VBA mis-converts SOME dates - how to work-around

Author	Message
LaoGu #1 / 4	Opening .CSV file from VBA mis-converts SOME dates - how to work-around When System date is DD/MM/YYYY and a csv file is opened in Excel VBA, dates in dd/mm/yyyy format and DD<=12 are incorrectly interpreted as American dates, and converted to MM/DD/YYYY. This doesn't happen if the CSV file is opened directly in Excel. So 12/11/2000 is interpreted as 11 Dec, 14/11/2000 is correctly interpreted as 14 Nov Has anyone else experienced this problem (unbelievable if not) but American guys I guess unlikely! <g> Does anyone know of any MSDN Q number, of MSDN knowledgebase reference,? Does anyone have a workaround? Greg (Oz, Sydney)
Mon, 05 May 2003 03:00:00 GMT

Cindy Meister -WordMVP #2 / 4	Opening .CSV file from VBA mis-converts SOME dates - how to work-around Hi LaoGui, I just read a thread in one of the .DATA newsgroups where someone said the Text ODBC driver (which is what you'd probably be working with via VBA) only works with dates in US format; I assume Excel performs some kind of conversion when you work through its interface... Quote: > When System date is DD/MM/YYYY and a csv file is opened in Excel VBA, dates > in dd/mm/yyyy format and DD<=12 are incorrectly interpreted as American > dates, and converted to MM/DD/YYYY. This doesn't happen if the CSV file is > opened directly in Excel. > So 12/11/2000 is interpreted as 11 Dec, 14/11/2000 is correctly interpreted > as 14 Nov > Has anyone else experienced this problem (unbelievable if not) but American > guys I guess unlikely! <g> Cindy Meister INTER-Solutions, Switzerland http://homepage.swissonline.ch/cindymeister http://www.mvps.org/word http://go.compuserve.com/MSOfficeForum This reply is posted in the Newsgroup; please post any follow question or reply in the newsgroup and not by e-mail :-)
Mon, 05 May 2003 03:00:00 GMT

Kenneth La #3 / 4	Opening .CSV file from VBA mis-converts SOME dates - how to work-around Quote: >Does anyone have a workaround? My method is open it as XXX.TXT Set the number format of that columns as text when opening that TXT. After open, change that column to approproate Date format Then do a search and replace "/" to "/" Regards. -- "Reply-To" address is OK.
Mon, 05 May 2003 03:00:00 GMT

LaoGu #4 / 4	Opening .CSV file from VBA mis-converts SOME dates - how to work-around Thank you... Quote: > Hi LaoGui, > I just read a thread in one of the .DATA newsgroups where someone said the > Text ODBC driver (which is what you'd probably be working with via VBA) only > works with dates in US format; I assume Excel performs some kind of > conversion when you work through its interface... > > When System date is DD/MM/YYYY and a csv file is opened in Excel VBA, dates > > in dd/mm/yyyy format and DD<=12 are incorrectly interpreted as American > > dates, and converted to MM/DD/YYYY. This doesn't happen if the CSV file is > > opened directly in Excel. > > So 12/11/2000 is interpreted as 11 Dec, 14/11/2000 is correctly interpreted > > as 14 Nov > > Has anyone else experienced this problem (unbelievable if not) but American > > guys I guess unlikely! <g> > Cindy Meister > INTER-Solutions, Switzerland > http://homepage.swissonline.ch/cindymeister > http://www.mvps.org/word > http://go.compuserve.com/MSOfficeForum > This reply is posted in the Newsgroup; please post any follow question or > reply in the newsgroup and not by e-mail :-)
Mon, 12 May 2003 03:00:00 GMT

Page 1 of 1

[ 4 post ]