VBA to export to a csv file with extra commas

bkennedy2004

New Member
Joined
Nov 2, 2011
Messages
2
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.
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,612
Here's one way - edit the code where shown to change the folder path and file name of the .csv file:
Code:
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

New Member
Joined
Nov 2, 2011
Messages
2
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,652
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top