Advertisement
ryanharne

CSV with comma and quote delimiters in Excel

Feb 12th, 2014
204
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /**
  2. http://support.microsoft.com/kb/291296/en-us
  3. Procedure to export a text file with both comma and quote delimiters in Excel
  4. **/
  5. Sub QuoteCommaExport()
  6.    ' Dimension all variables.
  7.   Dim DestFile As String
  8.    Dim FileNum As Integer
  9.    Dim ColumnCount As Integer
  10.    Dim RowCount As Integer
  11.  
  12.    ' Prompt user for destination file name.
  13.   DestFile = InputBox("Enter the destination filename" _
  14.       & Chr(10) & "(with complete path):", "Quote-Comma Exporter")
  15.  
  16.    ' Obtain next free file handle number.
  17.   FileNum = FreeFile()
  18.  
  19.    ' Turn error checking off.
  20.   On Error Resume Next
  21.  
  22.    ' Attempt to open destination file for output.
  23.   Open DestFile For Output As #FileNum
  24.  
  25.    ' If an error occurs report it and end.
  26.   If Err <> 0 Then
  27.       MsgBox "Cannot open filename " & DestFile
  28.       End
  29.    End If
  30.  
  31.    ' Turn error checking on.
  32.   On Error GoTo 0
  33.  
  34.    ' Loop for each row in selection.
  35.   For RowCount = 1 To Selection.Rows.Count
  36.  
  37.       ' Loop for each column in selection.
  38.      For ColumnCount = 1 To Selection.Columns.Count
  39.  
  40.          ' Write current cell's text to file with quotation marks.
  41.         Print #FileNum, """" & Selection.Cells(RowCount, _
  42.             ColumnCount).Text & """";
  43.  
  44.          ' Check if cell is in last column.
  45.         If ColumnCount = Selection.Columns.Count Then
  46.             ' If so, then write a blank line.
  47.            Print #FileNum,
  48.          Else
  49.             ' Otherwise, write a comma.
  50.            Print #FileNum, ",";
  51.          End If
  52.       ' Start next iteration of ColumnCount loop.
  53.      Next ColumnCount
  54.    ' Start next iteration of RowCount loop.
  55.   Next RowCount
  56.  
  57.    ' Close destination file.
  58.   Close #FileNum
  59. End Sub
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement