Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA Using FileFormat:=xlText | Excel Discussion (Misc queries) | |||
SaveAs Filename and FileFormat in Vs2007 | Excel Discussion (Misc queries) | |||
Fileformat XLText | Excel Programming | |||
fileformat not working with Object | Excel Programming | |||
FileFormat Macro Problem | Excel Programming |