Copy data from Excel to CSV file

Question

I have an Excel spreadsheet.

I want to copy Barcode Code and Quantity data from the Excel sheet to a CSV file as:

101320,546

101330,330

101340,360

With the below codes:

Dim i As Long, lastrow As Long
' Sheet name is İrsaliye
lastrow = Sheets("İrsaliye").Range("C1000").End(xlUp).Row 

For i = 23 To lastrow

    Cells(i, "C").Copy
    Cells(i, "I").Copy
    Workbooks.Add
    ActiveSheet.Paste
    ActiveWorkbook.SaveAs Filename:= _
    "C:\Users\oguz\Desktop\csv\yourCSV.csv" _
    , FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    Application.DisplayAlerts = False
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

Next

It just takes last Quantity data 366 to csv file.

If I use below code line

Range("C23:C25").Copy

instead of

Cells(i, "C").Copy

It takes all the data from Barcode Codes to CSV but in this way I could not take quantities to CSV file.

What is the error it gives you? On which line does it occur? Please include that in your question. — braX, Jul 10 '20 at 10:57
@braX you are right . I edited my problem. It does not gives an error. it is just take last Quantity data 366 to csv file. — , Jul 10 '20 at 11:01
At first glance, you have 2 `Copy` lines, one after another, making the first one irrelevant. You would need to paste the first one before doing a 2nd Copy. — braX, Jul 10 '20 at 11:04
At second glance, you are creating a new workbook for each iteration of the loop, and then saving overtop of the same file each time. I doubt that is your intent. — braX, Jul 10 '20 at 11:05

Muzaffer Galata · Accepted Answer · 2020-07-10T15:47:22.410

0

Please try this:

    Dim i As Long, lastRow As Long
    lastRow = Sheets("İrsaliye").Cells(Rows.Count, "A").End(xlUp).Row
    
    Dim sourceWb As Workbook
    Dim destinationWb As Workbook
    Set sourceWb = ThisWorkbook
    Set destinationWb = Workbooks.Add
    
    sourceWb.ActiveSheet.Range("C2:C" & lastRow).Copy
    destinationWb.Sheets(1).Range("A1").PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
    sourceWb.ActiveSheet.Range("I2:I" & lastRow).Copy
    destinationWb.Sheets(1).Range("B1").PasteSpecial xlPasteAll
    
    Application.ScreenUpdating = True        
        
    Application.DisplayAlerts = False
    destinationWb.SaveAs Filename:="c:\TMP\tmp.csv", FileFormat:=xlCSV, CreateBackup:=False
    destinationWb.Close SaveChanges:=False
    Application.DisplayAlerts = True

edited Jul 10 '20 at 15:47

answered Jul 10 '20 at 11:24

Muzaffer Galata

580
1
9
22

thanks Muzaffer, it works. Just a simple question too. Before it paste the lines to csv, excel just opens but not closed. How can i close it automaticlly_ – Jul 10 '20 at 11:58
Dear Muzaffer thank a lot. Maybe i am going too far however i have one question too. When i converted to data, it seperated the barcode code and quantity with coma (,) Could it possible to put semi colon (;) instead of coma (,) between the barcode code and quantity ? – Jul 16 '20 at 07:58
This suggestion may helps you. https://stackoverflow.com/a/19265906/1068246 if not may be this https://excel.tips.net/T003232_Specifying_a_Delimiter_when_Saving_a_CSV_File_in_a_Macro.html – Muzaffer Galata Jul 16 '20 at 13:58
Thanks again. https://stackoverflow.com/questions/19265636/save-as-csv-with-semicolon-separator I find the solution in here,eyvallah. – Jul 16 '20 at 15:16

Copy data from Excel to CSV file

1 Answers1