Using CSV and quotes
 

Hi there.

I have some applications that accept data as a CSV file (where ALL fields
must be quoted).

In Excel when I save as CSV it does not put quotes around text fields that
do not contain spaces etc.

Is there anyway to make Excel automatically quote all text fields?

Thanks
Neil

Using CSV and quotes
 

You have to use a macro. Here's one:

''Outputs the selection if more than one cell is selected, else entire sheet
Sub OutputActiveSheetQuotesAroundAll()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename("", "CSV File (*.csv), *.csv")
If FName < False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = ""
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & """" & CurrCell.Value & """" &
ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) - 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub


--
Jim
"Neil" wrote in message
...
| Hi there.
|
| I have some applications that accept data as a CSV file (where ALL fields
| must be quoted).
|
| In Excel when I save as CSV it does not put quotes around text fields that
| do not contain spaces etc.
|
| Is there anyway to make Excel automatically quote all text fields?
|
| Thanks
| Neil
|
|

Using CSV and quotes
 

Enter this small macro:

Sub Macro1()
Dim r As Range
For Each r In Selection
If Application.IsText(r.Value) Then
r.Value = Chr(34) & r.Value & Chr(34)
End If
Next
End Sub

Select the cells you want to modify and run the macro. If you wish to use
the apostophe rather than the double quote symbol, then use Chr(39) in place
of 34
--
Gary's Student


"Neil" wrote:

Hi there.

I have some applications that accept data as a CSV file (where ALL fields
must be quoted).

In Excel when I save as CSV it does not put quotes around text fields that
do not contain spaces etc.

Is there anyway to make Excel automatically quote all text fields?

Thanks
Neil

Using CSV and quotes
 

Thanks guys, I will give that a go.
Neil

"Neil" wrote in message
...
Hi there.

I have some applications that accept data as a CSV file (where ALL fields
must be quoted).

In Excel when I save as CSV it does not put quotes around text fields that
do not contain spaces etc.

Is there anyway to make Excel automatically quote all text fields?

Thanks
Neil

Using CSV and quotes
 

Thanks Jim that works great!

@Gary"s Student - your macro puts quotes around each cell, but then when
saved as CSV it ends up with quotes around the quotes as it thinks the first
quotes are part of the string. Thanks for your time anyway.

Neil


"Neil" wrote in message
...
Hi there.

I have some applications that accept data as a CSV file (where ALL fields
must be quoted).

In Excel when I save as CSV it does not put quotes around text fields that
do not contain spaces etc.

Is there anyway to make Excel automatically quote all text fields?

Thanks
Neil