VB reformats dates when opening .CSV in excel
 

I've had a look thru old posts, i think this one has come up before
but no-one seems to have a simple answer; I was upgraded (?)
yesterday from office 97 to 2000, including excel and vb, now xl2000
and vb6. i have a simple macro which opens a .CSV file (which comes
from a unix box via ftp), and saves it as .XLS one column is dates.
under XL97 no problem. Under XL2000, if i do it manually
(openfile/saveas .csv) no problem. If i do it under a VB6 macro,
either old one, or new one recorded today from xl2000, then when the
csv is opened it shows dates reversed; eg using a text editor the
date from unix box in the .csv is 05/08 (yesterday). using manual
opening of .csv, it's 05/08, serial number is 37839 (if i reformat it
as a number). Using macro to open it, it shows 08/05 (which my
machine reads as 8th may), serial number is 37780.
is there a simple way to set the date format used in the vba
process to be NOT US-centric? obviously this needs to be able to be
done before the file is opened, as once it's opened the serial number
is changed. thanks kkk

VB reformats dates when opening .CSV in excel
 

Look at the arguments for the opentext method. If you can't get it to work
by choosing appropriate options, then you will need to rename it to text and
do your own conversion after the column comes in as text.

Previous answer:

You can try naming the file with a txt extension. Then code opentext to
treat that column as text. You can convert it to a date once it is in
Excel, using your code. Reform the column, then do a replace of - with - in
the edit menu. This should cause the text string to be evaluated as a date.


Regards,
Tom Ogilvy

"kk" wrote in message
om...
I've had a look thru old posts, i think this one has come up before
but no-one seems to have a simple answer; I was upgraded (?)
yesterday from office 97 to 2000, including excel and vb, now xl2000
and vb6. i have a simple macro which opens a .CSV file (which comes
from a unix box via ftp), and saves it as .XLS one column is dates.
under XL97 no problem. Under XL2000, if i do it manually
(openfile/saveas .csv) no problem. If i do it under a VB6 macro,
either old one, or new one recorded today from xl2000, then when the
csv is opened it shows dates reversed; eg using a text editor the
date from unix box in the .csv is 05/08 (yesterday). using manual
opening of .csv, it's 05/08, serial number is 37839 (if i reformat it
as a number). Using macro to open it, it shows 08/05 (which my
machine reads as 8th may), serial number is 37780.
is there a simple way to set the date format used in the vba
process to be NOT US-centric? obviously this needs to be able to be
done before the file is opened, as once it's opened the serial number
is changed. thanks kkk

VB reformats dates when opening .CSV in excel
 

thanks for that, i kinda thought thats the solution that might be
proposed; however it strikes me that the thing worked perfectly two
days ago, when i was using office 97, excel 97, excel 97 sr-1, visual
basic that comes with it, but doesn't have a number on the "about"
screen..... so i wondered about either some sort of "environment
variable" (v loosely used prhrase) under which either the new excel or
vb could be opened (like a command0line switch) which would make it
behave like the old version, or just rolling back one or the other of
the two new applic's, whichever it is that is doing this, or perhaps
i'll just roll them both back to '97 versions?

any thoughts pls?

"Tom Ogilvy" wrote in message ...
Look at the arguments for the opentext method. If you can't get it to work
by choosing appropriate options, then you will need to rename it to text and
do your own conversion after the column comes in as text.

Previous answer:

You can try naming the file with a txt extension. Then code opentext to
treat that column as text. You can convert it to a date once it is in
Excel, using your code. Reform the column, then do a replace of - with - in
the edit menu. This should cause the text string to be evaluated as a date.


Regards,
Tom Ogilvy

"kk" wrote in message
om...
I've had a look thru old posts, i think this one has come up before
but no-one seems to have a simple answer; I was upgraded (?)
yesterday from office 97 to 2000, including excel and vb, now xl2000
and vb6. i have a simple macro which opens a .CSV file (which comes
from a unix box via ftp), and saves it as .XLS one column is dates.
under XL97 no problem. Under XL2000, if i do it manually
(openfile/saveas .csv) no problem. If i do it under a VB6 macro,
either old one, or new one recorded today from xl2000, then when the
csv is opened it shows dates reversed; eg using a text editor the
date from unix box in the .csv is 05/08 (yesterday). using manual
opening of .csv, it's 05/08, serial number is 37839 (if i reformat it
as a number). Using macro to open it, it shows 08/05 (which my
machine reads as 8th may), serial number is 37780.
is there a simple way to set the date format used in the vba
process to be NOT US-centric? obviously this needs to be able to be
done before the file is opened, as once it's opened the serial number
is changed. thanks kkk