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