0

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
Improve this question
3
  • 1
    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. Commented Jun 1, 2022 at 9:27
  • Thanks for your valuable time and for your comment @Pᴇʜ. I am sharing with you my solution below. ``` With wbDest 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" wbDest.Close False Set wbDest = Nothing Set wsDest = Nothing End With End If ``` Commented Jun 1, 2022 at 11:37
  • I posted it as an answer. Commented Jun 1, 2022 at 12:53

2 Answers 2

Reset to default
1

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"
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments