Saving Excel File as Comma-Quote Delimited

[acollins]

Hi Everyone. I have a tab-delimited file that I open with Excel, make a few tweaks to, then save as a comma-delimited file. However, the export file has to use quotes as a text qualifier as well, and I can't seem to find an option to add the quotes automatically when saving as a CSV file. Does anyone know how to achieve this. I must admit it has been many years since I have used Lotus 1-2-3, but I seem to remember it being quite easy to do in that program.

Since quotes are used quite commonly as a text qualifier in a delimited file, I find it hard to believe that Excel would not support this feature, but I can't find it if it does.

I don't have to do this that often, but often enough that I would really like to find a solution that doesn't involve me opening the CSV file and manually adding the quotes where necessary.

Any help would be much appreciated.

Alan

 

[Andrew Poulsom]

See if you can adapt this:

Sub Test()
'   Change to suit
    Const FileName As String = "P:\TEMP\MrExcel\TextFiles\MyFile.csv"
    Dim FileNo As Integer
    Dim Rng As Range
    Dim x As Long
    Dim y As Integer
    Dim Txt As String
    FileNo = FreeFile
    Open FileName For Output As #FileNo
    Set Rng = Worksheets("Sheet1").Range("A1").CurrentRegion
    For x = 1 To Rng.Rows.Count
        Txt = ""
        For y = 1 To Rng.Columns.Count
            If Txt = "" Then
                Txt = """" & Rng.Cells(x, y) & """"
            Else
                Txt = Txt & "," & """" & Rng.Cells(x, y) & """"
            End If
        Next y
    Print #FileNo, Txt
    Next x
    Close #FileNo
End Sub