Excel save sheet as VBA but remove commas

[5h1v]

Hi,

I use Excel to create journals that I then upload the problem with this is that it creates additional commas on some lines that I then have to manually remove, is there any VBA I can use that saves the sheet as csv pops up with the save as dialogue box but removes the excess commas?

The first two lines look like this;

HDR,JV1908030RC,Y,Y,,,,,,,,,,
VHD,PJE,OL,JV1908030RC,GL,Book ATM Income 01/04-30/04/19,31/05/2019,N,,SUB,,,,

These would need all the commas at the end of the line removing.

The last lines looks like this;

FLT,19841.68,19841.68,194,1,198,,,,,,,,
,,,,,,,,,,,,,
,0.00,,,,,,,,,,,,

Everything after the 198 would need to be removed.

I use the same method across numerous sheets so something I could use for all of them would be great, they all create the output in the same way though.

Thanks for the help.

 

[kweaver]

Is this what you're looking for?

Sub RemoveCommas()
Dim LR As Long, i As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To LR
Sheets("Sheet1").Range("A" & i) = Replace(Range("A" & i), ",,", "")
Next i
End Sub

Or is that a problem when you have two commas together in a line as in your 2nd row that are NOT at the end?

 

[5h1v]

Sorry I just reread my question and I forgot add that I save the worksheet as a csv file and that is what is causing these additional commas as the row sizes are differnt on the 1st, 2nd and last 3 rows compared to the rest.

 

[xenou]

The additional commas represent empty cells or field values with nothing in them. You could probably just leave that alone - why is it a problem?

For instance a worksheet with these values:
first row: a, b, c, d
second row: a, b

will save as csv:

a,b,c,d
a,b,,

This is correct for csv files.

 

[kweaver]

I saw a nice solution as a formula from Rick Rothstein once.

Put this formula in the cell to the right (in this case B column) assuming (a) your data starts in A2 and (b) isn't longer than 999 characters.
Also, this is an array formula entered with: CTRL+SHIFT+Enter

=LEFT(A2,MAX((MID(A2&REPT(",",999),ROW($2:$999),1)<>",")*ROW($2:$999)))

 

[5h1v]

Thanks for the responses.

I get that the the file will be formatted as
a,b,c,d
a,b,,

the additional commas in the frst lines are what causes an issue when I upload it into the accounting package if I could get rid of the excess one when I save the files as csv it would make it less time consuming having the remove the extras on every file.

I have been tryin a work round where I have all the correct info in one cell and then use VBA to paste it into a csv file but I am getting " at the start and end of every row and I am not sure how to remove it

Sub JournalFileCreation()
Dim myFolder As String
'Save Range as CSV File.
ActiveSheet.Activate
Set myRange = Application.InputBox(prompt:="Please select a range!", _
Title:="CSV File Range!", Type:=8)
myRange.Select
Selection.Copy
Sheets.Add.Name = "Test"
Sheets("Test").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
myFolder = Application.GetSaveAsFilename(fileFilter:="CSV (*.csv), *.csv")
ActiveWorkbook.SaveAs Filename:=myFolder, FileFormat:=xlCSV, CreateBackup:=False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "CSV File: " & myFolder & "Saved!"
Range("A1").Select
End Sub

 

[xenou]

You can edit the file you create after you create it.

For example:

Sub foo()
Dim fso As Object '//Scripting.FileSystemObject
Dim ts(1) As Object '//Scripting.TextStream
Dim s As String
Const OpenForReading As Long = 1
Const OpenForWriting As Long = 2
Dim i As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts(0) = fso.OpenTextFile("C:\myTemp\Book1.csv", OpenForReading)
Set ts(1) = fso.OpenTextFile("C:\myTemp\Book1_Adjusted.csv", OpenForWriting, True)

Do While Not ts(0).AtEndOfStream
    s = ts(0).ReadLine
    Do While s Like "*,"
        s = Left(s, Len(s) - 1)
    Loop
    ts(1).WriteLine s
Loop

For i = 0 To 1
    If Not ts(i) Is Nothing Then
        ts(i).Close
        Set ts(i) = Nothing
    End If
Next i

End Sub

Result before script:

a,b,c,d,e,f,g
a,b,c,,,,
a,b,d,e,e,,
a,b,,,,,
,,,,,,
a,b,c,,,,
a,,,,,,

Result after code:

a,b,c,d,e,f,g
a,b,c
a,b,d,e,e
a,b

a,b,c
a

 

[binarymindset]

You can edit the file you create after you create it.

For example:

Sub foo()
Dim fso As Object '//Scripting.FileSystemObject
Dim ts(1) As Object '//Scripting.TextStream
Dim s As String
Const OpenForReading As Long = 1
Const OpenForWriting As Long = 2
Dim i As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts(0) = fso.OpenTextFile("C:\myTemp\Book1.csv", OpenForReading)
Set ts(1) = fso.OpenTextFile("C:\myTemp\Book1_Adjusted.csv", OpenForWriting, True)

Do While Not ts(0).AtEndOfStream
    s = ts(0).ReadLine
    Do While s Like "*,"
        s = Left(s, Len(s) - 1)
    Loop
    ts(1).WriteLine s
Loop

For i = 0 To 1
    If Not ts(i) Is Nothing Then
        ts(i).Close
        Set ts(i) = Nothing
    End If
Next i

End Sub

Result before script:

a,b,c,d,e,f,g
a,b,c,,,,
a,b,d,e,e,,
a,b,,,,,
,,,,,,
a,b,c,,,,
a,,,,,,

Result after code:

a,b,c,d,e,f,g
a,b,c
a,b,d,e,e
a,b

a,b,c
a

i registered here to thank you very much. this workaround is very dynamic but simple and actually helped me in my work. so thank you!