Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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




  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 4,391
Default 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






  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA Using FileFormat:=xlText [email protected] Excel Discussion (Misc queries) 0 March 26th 08 05:02 PM
SaveAs Filename and FileFormat in Vs2007 RobN[_2_] Excel Discussion (Misc queries) 4 July 14th 07 01:21 AM
Fileformat XLText JeepNC Excel Programming 1 February 14th 05 07:38 PM
fileformat not working with Object Nancy[_4_] Excel Programming 1 September 27th 04 01:35 PM
FileFormat Macro Problem Jeffrey[_6_] Excel Programming 0 March 5th 04 05:26 PM


All times are GMT +1. The time now is 08:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"