Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 | | |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|