How can I prevent Excel from doubling quotes when exporting to CSV using xlCSV in VBA

I'm trying to export a worksheet to a comma delimited file using the xlCSV format. I have multiple massive sheets, and therefore a line by line approach would take hours.

If I try and save a column of entries e.g.

*,"product"

then the output looks like

*, """product"""

This is because it seems like fields with quotes in get automatically wrapped in double quotes

How do I get it so my output looks like

*, "product"

Is there a way to prevent Excel from automatically doubling quotes when saving as a CSV using xlCSV, or is there a work around that still uses xlCSV, but gives me control over the output format.

Sketch of my code:


sub ExportCSV(outputSheet As String, path As String)

Dim sourcews As Worksheet
Dim newws As Worksheet
Dim data as Variant

' define source worksheet, and open a new worksheet to copy data to, and then save to

data = sourcews.UsedRange.Value


With newws.Range("A1")
' paste data

workbook.SaveAs filename:=path, FileFormat:=xlCSV

Exit Sub

Solution

It seems there’s no built-in argument to control double quotes when exporting to CSV. As a workaround, you can replace them after the export is complete.

Please update the original post to include sample data if the script doesn't work with your actual dataset.

Sub SaveCSV()
    Const CSV_File As String = "D:\temp\Data.csv"
    ActiveWorkbook.SaveAs Filename:=CSV_File, _
        FileFormat:=xlCSV, CreateBackup:=False
    ReplaceTripleQuotesInCSV CSV_File
End Sub

Sub ReplaceTripleQuotesInCSV(ByVal filePath As String)
    Dim fileContent As String
    Dim fileNum As Long

    ' Open the file
    fileNum = FreeFile
    Open filePath For Input As #fileNum
    fileContent = Input$(LOF(fileNum), fileNum)
    Close #fileNum

    ' Replace all occurrences of triple quotes with a single quote
    fileContent = Replace(fileContent, String(2, Chr(34)), "")

    ' write to *_update.csv
    fileNum = FreeFile
    filePath = Replace(filePath, ".csv", "_update.csv", , , vbTextCompare)
    Open filePath For Output As #fileNum
    Print #fileNum, fileContent
    Close #fileNum

    MsgBox "Replacement completed in " & filePath, vbInformation
End Sub

Microsoft documentation:

String function

FreeFile function

Replace function