Exporting to CSV, messed up date format.

[exPERten]

Using this:

Private Sub Create_CSV()
Dim content As String
Dim Rng As Range
Set Rng = Range("A12:AS30")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB As Workbook, _
sWS As Worksheet

Dim dWB As Workbook, _
dWS As Worksheet

Path = "PATH\"
FileName1 = Range("A16")
FileName2 = Range("B16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("A12:AS30").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV, Local:=True, CreateBackup:=False
dWB.Close False
End Sub


I have a file with formulas that collects data from other tabs in the workbook, and some of the fields contain dates, those fields are formatted correctly.
But when saving to CSV the date format (YYYY-MM-DD) is lost and it saves it as a number (43983) instead.

 

[Joe4]

I have a file with formulas that collects data from other tabs in the workbook, and some of the fields contain dates, those fields are formatted correctly.
But when saving to CSV the date format (YYYY-MM-DD) is lost and it saves it as a number (43983) instead.

How exactly are you trying to view the CSV file?
Are you using Excel, or some other text file viewer?
You should NEVER use Excel to view a CSV file, if you want to see what the data inside a CSV really looks like. Excel automatically does field conversions on opening CSV files, so it does not give you an accurate picture of the contents of the CSV file.
View the CSV in a Text Editor like "NotePad" to see what it truly looks like.

 

[exPERten]

How exactly are you trying to view the CSV file?
Are you using Excel, or some other text file viewer?
You should NEVER use Excel to view a CSV file, if you want to see what the data inside a CSV really looks like. Excel automatically does field conversions on opening CSV files, so it does not give you an accurate picture of the contents of the CSV file.
View the CSV in a Text Editor like "NotePad" to see what it truly looks like.

I opened the csv-file with notepad.

 

[Joe4]

Which column contains these dates?
Are they the result of formulas or are they hard-coded?
If you put a breakpoint in your code at the point where it saves the new file as a CSV, if you view that sheet that is being saved as the new CSV (that was copied after pasted from your original), what does that column look like?
Is if formatted correctly there?
If not, you may need to add a formatting step to that column before creating the CSV file.

 

[RoryA]

You're just pasting values, not formats. I'd suggest you paste formats after you paste the values and before you save as CSV.

 

[exPERten]

You're just pasting values, not formats. I'd suggest you paste formats after you paste the values and before you save as CSV.

How do I do that?

 

[exPERten]

Which column contains these dates?
Are they the result of formulas or are they hard-coded?
If you put a breakpoint in your code at the point where it saves the new file as a CSV, if you view that sheet that is being saved as the new CSV (that was copied after pasted from your original), what does that column look like?
Is if formatted correctly there?
If not, you may need to add a formatting step to that column before creating the CSV file.

The file is a mix of different data.
Some specific columns can contain data in form of a date, but it could also be empty. (A formula in each field collects data from elsewhere in the file, so NOT hardcoded)

If I do add a breakpoint, it opens an excel-window with all the data in it but the dates are in the wrong format.

 

[exPERten]

Nevermind guys.

Just added this as a longshot, and it worked. (Thanks for the tip RoryA)

dWS.Range("A1").PasteSpecial xlPasteFormats

I am totally new to this, but sometimes it seems to easy to be true, and sometimes it is totally unintuitive.


Thanks for all your help. It is much appreciated.

 

[exPERten]

How do I mark an issue as solved?

Solution:
Private Sub Create_CSV()
Dim content As String
Dim Rng As Range
Set Rng = Range("A12:AS30")
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Dim FileName3 As String

Dim sWB As Workbook, _
sWS As Worksheet

Dim dWB As Workbook, _
dWS As Worksheet

Path = "\\vcn.ds.volvo.net\vpt-kop\proj01\002301\VMMS\Migrationsfiler\Klara_För_Inmigrering_TEST\"
FileName1 = Range("A16")
FileName2 = Range("B16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet

Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)

sWS.Range("A12:AS30").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWS.Range("A1").PasteSpecial xlPasteFormats
dWB.SaveAs filename:=Path & FileName1 & "_" & FileName2 & ".csv", FileFormat:=xlCSV, Local:=True, CreateBackup:=False
dWB.Close False
End Sub

 

[RoryA]

You don't. All you need to do is say thanks to anyone who helped (as you have).