auto wrap text for newly created csv

excellol

New Member
Joined
May 5, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
Try this:

VBA Code:
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

New Member
Joined
May 5, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows
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
 

excellol

New Member
Joined
May 5, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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

New Member
Joined
May 5, 2020
Messages
19
Office Version
  1. 2019
Platform
  1. Windows
in case u missed post 4, please have a look at my question there
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,908
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,147,474
Messages
5,741,341
Members
423,656
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top