ExcelBanter - Convert excel to different file format.

Someone must be able to come up with a neater solution that this ... but I'll
post it anyway.

With a reference set to 'Microsoft Scripting Runtime', the following will
save the selected are as 'c:\myfile.csv' in the format you want.

There is surely a better way though!

Regards,

Chris.

Public Sub CSVQuote()

Dim fso As New FileSystemObject
Dim fsoTextStream As TextStream
Dim ranRow As Excel.Range
Dim ranCell As Excel.Range

Set fsoTextStream = fso.CreateTextFile("c:\myfile.csv", True)

For Each ranRow In Selection.Rows()

For Each ranCell In ranRow.Cells

If IsNumeric(ranCell) Or IsDate(ranCell) Then
fsoTextStream.Write ranCell
ElseIf IsEmpty(ranCell) Then
'Do nothing
Else
fsoTextStream.Write """" & ranCell & """"
End If

If ranCell.Column < ranRow.Column + ranRow.Columns.Count - 1 Then

fsoTextStream.Write ","

End If

Next ranCell

fsoTextStream.Write vbCrLf

Next ranRow

End Sub

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master

"knothead" wrote:

How do I save the excel file with both comma AND quotation delimited fields.
i am using Office 2003 SP1.