Missing commas half way down csv

[KBD]

Hi all,

I have a problem which has me a bit stumped..

I have written a macro in excel 2010 which seems to work fine but when I use it in excel 2003 it all works except the csv produced is not right. The macro writes the data into a csv which needs to have a comma at the end of each row.
eg. 3,4,5,
3,4,5,
but half way down when running this in excel 2003 it does this..
3,4,5,
3,4,5,
3,4,5
3,4,5

I use this code to write to the csv..
ActiveWorkbook.SaveAs FileName:=fullFile, _
FileFormat:=xlCSV, CreateBackup:=False, Local:=True
wBook.Close savechanges:=False
I have tried changing the file format to xlCSVMSDOS and xlCSVWindows but I still have the same problem.

Any help would be greatly appreciated.

Many thanks, K

 

[Cindy Ellis]

This is mostly a guess, but it seems likely that there is a space character in the final column of the rows that have the trailing comma (e.g. Column D). Normally, a csv file will NOT have a trailing comma. One way to test this is to put a formula in Column E:
=LEN(D1)
My guess is that the value in Column E will be 1 for the first rows (that have a trailing zero) and 0 for the rows without a trailing zero in the csv file.
Hope that helps,

 

[KBD]

Hi Cindy,

Thank you for getting back to me. I have tried to write a space into the final column as I need the trailing comma, I do the same on each line (after the header string):

ActiveSheet.Range("L" & NextRow).Value = ""

It recognises the space for the first 15 lines.. This works fine in 2010 (giving commas all the way down the file) but not 2003, I have seen a thread some where else that suggests it is a bug!? Do you know if there is a way to write the space/or get the trailing comma other than this way?

Many thanks, K

 

[KBD]

He he,
Writing the post has made me realise the error =" " works, I was confused by the fact it worked fine in 2010 with ="" ??
thanks Cindy.
K