Excel – Saving CSV with Quotes

Whenever I export a file with Excel to CSV, it does not add quotes. Wouldn’t you know it that I’m using an application that requires quotes on input files for CSV. I found the following routine for doing this. You open up the VB editor in Excel, start a new class and add this to it. Then select the TAB you want to export and click RUN. It does the job quite well.

Sub CSVFile()

Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename(“”, “CSV File (*.csv), *.csv”)

If FName False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = “”
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & “””” & CurrCell.Value & “””” & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) – 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

Advertisements

3 responses to “Excel – Saving CSV with Quotes

  1. Good post! Worked for me with a few tweaks to the “” characters. Also suggest saving this in a module and assigning a hotkey for future use.

  2. Besides the quotes, also needed to fix the -1 in Len(CurrTextStr) – 1)
    And “If FName False Then” should actually be “If FName [lessthan sign][greaterthan sign] False Then”
    GG, WordPress. 🙂
    Also, 2 caveats with the logic itself:
    1) Blank cells show as ,””, rather than ,, (May or may not be what you want.)
    2) This does NOT properly escape embedded quotes within fields. Example: ,We said “Hi” today,
    becomes: ,”We said “Hi” today”,
    instead of: ,”We said “”Hi”” today”,

  3. To fix #2 above, I added this line to the beginning of the innermost For loop:
    CurrCell.Value = Replace(CurrCell.Value, “”””, “”””””)
    Of course WordPress is going to mangle those quotes^

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s