Exporting date to csv formating problem

[steverob]

I need to export a list of dates from Excel of the form dd-mm-yy hh:mm to a .csv file where they have to be wrapped in double quotes. Like this
"31-12-2020 14:23"

This is turning out to be quite tricky. I tried the following:

a) Export as they are, and they appear in the csv file unwrapped.
b) Change them to text format - same thing. (Even though if you do the same thing with a list of words they do get wrapped in quotes)
c) I tried this:

=CONCATENATE(CHAR(34),TEXT(A1, "yyyy-mm-dd hh:mm"),CHAR(34)) - which wraps the date in cell A1 with double quotes.
Exporting this to a csv file ends up with the date wrapped in three sets of double quotes.

As a quick fix I then used the search and replace function but there must be a better way!
Can anyone help please?

 

[Marc L]

​

Via VBA there is nothing to change, quite easy but you forgot to attach a source workbook sample and accordingly the expected result text file as well …​

​

As the CSV file format does not require any double quote until there is a comma within a cell …​

 

[johnnyL]

I found the following code from microsoft, via googling, that may steer towards a solution. I have not tested it, I only did some formatting and stuff to the original code.

Sub QuoteCommaExport()
'
'   From: https://docs.microsoft.com/en-US/office/troubleshoot/excel/export-text-file-with-comma-quote
'
'   Microsoft Excel does not have a menu command to automatically export data to a text file so that the text file is exported
'   with both quotation marks and commas as delimiters.
'   However, you can create this functionality in Excel by using a Microsoft Visual Basic for Applications procedure.
'
'   Before you run the macro, select the data that you want to export, and then run the QuoteCommaExport subroutine.
'
    Dim DestFile As String
    Dim FileNum As Integer
    Dim ColumnCount As Long
    Dim RowCount As Long
'
    DestFile = InputBox("Enter the destination filename" & Chr(10) & "(with complete path):", "Quote-Comma Exporter")   ' Prompt user for destination file name
'
    FileNum = FreeFile()                                ' Obtain next free file handle number
'
    On Error Resume Next                                ' Turn error checking off
'
    Open DestFile For Output As #FileNum                ' Attempt to open destination file for output
'
    If Err <> 0 Then                                    ' If an error occurs report it and end
        MsgBox "Cannot open filename " & DestFile
        End
    End If
'
    On Error GoTo 0                                     ' Turn error checking on
'
    For RowCount = 1 To Selection.Rows.Count            ' Loop for each row in selection
        For ColumnCount = 1 To Selection.Columns.Count  ' Loop for each column in selection
'                                                                                           ' \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/ \/
            Print #FileNum, """" & Selection.Cells(RowCount, ColumnCount).Text & """";      ' Write current cell's text to file with quotation marks
'
            If ColumnCount = Selection.Columns.Count Then   ' Check if cell is in last column
                Print #FileNum,                             '   If so, then write a blank line
            Else
                Print #FileNum, ",";                        ' Otherwise, write a comma
            End If
        Next ColumnCount                                    ' Start next iteration of ColumnCount loop
    Next RowCount                                       ' Start next iteration of RowCount loop
'
    Close #FileNum                                      ' Close destination file
End Sub

 

[steverob]

Thanks you - will check