Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy
__with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Don't verify your data in that CSV file by reopening it in Excel.
Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Dave,
That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Another question regarding my MACRO:
fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False Dave, it picks up the "FILE_Name" but doesn't change directory to where it got the file name. Do I have to do a ChangeDirectory to actually get the file saved in this new directory? Helmut "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
#1. Did you verify this by opening the .csv file in excel or in Notepad? When
I opened the file in Notepad, it looked ok. #2. Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "c:\MESSER\MESSERmmyy"" ChDrive myNewFolder ChDir myNewFolder 'do your save 'change back ChDrive myCurFolder ChDir myCurFolder Helmut wrote: Dave, That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Dave,
Yes, I did verify it in Notepad. I does change the dd/mm/yyyy to mm\dd\yyyy it doesn't make any sense. I checked my 'global' settings on my computer and they are set for dd/mm/yyyy. If I save it 'manually' the file is ok. But when I use the MACRO to save as csv then it changes the date around. If you have any more suggestions, they are welcome. Thanks for your time already spent. Helmut "Dave Peterson" wrote: #1. Did you verify this by opening the .csv file in excel or in Notepad? When I opened the file in Notepad, it looked ok. #2. Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "c:\MESSER\MESSERmmyy"" ChDrive myNewFolder ChDir myNewFolder 'do your save 'change back ChDrive myCurFolder ChDir myCurFolder Helmut wrote: Dave, That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Dave,
it doesn't like: ChDir myNewFolder Also, when you say: 'do your save - do you mean this: -------------------------- 'do your save fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") If fileSaveName < False Then MsgBox "Save as " & fileSaveName End If 'save the file and close Workbook ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False --------------------------------------------- or did yo mean the normal SaveAs dialogue where you have to manually select csv? if I do the latter, the file saves ok but I don't want the manual intervention. thanks Helmut "Dave Peterson" wrote: #1. Did you verify this by opening the .csv file in excel or in Notepad? When I opened the file in Notepad, it looked ok. #2. Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "c:\MESSER\MESSERmmyy"" ChDrive myNewFolder ChDir myNewFolder 'do your save 'change back ChDrive myCurFolder ChDir myCurFolder Helmut wrote: Dave, That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Try dropping the \\ from the initial filename.
And I don't have a guess why you're having trouble with the dates. I know that there are some differences between saving in code vs saving manually, but I couldn't duplicate your results. If I formatted my dates as dd/mm/yyyy (my default settings are mm/dd/yyyy), the dates were saved in dmy order. I used xl2003 for my testing. Maybe you could write your own exporting program that would behave exactly the way you want: Here are three sites that you could steal some code from: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http://www.mcgimpsey.com/excel/textfiles.html Helmut wrote: Dave, it doesn't like: ChDir myNewFolder Also, when you say: 'do your save - do you mean this: -------------------------- 'do your save fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") If fileSaveName < False Then MsgBox "Save as " & fileSaveName End If 'save the file and close Workbook ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False --------------------------------------------- or did yo mean the normal SaveAs dialogue where you have to manually select csv? if I do the latter, the file saves ok but I don't want the manual intervention. thanks Helmut "Dave Peterson" wrote: #1. Did you verify this by opening the .csv file in excel or in Notepad? When I opened the file in Notepad, it looked ok. #2. Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "c:\MESSER\MESSERmmyy"" ChDrive myNewFolder ChDir myNewFolder 'do your save 'change back ChDrive myCurFolder ChDir myCurFolder Helmut wrote: Dave, That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Dave, one more question:
How can replace in the following macro that the "SEP" is always a "," and the Save Entire Worksheet question as automatic fixed in the Macro without user dialogue? ------------- Public Sub DoTheExport() Dim FName As Variant Dim Sep As String FName = Application.GetSaveAsFilename("c:\MESSER\MESSERmmy y", fileFilter:="CSV Files (*.csv), *.csv") If FName = False Then MsgBox "You didn't select a file" Exit Sub End If Sep = InputBox("Enter a single delimiter character (e.g., comma or semi-colon)", _ "Export To CSV File") ExportToTextFile CStr(FName), Sep, _ MsgBox("Do You Want To Export The Entire Worksheet?", _ vbYesNo, "Export To CSV File") = vbNo ExportToTextFile "FName", ",", False End Sub --------------------------- Thanks Helmut "Dave Peterson" wrote: Try dropping the \\ from the initial filename. And I don't have a guess why you're having trouble with the dates. I know that there are some differences between saving in code vs saving manually, but I couldn't duplicate your results. If I formatted my dates as dd/mm/yyyy (my default settings are mm/dd/yyyy), the dates were saved in dmy order. I used xl2003 for my testing. Maybe you could write your own exporting program that would behave exactly the way you want: Here are three sites that you could steal some code from: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http://www.mcgimpsey.com/excel/textfiles.html Helmut wrote: Dave, it doesn't like: ChDir myNewFolder Also, when you say: 'do your save - do you mean this: -------------------------- 'do your save fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") If fileSaveName < False Then MsgBox "Save as " & fileSaveName End If 'save the file and close Workbook ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False --------------------------------------------- or did yo mean the normal SaveAs dialogue where you have to manually select csv? if I do the latter, the file saves ok but I don't want the manual intervention. thanks Helmut "Dave Peterson" wrote: #1. Did you verify this by opening the .csv file in excel or in Notepad? When I opened the file in Notepad, it looked ok. #2. Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "c:\MESSER\MESSERmmyy"" ChDrive myNewFolder ChDir myNewFolder 'do your save 'change back ChDrive myCurFolder ChDir myCurFolder Helmut wrote: Dave, That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
save as csv cell format
Hi Dave,
ignore my previous question...problem solved. Again Thanks a lot for your help. Helmut....have a nice Day and God Bless You! "Dave Peterson" wrote: Try dropping the \\ from the initial filename. And I don't have a guess why you're having trouble with the dates. I know that there are some differences between saving in code vs saving manually, but I couldn't duplicate your results. If I formatted my dates as dd/mm/yyyy (my default settings are mm/dd/yyyy), the dates were saved in dmy order. I used xl2003 for my testing. Maybe you could write your own exporting program that would behave exactly the way you want: Here are three sites that you could steal some code from: Earl Kiosterud's Text Write program: www.smokeylake.com/excel (or directly: http://www.smokeylake.com/excel/text_write_program.htm) Chip Pearson's: http://www.cpearson.com/excel/imptext.htm J.E. McGimpsey's: http://www.mcgimpsey.com/excel/textfiles.html Helmut wrote: Dave, it doesn't like: ChDir myNewFolder Also, when you say: 'do your save - do you mean this: -------------------------- 'do your save fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") If fileSaveName < False Then MsgBox "Save as " & fileSaveName End If 'save the file and close Workbook ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False --------------------------------------------- or did yo mean the normal SaveAs dialogue where you have to manually select csv? if I do the latter, the file saves ok but I don't want the manual intervention. thanks Helmut "Dave Peterson" wrote: #1. Did you verify this by opening the .csv file in excel or in Notepad? When I opened the file in Notepad, it looked ok. #2. Dim myCurFolder As String Dim myNewFolder As String myCurFolder = CurDir myNewFolder = "c:\MESSER\MESSERmmyy"" ChDrive myNewFolder ChDir myNewFolder 'do your save 'change back ChDrive myCurFolder ChDir myCurFolder Helmut wrote: Dave, That's not it.... 1 - I do a manual Save-As and select in the dialogue box the CSV and save the file. \When opening it, the date in Column C is dd/mm/yyyy just as it was in the xls file. 2 - I run the Macro Save-As CSV and the Column C in the CSV file becomes mm/dd/yyyy here is the macro I use: fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERm myy", fileFilter:="CSV Files (*.csv), *.csv") 'save the file ActiveWorkbook.SaveAs Filename:= _ fileSaveName, FileFormat _ :=xlCSV, CreateBackup:=False "Dave Peterson" wrote: Don't verify your data in that CSV file by reopening it in Excel. Open that CSV file in NotePad to check it out. If you really want to reopen the CSV file in Excel, then rename it to .TXT and then you can specify the format you need for that date field. Helmut wrote: I have WORKSHEET(1) with Column "C" formatted as dd/mm/yyyy __with other values in other columns I copy and paste_special 'values' then I 'saveas' the file in *CSV format and when I open the CSV file the date format has changed to 'general' which is to be expected in CSV, but the value has also changed to m/dd/yyyy I need the format in the CSV file to be dd/mmm/yyyy thanks -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save in a .prn format | Excel Discussion (Misc queries) | |||
How do I save a custom cell format that I created? | Excel Worksheet Functions | |||
How can I format an Excel cell with a "save" or other command? | Excel Programming | |||
Custom Cell Format Will Not Save Correctly | Excel Worksheet Functions | |||
save button in excel to save one of the worksheets with a cell value as its name | Excel Programming |