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