convert xls to csv with quotes for alpha fields

What do you use to convert an XLS file to to a CSV text file, that contains
double quotes around every alphnumeric field, but NO double quotes around
numeric fields?

Thank you.

I'd use a macro.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim myRng As Range
Dim myRow As Range
Dim myCell As Range
Dim fNum As Long
Dim myRec As String
Dim myVal As Variant

Set wks = Worksheets("sheet1")
With wks
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))

fNum = FreeFile
Open "C:\output.txt" For Output As fNum

For Each myRow In myRng.Rows
myRec = ""
For Each myCell In .Range(.Cells(myRow.Row, "A"), _
.Cells(myRow.Row, .Columns.Count).End(xlToLeft)).Cells
myVal = myCell.Value
If Application.IsNumber(myCell.Value) Then
'don't add the double quotes
Else
'double up any embeded double quotes
myVal = Application.Substitute(myVal, Chr(34), _
Chr(34) & Chr(34))
myVal = Chr(34) & myVal & Chr(34)
End If
myRec = myRec & "," & myVal
Next myCell
Print #fNum, Mid(myRec, 2)
Next myRow

Close fNum

End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

majobojrod wrote:
>
> What do you use to convert an XLS file to to a CSV text file, that contains
> double quotes around every alphnumeric field, but NO double quotes around
> numeric fields?
>
> Thank you.

--

Dave Peterson

Dave,

Perfect! Thank you so much.

John Rodrigues

"Dave Peterson" wrote:

> I'd use a macro.
>
> Option Explicit
> Sub testme()
>
> Dim wks As Worksheet
> Dim myRng As Range
> Dim myRow As Range
> Dim myCell As Range
> Dim fNum As Long
> Dim myRec As String
> Dim myVal As Variant
>
> Set wks = Worksheets("sheet1")
> With wks
> Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
>
> fNum = FreeFile
> Open "C:\output.txt" For Output As fNum
>
> For Each myRow In myRng.Rows
> myRec = ""
> For Each myCell In .Range(.Cells(myRow.Row, "A"), _
> .Cells(myRow.Row, .Columns.Count).End(xlToLeft)).Cells
> myVal = myCell.Value
> If Application.IsNumber(myCell.Value) Then
> 'don't add the double quotes
> Else
> 'double up any embeded double quotes
> myVal = Application.Substitute(myVal, Chr(34), _
> Chr(34) & Chr(34))
> myVal = Chr(34) & myVal & Chr(34)
> End If
> myRec = myRec & "," & myVal
> Next myCell
> Print #fNum, Mid(myRec, 2)
> Next myRow
>
> Close fNum
>
> End With
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> majobojrod wrote:
> >
> > What do you use to convert an XLS file to to a CSV text file, that contains
> > double quotes around every alphnumeric field, but NO double quotes around
> > numeric fields?
> >
> > Thank you.
>
> --
>
> Dave Peterson
>