Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I export a CSV file where the values and fields are both enclosed in double quotes (") from Excel 2004 (Mac)?


"John Doe","555-5009"
share|improve this question
Is your data already enclosed in double quotes? Or is that part of what you need help with? – Excellll Oct 13 '11 at 15:39
I need help enclosing the values in " without having to manually type it in. – Anriëtte Myburgh Oct 13 '11 at 22:19
Does your spreadsheet include formulas, or is all the data values only? – Excellll Oct 13 '11 at 23:44
did you get solution for this ? – Atif Mohammed Ameenuddin Jul 21 '12 at 21:36

It only took me close to two years to come back and edit this. Here's a workaround that will hopefully wrap all your data in quotation marks and save as CSV. You will need to add the Microsoft Scripting Runtime reference to your VBA project (Under Tools > References...).

Sub addquotes()
Dim s As Worksheet
Dim tmpR As Range
Dim tmpArray() As Variant, out As String
Dim fso As FileSystemObject, ts As TextStream

Application.DisplayAlerts = False
Set s = ActiveSheet
Set tmpR = s.UsedRange

'Set format of all cells as Text.
tmpR.NumberFormat = "@"
tmpArray = tmpR.Value

For i = LBound(tmpArray, 1) To UBound(tmpArray, 1)
    For j = LBound(tmpArray, 2) To UBound(tmpArray, 2)
        If j = LBound(tmpArray, 2) Then out = out & Chr(34)
        out = out & tmpArray(i, j) & Chr(34)
        If j <> UBound(tmpArray, 2) Then
            out = out & "," & Chr(34)
            out = out & vbLf
        End If
    Next j
Next i
Set fso = New FileSystemObject
Set ts = fso.OpenTextFile("C:\Users\Editor 3\Desktop\yourcsv.csv", ForWriting, True)
ts.Write out
Application.DisplayAlerts = True
End Sub

Previous failed attempt (left only for reference; please don't use this):
Here's a macro that will open a new workbook and enter all the data from your sheet with each value enclosed in quotation marks. You can then save this new workbook as a .CSV without disturbing your original workbook. This will work even if your original data included formulas.

Sub addquotes()

Dim s As Worksheet, newS As Worksheet
Dim tmpR As Range
Dim tmpArray() As Variant

Set s = ActiveSheet
Set tmpR = s.UsedRange

'Loads all data from sheet into array.
tmpArray = tmpR.Value

'Adds quotes around members of array if not blank.
For i = 1 To UBound(tmpArray, 1)
    For j = 1 To UBound(tmpArray, 2)
        If tmpArray(i, j) <> "" Then
            tmpArray(i, j) = """" & tmpArray(i, j) & """"
        End If
    Next j
Next i

'Open new workbook and enter transformed data.
Set NewBook = Workbooks.Add
Set newS = NewBook.Sheets(1)
newS.Range("A1").Resize(UBound(tmpArray, 1), UBound(tmpArray, 2)) = tmpArray

End Sub
share|improve this answer
Nice start, but it doesn't work. You get quotes that show up as part of the cell data, rather than just enclosing it. I.e., something like """ID""","""Section""","""Sub-section""","""Title""","""Template""","""Owner/Au‌​thor""","""URL Fragment""","""URL""","""Initial Content From""","""Status""","""Body""","""Home""","""lang""","""Published""","""User"""‌​. This is not what the original poster was asking for. – iconoclast Aug 22 '12 at 13:41
@iconoclast Now that I've looked at the CSV in a text editor, I see you're right. Looks like an edit is in order. Thanks. – Excellll Aug 22 '12 at 17:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .