Create csv file with double quotes on all fields

[sugrue]

When I create csv files, I need to have all columns wrapped in double-quotes. Currently, Excel only wraps those columns that contain commas in double-quotes. I have access to both Excel 2003 and 2007. Is there a way to configure Excel to wrap all columns and if not, is there some VB magic that I can run to do this?

I tried to search the forum to see if this had already been discussed, but did not find anything.

Thanks,

Jack

[jindon]

try

Sub test()
Dim r As Range, txt As String
With ActiveSheet.UsedRange
    For i = 1 To .Rows.Count
        For Each r In .Rows(i).Cells
            txt = txt & "," & Chr(34) & r.Text & Chr(34)
        Next
        txt = txt & IIf(i <> .Rows.Count, vbCrLf, "")
    Next
End With
Open ThisWorkbook.Path & "\" & ActiveSheet.Name & ".csv" For Output As #1
    Print #1, Mid$(txt, 2)
Close #1
End Sub

[sugrue]

Thanks Jindon for the code. This is working fine for the first row in my spreadsheet. However, for every other row, the resulting csv file starts with a comma. For instance, my original file has 3 rows with 3 columns each:

apple smith 100
pear jones 20
orange jackson 120

After running your code, the resulting csv file has:

"apple","smith","100"
,"pear","jones","20"
,"orange","jackson","120"

The commas that begin rows 2 and 3 are not supposed to be there.

Thanks, Jack

[jindon]

try

Sub test()
Dim r As Range, temp As String, txt As String
With ActiveSheet.UsedRange
    For i = 1 To .Rows.Count
        For Each r In .Rows(i).Cells
            temp = temp & "," & Chr(34) & r.Text & Chr(34)
        Next
        txt = txt & vbCrLf & Mid$(temp,2)
        temp = ""
    Next
End With
Open ThisWorkbook.Path & "\" & ActiveSheet.Name & ".csv" For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1
End Sub

[sugrue]

Thanks Jindon,

That works!!

Jack