Date format changes to m/dd/yyyy in CSV

Isabella

Well-known Member
Joined
Nov 7, 2008
Messages
643
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
activeworkbook.SaveAs Filename:= _<o:p></o:p>
"s:\Testing\mytest.csv", FileFormat:=xlCSV, _<o:p></o:p>
CreateBackup:=False<o:p></o:p>
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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

Well-known Member
Joined
Nov 7, 2008
Messages
643

ADVERTISEMENT

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?
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Rich (BB code):
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:

Rich (BB code):
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,371
Messages
5,595,777
Members
414,020
Latest member
Meghdad

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top