auto wrap text for newly created csv

[excellol]

im practicing vba and the code below writes data from the excel sheet to a newly created csv. Column A shows the ISBN number, column B shows the last name and column c shows the first name. The data in the cells are not formatted with wrap text

however, when i open the newly created csv in excel, the cells are automatically formatted with wrap text. This causes the data for each row to not be in a single line when i open the csv in notepad.

How do i remove the auto wrap text such that my data will appear in a single line when i open the csv in notepad?

Sub writetextfile()

    Dim filepath As String
    Dim celldata As String
    Dim lastcol As Long
    Dim lastrow As Long
    
    lastcol = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Column

    lastrow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

  
    celldata = ""
    
    filepath = application.defaultfilepath & "\auth.csv"
    
    Open filepath For Output As #2
 
    
    For i = 1 To lastrow
        For j = 1 To lastcol
            If j = lastcol Then
                celldata = celldata + Trim(Cells(i, j).Value)
              
            Else
                celldata = celldata + Trim(Cells(i, j).Value) + ","
        
            End If
        Next j
        
        Write #2, celldata
        celldata = ""
        
    Next i
    
    Close #2
    MsgBox "done"
    
End Sub

 

[DanteAmor]

Try this:

Sub writetextfile()
  Dim filepath As String
  Dim i As Long, j As Long
  
  filepath = Application.DefaultFilePath & "\auth.csv"
  Open filepath For Output As #1
  For i = 1 To Range("A" & Rows.Count).End(3).Row
    For j = 1 To 3
      Print #1, Cells(i, j); ",";
    Next j
    Print #1,
  Next i
  Close #1
  MsgBox "done"
End Sub

 

[excellol]

i executed ur code and the same error occured.
turns out wrap text was automatically activated due to first names in column C have an empty line above each first name.

in that case why doesn't the trim function in my original code remove this extra line?

also, i cycled through my original code line by line and found that "j" ended with the value 4 (should be 3 as only columns A to C are entered with data), due to the usedrange function selecting the range A1:D10. however, the data is only contained in the range A1:C8. i used ctrl+down in column D (starting from D1) and ctrl+right in rows 8 and 9 (starting from A8 and A9) but found no values contained in these cells. is this a bug or something that i missed out?

this is the website for the original code

Excel VBA Programming - Write To A Text File

Learn how to write to a text or csv file using VBA code.

www.homeandlearn.org

 

[excellol]

For j = 1 To 3
      Print #1, Cells(i, j); ",";

also what does the ; after "," tell excel to do? i ran the code both with and without ; and found that with ; the data are joined together in a single row. without ; each cell data is printed in the next line.

the difference is obvious but would like to know what that ; after "," tell excel to do

 

[DanteAmor]

The data in the cells are not formatted with wrap text

It's not formatted but you seem to have a line control character.
Try the following to remove that character:

Sub writetextfile()
  Dim filepath As String
  Dim i As Long, j As Long

  Range("A:C").Replace Chr(10), "", xlPart
  filepath = Application.DefaultFilePath & "\auth.csv"
  Open filepath For Output As #1
  For i = 1 To Range("A" & Rows.Count).End(3).Row
    For j = 1 To 3
      Print #1, Cells(i, j); ",";
    Next j
    Print #1,
  Next i
  Close #1
  MsgBox "done"
End Sub

Or you definitely have other control characters in those cells. It tests on a new sheet, captures new data, and tests my macro again.

 

[excellol]

in case u missed post 4, please have a look at my question there

 

[DanteAmor]

in case u missed post 4, please have a look at my question there

It could be, but I think you have a data problem. Then you have to correct the problem of your data and we will gladly enter all the explanations you want.

also what does the ; after ","

Print #1, Cells(i, j); ","; Put the field (i,j), the field separator "," and the ";" is to continue with in the same line.
Next j
Print #1, The "," is to add the end of the line.

_______________

Try the macro on a new sheet with new data, but capture the data, do not copy and paste it from somewhere else. It is only for testing.