CSV export changing date format

Dgenty

New Member
Joined
Sep 8, 2014
Messages
19
Hi all,

I am using the below VBA to export a single sheet as a new CSV.
In the source file the date is shown as DD-MM-YYYY however when using the below it changes in the CSV file to MM-DD-YYYY?
If I don't use the VBA and copy the tab and save as CSV manually it does not do this so I can only assume I am missing something from the VBA?

Can anyone advise?

Also column A in the source data is in format of 5 digits with the first digit always as a "0" EG "01234" and the CSV file removes the leading "0" is there a way to stop this?



Sub saveExpressToCSV()


Dim myCSVFileName As String
Dim tempWB As Workbook


Application.DisplayAlerts = False
On Error GoTo err


myCSVFileName = ThisWorkbook.Path & "" & "Matrix-Express-" & VBA.Format(VBA.Now, "dd-MMM-yyyy hh-mm") & ".csv"


ThisWorkbook.Sheets("PriceRuleDaysExport").Activate
ActiveSheet.Copy
Set tempWB = ActiveWorkbook


With tempWB
.SaveAs Filename:=myCSVFileName, FileFormat:=xlCSV, CreateBackup:=False
.Close
End With
err:
Application.DisplayAlerts = True

Call Return_Express

End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,977
numbers with leading zeros will be removed. convert it to text.
you must use delimiters so excel knows this is text.
i convert the entire column to text by running a macro that puts a single quote in the front. Excel is no longer confused that it is text.

Code:
Public Sub Cvt2Txt()
range("c1").select
While ActiveCell.Value <> ""
   If Left(ActiveCell.Value, 1) <> "'" Then ActiveCell.Value = "'" & ActiveCell.Value
   ActiveCell.Offset(1, 0).Select   'next row
Wend
End Sub

same with the date ....dates are numeric, so no matter how you format them the # is the same.
the Export schema determines how to export the data ,no matter the cell format.
it also tells it what delimiters to use,(if any)

set your dates to be dd-mm-yyyy

https://docs.microsoft.com/en-us/sql/odbc/microsoft/schema-ini-file-text-file-driver
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,931
Latest member
pattichis

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