VBA code to save only text with quote marks in a CSV

Hi,

I have a 10 column worksheet with text in the first 9 columns (A to I) and a number in column 10 (J).

I need to save this as a CSV with quote marks around the data in the first 9 columns and without quotes around the number in column 10.

Found the attached macro code on the MS site, however it saves the data in all columns with quote marks. Can someone help with modifying it so it will do what I require?

 Sub QuoteCommaExport()
   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub

Thanks
Alan

Please post the code directly to the forum, not in a txt file attachment.
Use code tags: [CODE] [/CODE]
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Also attach a sample of the data the macro operates on.

• Click Advanced (next to quick post),
• Scroll down until you see "Manage Attachments",
• Click that then select "add files" (top right corner).
• Click "Select Files" find your file, click "open" click "upload"
• Once the upload is completed the file name will appear below the input boxes in this window.
• Click "Done" at bottom right to close the Attachment Manager.
• Click "Submit Reply"

Option Explicit
 Sub QuoteCommaExport()
   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Long
   Dim ColumnCount As Long
   Dim RowCount As Long, _
        LastRow As Long, _
        LastColumn As Long
        
    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    LastColumn = 10
    
   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

   ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To LastRow

      ' Loop for each column in selection.
      For ColumnCount = 1 To LastColumn
        If ColumnCount < LastColumn Then
            ' Write current cell's text to file with quotation marks.
            Print #FileNum, """" & Cells(RowCount, ColumnCount).Text & """";
            Print #FileNum, ",";
        Else
            Print #FileNum, Cells(RowCount, ColumnCount).Text;
            Print #FileNum,
        End If

      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub