CODEWITHSUNDEEP
vbacsvole
Mike
Mike

Reputation: 805

Remove final comma in CSV lines generated by VBA/OLE

I am using VBA to control a database frontend with OLE Automation. The output from the database queries comes in a tabular format and I can use the application's OLE methods to read individual cells. My goal is to automate the process of converting this output to CSV. My approach has been to loop through each cell and output to CSV using VBA's WRITE function, but this method leaves me with an extra unwanted comma on each line. Please help me to remove this extra comma or to write the line without it to begin with. Thanks in advance for your time.

The first loop writes the column headers, the second writes the table's data. Both loops produce a final unwanted comma.

'4. Write headers and table content to CSV File
     Open pathOutput For Output As #1
         For h = 1 To results.columnCount
             Write #1, Cells(1, h);
         Next h
         Write #1,

         For i = 1 To results.rowCount
             For j = 1 To (results.columncount)
                 Write #1, results.cell(i, j);
             Next j
             Write #1,
         Next i

     Close #1

Upvotes: 1

Views: 932

Answers (2)

Daniel
Daniel

Reputation: 13122

This should solve the problem:

'4. Write headers and table content to CSV File
    Open pathOutput For Output As #1
        For h = 1 To Results.columncount
            'Add a comma unless it's the last column
            If h <> Results.columncount Then
                Write #1, Cells(1, h);
            Else
                Write #1, Cells(1, h)
            End If
        Next h
        Write #1,

        For i = 1 To Results.RowCount
            For j = 1 To (Results.columncount)
                'Add a comma unless it's the last column
                If j <> Results.columncount Then
                    Write #1, Cells(1, j);
                Else
                    Write #1, Cells(1, j)
                End If
            Next j
            Write #1,
        Next i

    Close #1

I haven't been able to find documentation on this, but the semicolon such as found here

Write #1, Cells(1, j); causes Write to end the line with a comma instead of the default Crlf (related question).

Upvotes: 3

DWright
DWright

Reputation: 9500

I think the final Write #1, statements may be the culprit. What happens when you remove them?

Upvotes: 1

Related Questions