export to csv file (comma problem)

[Guest]

Hi
I am using the following code to export data in excel file (muliple columns)
to csv file
sub Export_toCSV()
ThisWorkbook.Sheets(1).Range("actlist").Select ' actlist is a named rage (3
columns)
Selection.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
True, Transpose:=False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path, FileFormat:=xlCSVMSDOS,
CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
ThisBook.Activate
ThisBook.Worksheets(1).Range("A1").Activate
End sub()

Here is what I am getting.
22900002,46002759,
22900003,46002776,22525548
22900040,46002806,
22995012,46002873,25658415
22995014,46002877,
22995103,46002982,
22995111,,
22995117,,
22995127
22995128
22995153

What I DON'T understand is why there are two commas put in for 22995111 &
22995127. (if the 3rd column does not have data, 1 comma put in for the same
numbers)
Also, why the comma is at the end for some rows but not for others. I need
consistency. if the comma is put always, I need the comma even for the last
column.
OR the comma should not be there if there are no data on the next column.

22995012,46002873,25658415
22995014,46002877,

Note: when I write to the file line by line with print statement its OK but
the performance is very slow.

Any help appreciated.

Thanks in advance

 

[Jim Rech]

I think you've encountered this issue:

http://support.microsoft.com/kb/77295/en-us

--
Jim
| Hi
| I am using the following code to export data in excel file (muliple
columns)
| to csv file
| sub Export_toCSV()
| ThisWorkbook.Sheets(1).Range("actlist").Select ' actlist is a named rage
(3
| columns)
| Selection.Copy
| Workbooks.Add
| Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
| True, Transpose:=False
| Application.DisplayAlerts = False
| ActiveWorkbook.SaveAs Filename:=path, FileFormat:=xlCSVMSDOS,
| CreateBackup:=False
| ActiveWorkbook.Close
| Application.DisplayAlerts = True
| ThisBook.Activate
| ThisBook.Worksheets(1).Range("A1").Activate
| End sub()
|
| Here is what I am getting.
| 22900002,46002759,
| 22900003,46002776,22525548
| 22900040,46002806,
| 22995012,46002873,25658415
| 22995014,46002877,
| 22995103,46002982,
| 22995111,,
| 22995117,,
| 22995127
| 22995128
| 22995153
|
| What I DON'T understand is why there are two commas put in for 22995111 &
| 22995127. (if the 3rd column does not have data, 1 comma put in for the
same
| numbers)
| Also, why the comma is at the end for some rows but not for others. I need
| consistency. if the comma is put always, I need the comma even for the
last
| column.
| OR the comma should not be there if there are no data on the next column.
|
| 22995012,46002873,25658415
| 22995014,46002877,
|
| Note: when I write to the file line by line with print statement its OK
but
| the performance is very slow.
|
| Any help appreciated.
|
| Thanks in advance
|