Articles in categories

Now on Amazon

Stieven's Corner

Export Excel to csv with quotes

I needed to export an Excel file to a csv file with all columns surrounded with double quotes ("). Excel doesn't have options for exporting this way. 

A way to achive this is to use a macro in Excel.
Start with your Excel file and press ALT-F11 to open the VB Editor. On the Insert Menu, select Module. Paste the code below. Go back to your Excel file and on the View menu in excel 2010 you should see the Macros button, if not you need to customize your ribbon and add it. Then run the CSVFile macro and it will prompt you for file name and you are done!

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")
 'Hardcoded value here for separator, change to Excel default by using the commented code:
ListSep = "," 'Application.International(xlListSeparator)
   If Selection.Cells.Count > 1 Then
   Set SrcRg = Selection
   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
   If (CurrCell.Value = Null Or Len(CurrCell.Value) < 1) Then
   'Accomodate for NULL values, replaces with ""
   CurrTextStr = CurrTextStr & """""" & ListSep
   'Replace double quote with 2 double quotes for values:
   CurrTextStr = CurrTextStr & """" & Replace(CurrCell.Value, """", """""") & """" & ListSep
   End If
 While Right(CurrTextStr, 1) = ListSep
   CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
 Print #1, CurrTextStr
 Close #1
 End Sub