FileFormat:=xlCSV for a cell with =TODAY() gives unwantedamerican
 

When saving an excel file with (SaveAS) FileFormat:=xlCSV the file has a
cell with =TODAY(). After saving the file the date it looks ok (it is in the
Australian form dd/mm/yyyy) but when it is saved using the above mehtod the
result is an American Date

FileFormat:=xlCSV for a cell with =TODAY() gives unwantedamerican
 

Crewman,
I cannot reproduce you problem. Looking at the csv text file, the date is
format as it appeared in Excel.

NickHK

"Crewman" wrote in message
...
When saving an excel file with (SaveAS) FileFormat:=xlCSV the file has a
cell with =TODAY(). After saving the file the date it looks ok (it is in
the
Australian form dd/mm/yyyy) but when it is saved using the above mehtod
the
result is an American Date

FileFormat:=xlCSV for a cell with =TODAY() gives unwantedameri
 

Mine looks fine too until I close the file and then re-open it then the date
displays in the American format

"NickHK" wrote:

Crewman,
I cannot reproduce you problem. Looking at the csv text file, the date is
format as it appeared in Excel.

NickHK

"Crewman" wrote in message
...
When saving an excel file with (SaveAS) FileFormat:=xlCSV the file has a
cell with =TODAY(). After saving the file the date it looks ok (it is in
the
Australian form dd/mm/yyyy) but when it is saved using the above mehtod
the
result is an American Date

FileFormat:=xlCSV for a cell with =TODAY() gives unwantedameri
 

Crewman,
If I reopen the .csv in Excel, the date format matches it's text version,
i.e. correct.
So, not sure why yours should be changing.

NickHK

"Crewman" wrote in message
...
Mine looks fine too until I close the file and then re-open it then the
date
displays in the American format

"NickHK" wrote:

Crewman,
I cannot reproduce you problem. Looking at the csv text file, the date
is
format as it appeared in Excel.

NickHK

"Crewman" wrote in message
...
When saving an excel file with (SaveAS) FileFormat:=xlCSV the file has
a
cell with =TODAY(). After saving the file the date it looks ok (it is
in
the
Australian form dd/mm/yyyy) but when it is saved using the above
mehtod
the
result is an American Date

FileFormat:=xlCSV for a cell with =TODAY() gives unwantedamerican
 

I hope the following is some help.

Rgds



Dave



Microsoft's American programmers have had severe problems getting their
heads around the fact that certain none-US parts of the world use logical
(none-US) date formats. When you open a CSV file manually, all works well
and Excel takes into account the regional settings in the control panel and
correctly interprets dates accordingly. However, when the same thing is done
using a macro, the regional settings are often ignored and dates are then
misinterpreted as either dates in the US format (MM/DD/YY) or if that is not
possible, they aren't even considered by Excel to be dates.

Fortunately for most of the Excel versions we use, a work-around is
available.

Microsoft Knowledge Base Articles
The following reference is for Microsoft Knowledge Base articles relating to
this issue:

Q288839 - XL: Exported Comma Separated File Ignores Regional Settings

Excel 97
The following registry fix is needed:

Resolution: Add the following registry value:

Key:
KCU\Software\Microsoft\Office\9.0\Excel\Options
Value type: DWord
Value name: VBAAlwaysLoadUS
Value : 0



Excel 2000
The only workaround is to open the CSV file manually and then save it as an
Excel file but with the same name. To do this you will need to put quotes
around the filename and extension e.g. "IMAC.csv". This fools Excel into
thinking it is opening the CSV under macro control, but it's really opening
an Excel sheet and then it doesn't misinterpret the dates.

Excel 2002
Here a command option is available on the open command to force Excel to
take into account the regional settings when opening the CSV file. Under
previous versions of Excel, the command:
Workbooks.Open FileName:="PR.csv"
can be replaced by:
Workbooks.Open FileName:="PR.csv", local:=True

The local:=True forces Excel to take the regional settings into account when
opening the file. Unfortunately this command isn't accepted in earlier
versions of Excel.



"Crewman" wrote in message
...
When saving an excel file with (SaveAS) FileFormat:=xlCSV the file has a
cell with =TODAY(). After saving the file the date it looks ok (it is in
the
Australian form dd/mm/yyyy) but when it is saved using the above mehtod
the
result is an American Date