Excel trailing comma bug in csv files

I have an XLS file creates a CSV file with a macro on Excel 2003, I have 40+ columns and the last 3 ones are optional, therefore there are a lot of empty values on the XLS, when I run the exporting subroutine it wont put a trailing comma on all of the rows, why ? Because : http://support.microsoft.com/kb/77295 -.-

In Microsoft Office Excel, if you save a file in the text or in the CSV (comma separated value) format, Excel puts tabs or commas between each column of the worksheet. However, certain text files may be saved with a different number of tabs or commas in 16-row blocks.

here is their suggested work around:

To make sure that Excel saves tab or comma delimiters for all empty columns, verify that the last column in the file contains some data in at least every 16 rows throughout the file. If the blocks of rows do not contain data, add spaces or other characters in every 16 rows to the cells in the last column, or reorder the columns in the worksheet so that the last column on the worksheet always contains information.

-.- way to go microsoft ! -.-

Ok so my main issue is that the generated file will be parsed by another program out of my scope which needs that specific format as it is right now I'm adding a blank every 16 row if the field is empty, this seems to do it but Data Processing Deparment is complaining about that white space... can you believe them!?

Anyway I also tried to add a flag and remove it with the find function, but ofc when you save the file it will take away the delimiters again...

thanks for reading my history ;p

Any suggestions ?

Edit: Unfortunately using Excel is must, the data is manually inputte开发者_运维技巧d through the excel sheet by different users, the vba codes does a lot more like template generation etc.. this is the only issue that I'm having and it is not feasible to change the whole process.

Manual example;

Dim r As Range: Set r = Range("A1:C10") '// or ActiveSheet.UsedRange for everything
Dim buffer As String, delimiter As String, c As Range
Dim i As Long

For Each c In r
    If (i Mod r.Columns.Count) = 0 Then
        If (Len(buffer)) Then delimiter = vbCrLf
    Else
        delimiter = ","
    End If
    buffer = buffer & delimiter & """" & CStr(c.Value) & """" '//or c.text to preserve formatting
    i = (i + 1)
Next

Open "c:\xxx.csv" For Output As #1
    Print #1, buffer
Close #1

If you don't have commas in your data, it would be very easy to write a text reader that goes through and counts the number of columns in a line and just add commas to the end if there aren't enough. It's not ideal, but it's probably easier than writing a custom Excel to CSV converter.

Simplest way:

ActiveWorkbook.SaveAs "MyFileNameAndDir.csv", xlCSV, Local:=True

Suggestion 1) Stop using Excel to generate a CSV file.

What's your data source? Where does Excel get the data from? Perhaps you can do something with the original data source to generate a CSV instead of using Excel as the middleman. If you like really big hammers, Biztalk is the ultimate MS tool for data input/output manipulation. Few can pull that one out of their toolbox, but throwing together a custom c# program to generate a CSV file can be done in a couple hours.

In short: If at all possible, use a better tool!