Posted to microsoft.public.excel.misc
|
|
convert xls to csv with quotes for alpha fields
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
|