VBA to export to a csv file with extra commas

[bkennedy2004]

Looking for VB code to in excel to export an unknown amount of rows of data and appending extra commas to it. For example, in 2 excel rows:
<TABLE style="WIDTH: 268pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=358><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=103>MOVESTOCK</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl63 width=75 align=right>11/2/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=60>@MD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>In-Service</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=56 align=right>7352</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 268pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=358><COLGROUP><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; HEIGHT: 15pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=20 width=103>MOVESTOCK</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=75 align=right>11/2/2011</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 45pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=60>@MD</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=64>In-Service</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 42pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=56 align=right>7353</TD></TR></TBODY></TABLE>
if I have a button, lets say export to a csv file, the file shpould look like this:
MOVESTOCK,11/2/2011,@MD,In-Service,7352,,,,
MOVESTOCK,11/2/2011,@MD,In-Service,7353,,,,

I need the extra comma's inserted when the export happens.
Any Help on this would be greatly appreciated.

 

[John_w]

Here's one way - edit the code where shown to change the folder path and file name of the .csv file:

Sub Create_CSV()

    Dim lastRow As Long
    Dim r As Long
    
    lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

    Open "C:\path\to your\output.csv" For Output As #1   'CHANGE THIS
    For r = 1 To lastRow
        Print #1, Join(Array(Cells(r, 1).Value, Cells(r, 2).Value, Cells(r, 3).Value, Cells(r, 4).Value, Cells(r, 5).Value), ",") & ",,,,"
    Next
    Close #1

End Sub

 

[bkennedy2004]

It almost works perfect, one thing I cannot get rid of, it adds a white space after the comma when it prints to the csv file even though there is not a white space in the cell, I been running around on this one, any ideas?