save as csv cell format

[Guest]

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]

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.

 

[Guest]

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\MESSERmmyy",
fileFilter:="CSV Files (*.csv), *.csv")

'save the file

ActiveWorkbook.SaveAs Filename:= _
fileSaveName, FileFormat _
:=xlCSV, CreateBackup:=False

 

[Guest]

Another question regarding my MACRO:

fileSaveName = Application.GetSaveAsFilename("\\c:\MESSER\MESSERmmyy",
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]

#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

 

[Guest]

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

 

[Guest]

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\MESSERmmyy",
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]

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

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\MESSERmmyy",
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

 

[Guest]

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\MESSERmmyy",
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

 

[Guest]

Hi Dave,
ignore my previous question...problem solved.
Again Thanks a lot for your help.
Helmut....have a nice Day and God Bless You!