Extra comma's after exporting to CSV from Excel using as my columns are inconsistent

Hi,

I wrote an macro which creates csv file from excel. I am pasting my Data from A3 and for 1st row & 2nd row, i had hardcoded with string values as shown in macro. But the csv is having extra comma's added in the 1st row eventhough i had added only 3 columns in 1st row. Please help me to remove the extra comma's from 1st row.

Option Explicit
Sub ExportAsCSV()

Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.Range("A3:Z300").Copy

Set TempWB = Application.Workbooks.Add(1)
TempWB.Sheets(1).Range("A1").Value = "TQLOADLINE"
TempWB.Sheets(1).Range("B1").Value = "TQLOADLINE"
TempWB.Sheets(1).Range("D1").Value = "EN"
TempWB.Sheets(1).Range("A2").Value = "TQREFNUM"
TempWB.Sheets(1).Range("B2").Value = "TQCLASSNAME"
TempWB.Sheets(1).Range("C2").Value = "ITEMNUM"
TempWB.Sheets(1).Range("D2").Value = "ITEMQTY"
TempWB.Sheets(1).Range("E2").Value = "LIKEFORLIKE"
TempWB.Sheets(1).Range("F2").Value = "LINETYPE"
TempWB.Sheets(1).Range("G2").Value = "TQTYPE"
TempWB.Sheets(1).Range("H2").Value = "UNITCOST"
TempWB.Sheets(1).Range("I2").Value = "ORDERUNIT"
TempWB.Sheets(1).Range("J2").Value = "DESCRIPTION"
TempWB.Sheets(1).Range("K2").Value = "DESCRIPTION_LONGDESCRIPTION"
TempWB.Sheets(1).Range("L2").Value = "HOLDITEM"
TempWB.Sheets(1).Range("M2").Value = "LOCATION"
TempWB.Sheets(1).Range("N2").Value = "MANSHIPTO"
TempWB.Sheets(1).Range("O2").Value = "REQUESTBY"
TempWB.Sheets(1).Range("P2").Value = "REQUIREDATE"
TempWB.Sheets(1).Range("Q2").Value = "INSPECTION"
TempWB.Sheets(1).Range("R2").Value = "CERTIFICATION"
TempWB.Sheets(1).Range("S2").Value = "TQDOCUMENTATION"
TempWB.Sheets(1).Range("T2").Value = "TQTESTING"
TempWB.Sheets(1).Range("U2").Value = "TQHIREDURATION"
TempWB.Sheets(1).Range("V2").Value = "TQOFFHIREDATE"
TempWB.Sheets(1).Range("W2").Value = "TQONHIREDATE"
TempWB.Sheets(1).Range("X2").Value = "TQVENDOR"
TempWB.Sheets(1).Range("Y2").Value = "COMMODITY"
TempWB.Sheets(1).Range("Z2").Value = "TQGLDEBITACCT"


TempWB.Sheets(1).Range("A3:Z300").PasteSpecial xlPasteValues



MyFileName = CurrentWB.Path & "\" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & Format(Now(), "YYYY-MM-DD") & "UPload" & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close False
Application.DisplayAlerts = True




End Sub


The Output
TQLOADLINE,TQLOADLINE,,EN,,,,,,,,,,,,,,,,,,,,,,

Desired output
TQLOADLINE,TQLOADLINE,,EN

Hi,

I too agree with you. But my columns are not consistent.

Row 1 will have only 4 columns
Row 2 will have 26 ( Column Headers)
From Row 3, the actual data will there...

Thank you. worked perfectly.

Hi,

Yesterday your solution worked perfectly but its adding 2 extra blank lines in the lines. So import of CSV fails in my application with these 2 blank lines. When deleted these lines using notepad works fine. can you please trim the 2 blank lines that were added in the end of the file. When opened in CSV format i can't see any difference but when opened in notepad i can see 2 blank lines were added.