Excel Comma Delimited CSV with Quotes

Option Explicit

Sub MakeFile()

Dim rng As Range
Dim NumR As Long
Dim NumC As Long
Dim CountR As Long
Dim CountC As Long
Dim Delim As String
Dim Qual As String
Dim Leading As Boolean
Dim Trailing As Boolean
Dim TheFile As String
Dim fso As Object
Dim ts As Object
Dim LineStr As String

UserForm1.Show

' if user cancels form, quit sub
If UserForm1.cmdCancel.Cancel Then
Unload UserForm1
MsgBox "Operation Canceled by user"
Exit Sub
End If

' get variable setting from UserForm
With UserForm1
Set rng = Range(.reRange)
NumR = rng.Rows.Count
NumC = rng.Columns.Count
Delim = IIf(.obCharacter, .tbDelimiter, Chr(9)) 'Chr(9) = tab
Qual = .tbTextQualifier
Leading = .ckLeadingDelimiter
Trailing = .ckTrailingDelimiter
TheFile = .tbCreateFile
End With
Unload UserForm1

' create the text file
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile(TheFile, True)

' loop through range to build text file records
For CountR = 1 To NumR
LineStr = IIf(Leading, """", "")
For CountC = 1 To NumC
If Not IsNumeric(rng.Cells(CountR, CountC)) And Not IsDate(rng.Cells(CountR, CountC)) Then
LineStr = LineStr & Qual & rng.Cells(CountR, CountC) & Qual
Else
LineStr = LineStr & rng.Cells(CountR, CountC)
End If
LineStr = LineStr & IIf(CountC < NumC, Delim, "")
Next
LineStr = LineStr & IIf(Trailing, """", "")
ts.WriteLine LineStr
Next

' release memory from object variables
ts.Close
Set ts = Nothing
Set fso = Nothing

MsgBox "Done. File written to " & TheFile

End Sub