Reputation: 944
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
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