CSV export changing date format

[Dgenty]

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

 

[ranman256]

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.

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