Category: Microsoft

Export an excel spreadsheet to a quoted CSV

Tutorial on how to Export to a Quoted CSV

This guide is tailored towards Microsoft Office 2007, but with a little intuition it should work on the other distributions.

1. If the developer tab is not shown at the top

  • Click File
  • Options
  • Customize Ribbon
  • Select the Developer checkbox

2. On Developer tab, Code Group, Click Macro Security

3. On the Developer tab, in Code Group click Visual Basic

4. Right click the spreadsheet, Insert, Module

5. Paste the following:

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")
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
  CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" & ","
While Right(CurrTextStr, 1) = ListSep
  CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Print #1, CurrTextStr
Close #1
End Sub

7. Click Run

8. Type name and Select Destination of the File