CODEWITHSUNDEEP
vbaexceldatetimecsv
Amber
Amber

Reputation: 944

Date format of CSV file generated through macro

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

Upvotes: 1

Views: 1068

Answers (1)

laylarenee
laylarenee

Reputation: 3284

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.

Upvotes: 1

Related Questions