 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007date 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 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. > 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. [quoted text clipped - 17 lines] > > Saul  SignatureDave Peterson 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 > How did you verify that the dates changed? > [quoted text clipped - 23 lines] > > > > Saul 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. > Hi Dave, > [quoted text clipped - 49 lines] > > > > > > Saul SignatureDave Peterson 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 > I'm not sure what you're doing. But maybe this will help (or may be not!). > [quoted text clipped - 112 lines] > > > > > > > > Saul |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.