Data export from Excel to CSV-CodePudding

When I transferred the data from separated columns and rows from my excel sheet to .csv format, it does not separate the data in .csv format. I am putting my code below.

Private Sub CommandButton1_Click()
    Dim lastrow As Long, wb As Workbook, ws As Worksheet, wbDest As Workbook, _
        wsDest As Worksheet, path As String

    If ComboBox1.Value = "DENİZBANK - ALTUNİZADE ŞUBE" Then
        'Combobox isimli bir dosya aç
        Set wb = ThisWorkbook
        Set ws = wb.Sheets("TL")
        path = "C:\Users\emir.DEMTA\Desktop\Vahit Çağlayan Dosyalar\p. CSV\DENİZBANK - ALTUNİZADE ŞUBE\Deniz_Giden_Banka_HGS.csv"
        Set wbDest = Workbooks.Open(path)
        Set wsDest = wbDest.Worksheets("Deniz_Giden_Banka_HGS")
        n = 1

        For i = 13 To 50 'lastrow
            'Aşağıdaki verileri combobox isimli dosyaya yazdır.
            If ComboBox1.Value = "DENİZBANK - ALTUNİZADE ŞUBE" And _
               ComboBox5.Value = "1073196-389" And _
               TextBox5.Value <> "" And _
               ws.Cells(i, "A").Value = "DENİZBANK - ALTUNİZADE ŞUBE" And _
               ws.Cells(i, "I").Value = "Banka" Then
                wsDest.Cells(n   1, 1) = ws.Cells(i, 9).Value
                wsDest.Cells(n   1, 2) = ws.Cells(i, 4).Value
                wsDest.Cells(n   1, 3) = ws.Cells(i, 6).Value
                wsDest.Cells(n   1, 5) = "1"
                wsDest.Cells(n   1, 6) = "'01"
                wsDest.Cells(n   1, 7) = ws.Cells(i, 7).Value
                wsDest.Cells(n   1, 8) = ws.Cells(i, 8).Value

                n = n   1
            End If
        Next i

        wbDest.SaveAs Filename:="C:\Users\emir.DEMTA\Desktop\Vahit Çağlayan Dosyalar\p. CSV\DENİZBANK - ALTUNİZADE ŞUBE\" _
           & " D - HGS - " & Date & ".csv"
        wbDest.Close
        Set wbDest = Nothing

        Unload Me
    End If
End Sub

Every information in the Excel transferred to .csv occurred alongside in one cell

MasrafHGS100

What I want is to transfer all the data the same of at my code

Masraf    HGS    100

CodePudding user response：

If you .SaveAs you also need to specify the FileFormat:= according the XlFileFormat enumeration in your case probably FileFormat:=xlCSV. Then open the CSV in a text editor to check it! Not in Excel.

If you open it in Excel, it does a CSV to Excel import (if that has the wrong porperties it does not import it correctly into different columns). Note that a double click on a .CSV file (that opens it in Excel) is a lucky shot and no proper import! It might show up as expected or not.

wbDest.SaveAs FileFormat:=xlCSV, local:=True, Filename:="C:\Users\emir.DEMTA\Desktop\Vahit Çağlayan Dosyalar\p. CSV\DENİZBANK - ALTUNİZADE ŞUBE\" _ & " D - HGS - " & Date & ".csv"

CodePudding user response：

In the code you're just specifying that the file ends with ".csv" . However that does not apply the "format" of a CSV file.

To do that you need to always specify when you do a .SaveAs Name - FileFormat You can do FileFormat:=xlCSV or FileFormat:=6

Alternatively, you can always do a FileFormat:=xlTextWindows to get a .txt file and then transform it to a .csv using an alternative method.

Hope it helps!