Export to csv file

[YasserKhalil]

Hello everyone
I have data in shCSV in four columns only

Code: Select all

Sub Export_To_CSV()
    Dim wb As Workbook
    Application.DisplayAlerts = False
        shCSV.Copy
        Set wb = ActiveWorkbook
        With wb
            .Worksheets(1).Rows(1).Delete
            .Worksheets(1).Columns("E:XFD").Delete
            .SaveAs ThisWorkbook.Path & "\Code.csv", xlCSVUTF8
            .Close
        End With
    Application.DisplayAlerts = True
End Sub

the problem in the csv output is that each line ends in extra columns like that

Code: Select all

152,-,,,

How can this be fixed so as to get rid of the three separators at the end

[HansV]

Excel inserts a comma for every column, whether the cells are filled or not.
If you want to avoid this, you'll have to write the .csv file yourself.

[YasserKhalil]

The output is OK, I need only to replace all the three commas at the end with null so as to get rid of the extra columns.

[HansV]

If the csv file contains 152,-,,, Excel must have exported 5 columns. You mentioned that you have data in 4 columns. Which cell is selected if you press Ctrl+End in Excel?

[YasserKhalil]

When Pressing Ctrl + End, I got it to column G although the data in four columns only A:D

[HansV]

Select columns E to G.
Right-click in the selection.
Select Delete from the context menu.
Save the workbook.

[YasserKhalil]

I did that several times manualy and the same problem. I even deleted all the columns after column D
the data is populated by worksheet change event (may be something wrong in the code)

Code: Select all

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ws As Worksheet, sh As Worksheet, lr As Long, iRow As Long, m As Long
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Address = "$A$1" Then
        Cancel = True
        Application.ScreenUpdating = False
        Set ws = shClients: Set sh = shCSV
        sh.Range("A1").CurrentRegion.Offset(1).ClearContents
        With ws
            lr = .Cells(Rows.Count, 1).End(xlUp).Row
            m = 2
            For iRow = 2 To lr
                If .Cells(iRow, "E").Value <> Empty And .Cells(iRow, "F").Value <> Empty Then
                    sh.Cells(m, "A").Value = .Cells(iRow, "E").Value
                    sh.Cells(m, "B").Value = .Cells(iRow, "B").Value
                    sh.Cells(m, "C").Value = .Cells(iRow, "F").Value
                    sh.Cells(m, "D").Value = "-"
                    m = m + 1
                End If
                If .Cells(iRow, "G").Value <> Empty And .Cells(iRow, "H").Value <> Empty Then
                    sh.Cells(m, "A").Value = .Cells(iRow, "G").Value
                    sh.Cells(m, "B").Value = .Cells(iRow, "B").Value
                    sh.Cells(m, "C").Value = .Cells(iRow, "H").Value
                    sh.Cells(m, "D").Value = "-"
                    m = m + 1
                End If
            Next iRow
        End With
        Application.ScreenUpdating = True
    End If
End Sub

[HansV]

I don't see anything that populates columns to the right of column D on shCSV.

[YasserKhalil]

I see. That's why i think something wrong.

[DocAElstein]

Hi
There is a quirk in Excel that some things use the last UsedRange, not the actual current one.
It seems that some things that change the actual UsedRange do not update the register thing.
Sometimes the fix is to just use UsedRange explicitly, before you try to do something that uses the value of it stored in the register thing.
Maybe something like this is worth a quick try

Code: Select all

            .Worksheets(1).Rows(1).Delete
            .Worksheets(1).Columns("E:XFD").Delete
            Dim Cnt as Long: let Cnt=UsedRange.Columns.Count
            .SaveAs ThisWorkbook.Path & "\Code.csv", xlCSVUTF8
            .Close

Alan

[DocAElstein]

Along the lines of what Hans said about making the csv file yourself: Depending on your actual typical data, a nice solution could be to copy the used range to the clipboard. Then get the text from the clipboard and replace multiple occurrences of Tabs follow by the line separator, by a single comma and a line separator.
Replace any other Tabs by a single comma
The resulting text would be what you then chuck out to a text file

[YasserKhalil]

It doesn't solve the problem.
How can I replace all the contents of the csv file .. replace the three commas with nothing? I did that manually and it worked.

[DocAElstein]

Sorry, I don’t know how to manipulate a text file directly with coding. My idea would be to manipulate the text, just before you make a text file out of it.
(personally I don’t like to make a text file from saving in csv from Excel, since it often seems to cause problems, as you are relying on Excel to guess what you want. I think probably a csv file is defined as having regular columns. What you want is not a csv file. Its a sort of staggered column text file. I don't know if Excel has an option to do that. I expect not, and if it did it would not call it csv)


If you could upload a small sized shCSV worksheet demonstrating the problem, it would be interesting to examine the contents and a possible solution for you

[HansV]

I'd go with the suggestion from my first reply: instead of saving as .csv, create the text file yourself.
You can write it line by line, or assemble one long string and write that in one go.
See:
Open statement
Print statement
Close statement

[YasserKhalil]

Thanks a lot my tutor. I did it with the apprroach you suggested to get rid of headache