Saving as csv with quotation marks around values

Topic

New Reply

This is the current part content of a csv file when opened in notepad:
Invoice_Date,Invoice_Net
20110518,4662.73
20110518,4662.73
20110518,4662.73
20110518,4662.73
20110518,405.07
20110518,565.14

but I want the content to be included with quotation marks such as this:
“Invoice_Date”,”Invoice_Net”
“20110518”,”4662.73″
“20110518”,”4662.73″
“20110518”,”4662.73″
“20110518”,”4662.73″
“20110518”,”405.07″
“20110518”,”565.14″

when i run a quick code to include the quotation marks in an excel app then the results look like this:
“””Invoice_Date”””,”””Invoice_Net”””
“””20110518″””,”””4662.73″””
“””20110518″””,”””4662.73″””
“””20110518″””,”””4662.73″””
“””20110518″””,”””4662.73″””
“””20110518″””,”””405.07″””
“””20110518″””,”””565.14″””

Is there a solution as to how to code the excel app such that the resultant csv file uses only 1 set of quotation marks around the text?

The file itself is approx 30 columns wide and typically 50 rows deep..but , you get the gist.

Thanks
Alan

Reply | Quote

Replies

Just re-read that post and, the text/cells in Excel window have just the single quotation marks around them but then have 3 sets when opened in notepad.

Reply | Quote

How about this?

Steve

Code:

Option Explicit
Sub ExportCSV()
  On Error GoTo ErrHandler
  Dim sDelimiter As String
  Dim sQualifier As String
  Dim vRng
  Dim sFilename As String
  Dim i As Long
  Dim j As Long
  Dim sTemp As String
  
  sDelimiter = ","
  sQualifier = Chr(34) 'DblQuote
  vRng = ActiveSheet.UsedRange.Value
  sFilename = InputBox( _
  prompt:="Enter a filename for saving Text File", _
  Default:="c:test.csv")
  If Trim(sFilename) = "" Then
    MsgBox "No file listed"
    GoTo ExitHandler
  End If
  Open sFilename For Output As #1
  sTemp = ""
  For i = 1 To UBound(vRng, 1)
    sTemp = ""
    For j = 1 To UBound(vRng, 2)
      sTemp = sTemp & sQualifier & vRng(i, j) & _
        sQualifier & sDelimiter
    Next j
    Print #1, Left(sTemp, Len(sTemp) - Len(sDelimiter))
  Next i
ExitHandler:
  Close #1
  Exit Sub
ErrHandler:
  MsgBox Err.Number & Err.Description
  Resume ExitHandler
End Sub

Reply | Quote

That’s great Steve.
I should have realised creating the text file in code was the solution.
Many thanks for that.

Reply | Quote

You are very welcome. The CSV converter in XL is not customizable and it is very limited. The code is relatively straightforward, but if you have any questions on it let us know.

Steve

Reply | Quote