saving in CSV with a nonstandard list separator - possible bug or 'bydesign' quirkness ?

[fabio.rossetti]

I've been trying to save an excel workbook in CSV format, using a
semicolon instead of a colon as field separator.
The MSDN docs, more or less, state that via VBA/Ole Automation a comma
is always used, unless the Local parameter of the Workbook.SaveAs
method is set to True. In this case the Control Panel/Internation
Settings for the list separator should be used. I've built a very
simple VB6 sample application, using Excel 2003 (11.0 version type
library). All I do is open excel, add a workbook (creating a Workbook
object), write into a couple of cells, and then try to save the file
in CSV format (using the Workbook.SaveAs method). Then I duly
WorkBook.Close the Workbook and Excel.Quit MS Excel. I've been
getting always commas, the locale setting seemed to be always ignored.
A quick google search resulted about the problem confirmed that a lot
of other people had the same situation, but there was no solution. I
seem to have found, however, that the WorkBook.Close method,
unexplainedly, seems to perform a second Save on the file, this time
ignoring the locale settings and overwriting the first .SaveAs, which
is performed using the correct locale settings... Using Workbook.Close
false or avoiding a .Close altogether seems to bypass problems. When
Workbook.Close is not used, Excel.Quit forces another save, but this
time the locale settings are retained !!


Private Sub Command1_Click()

Dim Excel As Object

Set Excel = CreateObject("Excel.application")
Dim Workbook As Excel.Workbook
Set Workbook = Excel.Workbooks.Add

Workbook.Activate
Excel.Visible = True
Dim Worksheet As Object
Set Worksheet = Workbook.ActiveSheet


Worksheet.Rows.Cells(1, 1) = 3.14159265
Worksheet.Rows.Cells(1, 2) = 2.71828183

'xlCSV = 6
'Local := True forces excel to use control panel settings for the list
separator
Workbook.SaveAs "C:\testcsv.txt", 6, Local:=True

'if this line is commented, the file is saved a second time, WITHOUT
control panel settings
'avoiding to close explicitly the workbook and quitting excel directly
forces another save
'but this time control panel settings are used!
Workbook.Close

Excel.Quit
End Sub

 

[Joel]

You are asking a silly question. CSV means Commar - seperated - Values. If
the data was semiicolon seperated then it would be SSV. Anyway, here is code
that will do what you want.


Sub WriteDelimited()
Const MyPath = "C:\temp\"
Const WriteFileName = "text.csv"

Const Delimiter = ","
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

Set fswrite = CreateObject("Scripting.FileSystemObject")
'open files
WritePathName = MyPath + WriteFileName
fswrite.CreateTextFile WritePathName
Set fwrite = fswrite.GetFile(WritePathName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

For RowCount = 1 To LastRow
LastCol = Cells(RowCount, Columns.Count).End(xlToLeft).Column
For ColCount = 1 To LastCol
If ColCount = 1 Then
OutputLine = "," & Cells(RowCount, ColCount)
Else
OutputLine = OutputLine & Delimiter & Cells(RowCount, ColCount)
End If
Next ColCount
tswrite.writeline OutputLine
Next RowCount

tswrite.Close

Exit Sub
End Sub

 

[fabio.rossetti]

You are asking a silly question. CSV means Commar - seperated - Values. If
the data was semiicolon seperated then it would be SSV. Anyway, here is code
that will do what you want.

Well, then there wouldn't be a need to have the list separator setting
in Control panel or the local parameter in the SaveAs method, then,
wouldn't it.. Semicolons are a necessity especial for countries
where the comma is used as a decimal separator. I thought about
avoiding excel altogether and write directly to a text file, but I had
already a routine that originally outputted in xls, so..