Hi Julian -

The second set of quotes you describe, the ones like “” or \\ // around
your text, are the curly quotes ("smart quotes", they call them!) that
auto-format features in Office put in place of the straight ones "" or
|| || that are typed in, and which CSV files use to contain text.

Are you pasting in the quotes from Word or PowerPoint? I believe both
of these will convert typed quotes to the curly quotes by default,
unless you go to Tools menu AutoCorrect AutoFormat and
AutoFormatAsYouType, then uncheck the appropriate box.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Julian Milano wrote:
XL2000 SP3, 2002, WinXP SP2

I have an application which contains a userform with a textbox. The data in
the textbox is written to a worksheet where the worksheet is saved as a CSV
file. I have been experiencing corruption in the CSV file occaisonally, but
have not been able to find the problem until now and need someone to shed
some light.

The following text has been typed, manually via the keyboard, into the
textbox. This is what is written to the CSV file:

called "magically", but really

The following text has been copied from a Word document, via the cut/paste
facility (CTRL-C & CTRL-V), and pasted into the textbox. This is what is
written to the CSV file:

called "magically", but really

Note two things here. Quotes typed into the textbox will cause the CSV file
to "corrupt itself", while quotes pasted into an textbox will work fine!
Based on this, note the style of the quote characters above, which are only
evident when the font is changed to Tahoma.

For those of you who can't see the formatting above, the first block of text
contains two double-quotes, both are vertical in style, like this: || and
||.
The second block of text contains two double-quotes, both are angled style,
like this: \\ and //.

Why is this happening?

Here's my CSV writing routine (Why am I not using the standard Save-As CSV
in XL - because my clients in some foreign countries have their regional
settings set so that CSV files are not comma delimited, but semi-colon
delimited!!! And when the resulting CSV file is received by the software in
a different regional setting, where commas are the default delimiter, the
CSV file appears corrupt) When I say "appears corrupt", I mean that the
cells pasted to create the original CSV, appear in all the wrong places when
the CSV is received in a different regional setting PC, due to the quotes in
the text in the cells.

Function WriteFile(ws2Save As Worksheet, CSVFilename As String, _
delimiter As String, quotes As Integer) As String

' Dimension variables to be used in this function.
Dim CurFile As String
'Dim wb2Save
Dim CellText As String
Dim RowNum As Integer
Dim ColNum As Integer
Dim FNum As Integer
Dim TotalRows As Double
Dim TotalCols As Double

FNum = FreeFile()
' Open the selected file name for data output.
Open CSVFilename For Output As #FNum
' Store the total number of rows and columns to variables.
TotalCols = ws2Save.UsedRange.Columns.Count
TotalRows = ws2Save.Range("A65535").End(xlUp).Row
' Loop through every cell, from left to right and top to bottom.
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With ws2Save.Cells(RowNum, ColNum)
' CellText = PreProcessText(.Value)
CellText = .Value
End With
' Write the contents to the file.
' With or without quotation marks around the cell information.
Select Case quotes
Case vbYes
CellText = Chr(34) & CellText & Chr(34) & delimiter
Case vbNo
CellText = CellText & delimiter
End Select
Print #FNum, CellText;
' Update the status bar with the progress.
Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
+ ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
' Loop to the next column.
Next ColNum
' Add a linefeed character at the end of each row.
If RowNum < TotalRows Then Print #FNum, ""
' Loop to the next row.
Next RowNum
' Close the .prn file.
Close #FNum
' Reset the status bar.
Application.StatusBar = False
WriteFile = "Exported"
End Function