Exporting to CSV, messed up date format.

exPERten

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,916
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.

1593439434621.png
 

exPERten

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Jun 23, 2020
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,696
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You don't. All you need to do is say thanks to anyone who helped (as you have). :)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,557
Messages
5,637,043
Members
416,955
Latest member
Gohar hussain

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