| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
date format changes when I save to CSV via a macro, but not manual
I'm using a macro to extract rows of data from a large, master CSV file -
breaking it down into chunks and re-saving it as smaller CSV files. One of the columns in the master CSV file contains date and time data in the format "dd/mm/yyyy hh:mm" The macro pastes this data into the workbook fine, but when the macro saves the sheet: ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False .... all the dates have been transposed into mm/dd/yyyy! This doesn't happen when I save the sheet manually (Office button/Save As... CSV) My region settings are all set to U.K. - what's going on? Thanks, in advance, for your help, Saul |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format changes when I save to CSV via a macro, but not manual
How did you verify that the dates changed?
Did you reopen the CSV file in Excel or in Notepad? If you used excel, try using Notepad. brawlsadford wrote: I'm using a macro to extract rows of data from a large, master CSV file - breaking it down into chunks and re-saving it as smaller CSV files. One of the columns in the master CSV file contains date and time data in the format "dd/mm/yyyy hh:mm" The macro pastes this data into the workbook fine, but when the macro saves the sheet: ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False ... all the dates have been transposed into mm/dd/yyyy! This doesn't happen when I save the sheet manually (Office button/Save As... CSV) My region settings are all set to U.K. - what's going on? Thanks, in advance, for your help, Saul -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format changes when I save to CSV via a macro
Hi Dave,
thanks for your response - I appreciate the help on this one! Whether I open the new CSV file in Notepad or Excel, the entry reads: 9/14/2006 0:00 (in Excel the cell format upon opening is "General" - note the change in hour format too) The same entry in the master CSV (in both Notepad and Excel) looks like this: 14/09/2006 00:00:00 (in Excel the cell format upon opening is "Custom - dd/mm/yyyy hh:mm" ) When the data is pasted across into the macro workbook, the U.K. formatting is preserved. After saving as CSV (automatically via the macro), the formatting in the macro Workbook (i.e. the xlsm file) is still U.K. - if I then save to CSV manually, the formatting is still dd/mm/yyyy hh:mm It's only in the macro-saved version that this transposition to mm/dd/yyyy h:mm is present. Am I missing something? Thanks Again, Saul "Dave Peterson" wrote: How did you verify that the dates changed? Did you reopen the CSV file in Excel or in Notepad? If you used excel, try using Notepad. brawlsadford wrote: I'm using a macro to extract rows of data from a large, master CSV file - breaking it down into chunks and re-saving it as smaller CSV files. One of the columns in the master CSV file contains date and time data in the format "dd/mm/yyyy hh:mm" The macro pastes this data into the workbook fine, but when the macro saves the sheet: ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False ... all the dates have been transposed into mm/dd/yyyy! This doesn't happen when I save the sheet manually (Office button/Save As... CSV) My region settings are all set to U.K. - what's going on? Thanks, in advance, for your help, Saul |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format changes when I save to CSV via a macro
I'm not sure what you're doing. But maybe this will help (or may be not!).
This is what I tried. (I use USA date format mm/dd/yyyy). I started a new workbook. I put today's date in A1:I26. It showed up as 05/16/2007. I selected that range and formatted it as dd/mm/yyyy. The dates appeared as 16/05/2007. I saved that workbook manually (file|saveas) as a .csv file and saw this when I opened the file in NotePad. 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... .... So it looked like the formatted text was saved in the .CSV file. Then I ran a macro to save that same file: Option Explicit Sub testme() ThisWorkbook.SaveAs Filename:="C:\my documents\excel\book1.csv", _ FileFormat:=xlCSV End Sub When I opened the file in Notepad, I saw the same formatted text. 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... .... =========== But the problem occurs when I try to bring that data back into excel. With .csv files, excel won't stop to ask you what each field is and what order the dates are formatted. It just does what it wants. In this case, 16/05/2007 would be brought in as Text--since it doesn't look like a date according to my windows short date format (mdy). The values that look like dates (01/02/2007, say) will be brought into excel using my windows short date format. 01/02/2007 would be brought in as January 2, 2007--no matter what the date was supposed to be (dmy would/should have been February 1, 2007). ====== One way around this if you must bring the file back into excel is to rename the ..csv file to a .txt file. Then open excel and use File|Open to open that .txt file. You'll see a text to columns wizard pop up and you can specify that this field is a date in whatever order you need. ========= An alternative would be to close excel (not sure if this is necessary) and change your windows short date format (under the control panel|regional settings applet) so that it matches the same order as the .csv file. And change that setting back when you're done--it is a windows wide setting. brawlsadford wrote: Hi Dave, thanks for your response - I appreciate the help on this one! Whether I open the new CSV file in Notepad or Excel, the entry reads: 9/14/2006 0:00 (in Excel the cell format upon opening is "General" - note the change in hour format too) The same entry in the master CSV (in both Notepad and Excel) looks like this: 14/09/2006 00:00:00 (in Excel the cell format upon opening is "Custom - dd/mm/yyyy hh:mm" ) When the data is pasted across into the macro workbook, the U.K. formatting is preserved. After saving as CSV (automatically via the macro), the formatting in the macro Workbook (i.e. the xlsm file) is still U.K. - if I then save to CSV manually, the formatting is still dd/mm/yyyy hh:mm It's only in the macro-saved version that this transposition to mm/dd/yyyy h:mm is present. Am I missing something? Thanks Again, Saul "Dave Peterson" wrote: How did you verify that the dates changed? Did you reopen the CSV file in Excel or in Notepad? If you used excel, try using Notepad. brawlsadford wrote: I'm using a macro to extract rows of data from a large, master CSV file - breaking it down into chunks and re-saving it as smaller CSV files. One of the columns in the master CSV file contains date and time data in the format "dd/mm/yyyy hh:mm" The macro pastes this data into the workbook fine, but when the macro saves the sheet: ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False ... all the dates have been transposed into mm/dd/yyyy! This doesn't happen when I save the sheet manually (Office button/Save As... CSV) My region settings are all set to U.K. - what's going on? Thanks, in advance, for your help, Saul -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
date format changes when I save to CSV via a macro
Hi Dave,
thanks for the explanation - your suggestion to change the 'source' CSV file to a TXT file, and then use the text to columns wizard to open it into Excel, works perfectly. Cheers, Saul "Dave Peterson" wrote: I'm not sure what you're doing. But maybe this will help (or may be not!). This is what I tried. (I use USA date format mm/dd/yyyy). I started a new workbook. I put today's date in A1:I26. It showed up as 05/16/2007. I selected that range and formatted it as dd/mm/yyyy. The dates appeared as 16/05/2007. I saved that workbook manually (file|saveas) as a .csv file and saw this when I opened the file in NotePad. 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... .... So it looked like the formatted text was saved in the .CSV file. Then I ran a macro to save that same file: Option Explicit Sub testme() ThisWorkbook.SaveAs Filename:="C:\my documents\excel\book1.csv", _ FileFormat:=xlCSV End Sub When I opened the file in Notepad, I saw the same formatted text. 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... 16/05/2007,16/05/2007,16/05/2007,16/05/2007,... .... =========== But the problem occurs when I try to bring that data back into excel. With .csv files, excel won't stop to ask you what each field is and what order the dates are formatted. It just does what it wants. In this case, 16/05/2007 would be brought in as Text--since it doesn't look like a date according to my windows short date format (mdy). The values that look like dates (01/02/2007, say) will be brought into excel using my windows short date format. 01/02/2007 would be brought in as January 2, 2007--no matter what the date was supposed to be (dmy would/should have been February 1, 2007). ====== One way around this if you must bring the file back into excel is to rename the ..csv file to a .txt file. Then open excel and use File|Open to open that .txt file. You'll see a text to columns wizard pop up and you can specify that this field is a date in whatever order you need. ========= An alternative would be to close excel (not sure if this is necessary) and change your windows short date format (under the control panel|regional settings applet) so that it matches the same order as the .csv file. And change that setting back when you're done--it is a windows wide setting. brawlsadford wrote: Hi Dave, thanks for your response - I appreciate the help on this one! Whether I open the new CSV file in Notepad or Excel, the entry reads: 9/14/2006 0:00 (in Excel the cell format upon opening is "General" - note the change in hour format too) The same entry in the master CSV (in both Notepad and Excel) looks like this: 14/09/2006 00:00:00 (in Excel the cell format upon opening is "Custom - dd/mm/yyyy hh:mm" ) When the data is pasted across into the macro workbook, the U.K. formatting is preserved. After saving as CSV (automatically via the macro), the formatting in the macro Workbook (i.e. the xlsm file) is still U.K. - if I then save to CSV manually, the formatting is still dd/mm/yyyy hh:mm It's only in the macro-saved version that this transposition to mm/dd/yyyy h:mm is present. Am I missing something? Thanks Again, Saul "Dave Peterson" wrote: How did you verify that the dates changed? Did you reopen the CSV file in Excel or in Notepad? If you used excel, try using Notepad. brawlsadford wrote: I'm using a macro to extract rows of data from a large, master CSV file - breaking it down into chunks and re-saving it as smaller CSV files. One of the columns in the master CSV file contains date and time data in the format "dd/mm/yyyy hh:mm" The macro pastes this data into the workbook fine, but when the macro saves the sheet: ActiveWorkbook.SaveAs Filename:=ActiveSheet.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False ... all the dates have been transposed into mm/dd/yyyy! This doesn't happen when I save the sheet manually (Office button/Save As... CSV) My region settings are all set to U.K. - what's going on? Thanks, in advance, for your help, Saul -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Auto Date with Manual Entry Protection | Excel Worksheet Functions | |||
| save workbook as current date using a macro | Excel Discussion (Misc queries) | |||
| Excel Macro save as date of last friday | Excel Discussion (Misc queries) | |||
| Macro to auto save file using the format YYMM | Excel Discussion (Misc queries) | |||
| RECORDED MACRO PASTE'S DATE DIFFERENTLY TO MANUAL PASTE | Excel Worksheet Functions | |||
Linear Mode
