Date format changes to m/dd/yyyy in CSV

[Isabella]

Hi i am using the following code to save file as csv, but when i open the file the date format changes from dd/mm/yyyy to m/dd/yyyy, how can i overcome this?

Sheets("Sheet1").copy<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
activeworkbook.SaveAs Filename:= _<o></o>
"s:\Testing\mytest.csv", FileFormat:=xlCSV, _<o></o>
CreateBackup:=False<o></o>

 

[Richard Schollar]

Hi

Is there a paticular reason you are saving as a csv? You wouldn't get any unexpected results if you saved as an xls

 

[Isabella]

Hi

Is there a paticular reason you are saving as a csv? You wouldn't get any unexpected results if you saved as an xls

My file must be saved as CSV as it get uploaded into our in house system

 

[Richard Schollar]

The csv file should be being saved down fine - the problems only come when you try and re-open in Excel. Rather, confirm you have the correct format in the file by opening the csv in Notepad.

 

[Isabella]

The csv file should be being saved down fine - the problems only come when you try and re-open in Excel. Rather, confirm you have the correct format in the file by opening the csv in Notepad.

nope even in notepad its d/mm/yyyy

would the text function work in excel and then save as CSV?

 

[Richard Schollar]

My apologies Isabella - I neglected to appreciate you were doing this thru code (rather than the Excel interface). In these cases, I usually create the csv file directly rather than using the VBA built in functions (I find it more reliable). To do this I use a ciuple of custom functions:

Function WriteText(ByVal strPathAndFilename As String, ByVal strToWrite As String, Optional ByVal blnOverWrite As Boolean = False) As Boolean
Dim i As Integer, strTemp
strTemp = Dir(strPathAndFilename)
If Len(strTemp) > 0 Then
    If blnOverWrite Then
        Kill strPathAndFilename
    Else
        WriteText = False
        Exit Function
    End If
End If
i = FreeFile
Open strPathAndFilename For Binary Access Write As #i
    Put #i, , strToWrite
Close #i
WriteText = True
End Function
Function MakeText(ByRef rng As Range, Optional ByVal strDelim As String = ",", Optional ByVal strNewLine As String = vbCrLf) As String
Dim cell As Range
Dim i As Long, j As Long
Dim strTemp As String
If rng.Count = 1 Then
    MakeText = rng.Text
    Exit Function
Else
    
    For i = 1 To rng.Rows.Count
        For j = 1 To rng.Columns.Count
            strTemp = strTemp & rng.Cells(i, j).Text & strDelim
        Next j
        strTemp = Left(strTemp, Len(strTemp) - 1) & strNewLine
    Next i
    strTemp = Left(strTemp, Len(strTemp) - 1)
    MakeText = strTemp
End If
End Function

To create a csv you would then use these functions like so:

Sub CreateCSV()
Dim s As String
 
s = MakeText(Sheets("Sheet1").UsedRange)
 
WriteText "s:\Testing\mytest.csv", s, True

End Sub

Be careful with this as the MakeText function uses the Text property of the range of cells passed to it, so cell contents longer than 1024 characters will be truncated and some number formats actually include spaces which will be carried across when accessing Text. All date formats will be preserved, however.

Try running it and opening the file up in Notepad to verify it is as you want it.

 

[Legacy 68668]

See here