vba - Excel trailing comma bug in csv files

Question

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 inputted 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.

Answer 1

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