Date format of CSV file generated through macro

Question

I'm trying to export data from an excel file to a csv file using a macro. It works fine except for the part where the date format from the excel file (dd-mmm-yyyy) gets changed to (dd-mmm-yy) in the csv file. I would like to keep the date format as (dd-mmm-yyyy) in the csv file as well.

Could anyone please help me? A part of the code that writes the data to the csv file is

c1 = 2
Dim tempDate As String
Dim tempDate2 As String

tempDate = Format(Sheets("Source").Cells(c1, 2).value, "dd-mmm-yyyy")
tempDate2 = Format(Sheets("Source").Cells(c1, 3).value, "dd-mmm-yyyy")


txtfile.WriteLine (Sheets("Source").Cells(c1, 1).value & sSep & tempDate & sSep & _
tempDate2 & sSep & Sheets("Source").Cells(c1, 4).value & sSep & _
temp1 & sSep & Sheets("Source").Cells(c1, 6).value & sSep & Sheets("Source").Cells(c1, 7).value & sSep & _
Sheets("Source").Cells(c1, 8).value)
c1 = c1 + 1

How are you checking the .csv file - using a text editor or opening it in Excel again? (should do the former) — Rory
@Rory I use Excel to check it. The csv file is later uploaded to fill in a database table. That's where the date format is required — Amber
Could you post the relevant code block that is malfunctioning? — laylarenee
@Amber Excel will reformat the data when it opens the csv file. Check it using a program like Notepad/Wordpad - it should be the same as was displayed when you created the csv. — Rory
@Amber, when you open the *.CSV file in Excel, Excel's default date format is applied since there is no formatting specified. You can't apply formatting in CSV's. — laylarenee

laylarenee laylarenee · Accepted Answer · 2015-01-12T16:58:28

It sounds like CSV files are set to open using Microsoft Excel. You could change the "Opens With" dialog to use another program, perhaps Notepad.exe.

When you open the CSV file using Microsoft Excel, Excel's default date format is applied to all dates since there is no formatting specified. CSV files cannot contain formatting information.

Possible Solution: prefix your formatted date string with an = symbol and wrap it in quotes so it's treated as a string like this:

="1/12/2014"

You could alternatively prefix the date with an apostrophe, which also causes Excel to treat it as a string:

'1/12/2014

Another third possible solution would be to create an actual Excel file.

Date format of CSV file generated through macro

1 Answers