Excel remove cells in export to CSV

Question

I have a table in Excel that I have to export to CVS. The table is like:

I have the following code to export to CSV:

Columns("A:D").Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:="C:\x_test.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False
ActiveWindow.Close
Application.DisplayAlerts = True

The output is like:

Book Title,,,
Author,Total Pages,Editor,Year Published
Adam,50,Universal,2018
Section,Pages,,
1,20,,
2,30,,

The problem I have is that it adds additional cells in rows that I don't need. for example, the first line should be like "Book Title" not like "Book Title,,,"

Is there a way to export to CSV with the relevant number of columns properly reflected with comas for each line

I tried merging the cells in Excel, but it did not make any difference.

It doesn't add cells, the cells are there (only they are empty). You may want to go through the csv after it is saved to remove the extraneous commas. — cybernetic.nomad

Selrac Selrac · Accepted Answer · 2018-03-22T16:12:17

Following advice from @cybernetic.nomad I have resolved the issue with the following code:

Sub Example()
'Final string to print to file
Dim strFinal As String
'Line read from original text file
Dim strLine As String
'open the text file for reading
Open "C:\x_test.csv" For Input As #1
strFinal = ""
'loop until the end of the text file is reached
While EOF(1) = False
'read the current line from the text file
    Line Input #1, strLine
    'remove the commas
    If InStr(strLine, ",,,") > 0 Then
        strFinal = strFinal + Replace(strLine, ",,,", "") + vbCrLf 
    ElseIf InStr(strLine, ",,") > 0 Then
        strFinal = strFinal + Replace(strLine, ",,", "") + vbCrLf
    Else
        strFinal = strFinal + strLine + vbCrLf
    End If

Wend
strFinal = Left(strFinal, Len(strFinal) - 2)
'close the file
Close #1

'open the file for writing
Open "C:\x_test.csv" For Output As #1
Print #1, strFinal
'overwrite the file with the new string
Close #1
End Sub

Excel remove cells in export to CSV

1 Answers