Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Saving as csv with quotation marks around values

    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

  2. #2
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    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.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    ase001 (2011-05-24)

  5. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts
    That's great Steve.
    I should have realised creating the text file in code was the solution.
    Many thanks for that.

  6. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •