Remove Trailing Comma when Exporting CSV

[bonescoster]

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:

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.

 

[Joe4]

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.