CODEWITHSUNDEEP
excelcsv
John
John

Reputation: 227

CSV file from excel with fields inside double quotes

I want to create a CSV file from excel where all data in fields will be inside double quotes. I found this code from a similar questions and it almost works...

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

It lets me save the selected fields in a CSV and the outcome is something like this:

"A1","B1","C3"
"A2","B2","C3"

What i need it also to do is put a comma after the last field too like this:

"A1","B1","C3",
"A2","B2","C3",

I have no idea about this kind of code and could use some help.

Also if this works how do i save a macro in an excel sheet so i can use it any time i need it.

Thanks John

Upvotes: 2

Views: 3911

Answers (1)

David Zemens
David Zemens

Reputation: 53623

I think this bit of code is removing the list separator/comma from each line. Try removing these three lines and see if that fixes it.

While Right(CurrTextStr, 1) = ListSep
  CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend

Place the code module containing this script in your "PERSONAL" Excel workbook so that it will always be available to you, no matter what file is open.

Otherwise, just place this code module in the required workbooks' code module and make sure to save as XLSM.

Upvotes: 1

Related Questions