Workbooks.Open incorrectly formating date column
 

Hi,
I have a CSV file and one of the columns is date in the format of
DD/MM/YYYY. If I open this manually the date column is fine. If however I
open the same file in code...
Workbooks.Open Filename:="Log.csv"
Excel converts the dates where the first number is less than 13 to an
american type date. I.e. 03/10/2004 becomes 10/03/2004.

How can I stop this from happening???
Ta
Paul

Workbooks.Open incorrectly formating date column
 

CSV file format and Excel are notorious
if your regional settings are anything but USenglish :(


Step 1 .. RENAME the thing to TXT.


DO you have Excel XP or 2003?

Check out the LOCAL argument in
OpenText and
SaveAS methods

(again: ONLY for excel XP / 2003)
it appears to have been added as an afterthought..
and is hardly documented in VBA help.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"?B?UGF1bA==?=" wrote:

Hi,
I have a CSV file and one of the columns is date in the format of
DD/MM/YYYY. If I open this manually the date column is fine. If
however I open the same file in code...
Workbooks.Open Filename:="Log.csv"
Excel converts the dates where the first number is less than 13 to an
american type date. I.e. 03/10/2004 becomes 10/03/2004.

How can I stop this from happening???
Ta
Paul

Workbooks.Open incorrectly formating date column
 

Hi Paul,

I have a CSV file and one of the columns is date in the format of
DD/MM/YYYY. If I open this manually the date column is fine. If however I
open the same file in code...
Workbooks.Open Filename:="Log.csv"
Excel converts the dates where the first number is less than 13 to an
american type date. I.e. 03/10/2004 becomes 10/03/2004.

How can I stop this from happening???

Use Workbooks.OpenText, which takes additional parameters to specify the
DMY/MDY order of dates. You might have to rename the file's extension from
csv to something else though, as Excel sometimes treats files with a 'csv'
extenions in a 'special' way.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

Workbooks.Open incorrectly formating date column
 

Rename the workbook with a .txt extension.

Turn on the macro recorder

Manually Open the workbook using File=Open

Walk through the text import wizard. In the 3rd dialog, specify the date
order for that column

Complete opening the file and turn off the macro recorder.

Now modify the macro to rename the file with a .txt extension, open it using
the recorded opentext method.

Whatever else you need.

--
Regards,
Tom Ogilvy

"Paul" wrote in message
...
Hi,
I have a CSV file and one of the columns is date in the format of
DD/MM/YYYY. If I open this manually the date column is fine. If however
I
open the same file in code...
Workbooks.Open Filename:="Log.csv"
Excel converts the dates where the first number is less than 13 to an
american type date. I.e. 03/10/2004 becomes 10/03/2004.

How can I stop this from happening???
Ta
Paul