Remove Trailing Comma when Exporting CSV

bonescoster

New Member
Joined
Mar 16, 2015
Messages
18
Good Day Excel Masters,

I have a bit of code I could use your expert opinions on fixing. I have a Macro created to export one sheet of a workbook as a CSV file. It is used as an upload, but the site it is uploaded to is crazy picky and does not like the comma at the end of row 1.

The first row of the excel sheet has data in columns A:E but each additional row has data in columns A:F. Therefore there is a trailing comma at the end of the first row when looking at the exported CSV in notepad.

Is there a way to have the macro remove the trailing comma when saving the CSV?

Here is the code I have in place now:
VBA Code:
Sub Export()

'Define Destination File Path
    ExpDir = "\Uploads\" & Format(Range("WDate").Value, "yyyymm") & " Upload.csv"

'Copy Sheet to New Book
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlManual
        Sheets("ACH Upload").Select
        Sheets("ACH Upload").Copy
      
'Select All, Remove Formulas, Make Text
    Cells.Select
    Selection.NumberFormat = "@"
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    'Save New Book
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & ExpDir, FileFormat:=xlCSV, CreateBackup:=False
        ActiveWindow.Close
    
    'Display Confirmation MSG Box
        MsgBox "Data has been exported to: " & ExpDir
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    Application.Calculation = xlAutomatic
      
End Sub

Thank you in advance for your help.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,817
Office Version
  1. 365
Platform
  1. Windows
Excel does not like it if each row does not end in the same place. It will make them all end at the same place by filling in null values, causing that extra comma.

Years ago, I used to work with a system that required a header and trailer record on all text files being imported, and they were always a different length than the body of the data.
What I used to do was export each part separately (export the header, then export the data, then export the trailer), and use a batch file script to then sew those pieces together in one file.

If you do a Google search, you could probably find batch file (or VBScript code, if you prefer), for combining data files together.

The other option is to use VBA code that writes each line out to the text file individually (right from Excel VBA). I don't use that method very much, so I am not too proficient at it, but I have seen others use it many times on this site. Once again, you may be able to find some via a Google search.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,784
Messages
5,542,500
Members
410,559
Latest member
jordansmith6532
Top