Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving in CSV with a nonstandard list separator - possible bug or 'bydesign' quirkness ?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving in CSV with a nonstandard list separator - possible bug or
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 " wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
saving in CSV with a nonstandard list separator - possible bug or
On 29 Mag, 16:54, Joel wrote:
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.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
list separator | Excel Discussion (Misc queries) | |||
List Separator | Excel Programming | |||
list separator | Excel Programming | |||
Saving a worksheet to csv with different separator | Excel Worksheet Functions | |||
list separator settings | Excel Discussion (Misc queries) |