Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

VBA CSV Remove multiple commas

Avatar of dnt2009
dnt2009 asked on
Microsoft Excel
28 Comments1 Solution1936 ViewsLast Modified:
Hi,

I've written a macro that converts an excel worksheet into a csv file. The the Excel worksheet, the lines don't have the same number of columns. When the file is converted into csv, the system adds commas at the end of each line to make up of the empty column.
Please see bellow:
1 - The Excel data
2 - The code
3 - Resulting csv file


1 - The Excel data

H      CITCLULL      CSV535A      NEWM      12345      10212      20100705      20100705083748
D      12975812      0      4600000      1.0049                  
T      1      0.00                              


2 - The code

Sub CreateHoldingsCSVFile()
'
' Create CSV File for Holdings
'
    Dim FileReference
    Dim YourInitials
   
    FileReference = InputBox("Please enter Fund Reference (Fund id)")
    If FileReference = "" Then Exit Sub
    YourInitials = InputBox("Please enter Your Initials(ABC)")
    If YourInitials = "" Then Exit Sub
       
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Sheets("Holdings").Select
    Sheets("Holdings").Copy
    ActiveWorkbook.SaveAs Filename:= _
        "C:\HOLDINGS_CSV_" & FileReference & "_" & YourInitials & "_" & Format(Date, "YYYYMMDD") & ".csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Sheets("MACRO").Select
    Range("A1").Select
   
End Sub

3 - Resulting csv file

H,CITCLULL,CSV535A,NEWM,12345,10212,20100705,20100705083431
D,12975812,0,4600000,1.0049,,,
T,1,0.00,,,,,

Thanks for your help.
D