CODEWITHSUNDEEP
vbaexcel
Ashwin Balaji Kuppuraj
Ashwin Balaji Kuppuraj

Reputation: 81

How can I avoid Line feeder in text file when I write the content from excel sheet?

My Piece of VBA code for writing content from excel sheet to text file is as below.

ActiveWorkbook.Sheets("Sheet1").Activate
With ActiveSheet
    .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
End With
Dim strPath As String
Dim FSO As Object
Dim oFile As Object
Set oFile = Nothing
Dim c As Range
Dim linefeed_remover
strPath = "path"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set oFile = FSO.CreateTextFile(strPath & filesname)
For Each c In Selection
    oFile.Write Application.WorksheetFunction.Clean(c.Value) & vbLf
Next c
oFile.Close
strNameOld = strName & "_Old"
'MsgBox "File Created"
'conn2.Close

can anyone help me in solving this?

Upvotes: 1

Views: 78

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149315

Suggestions

  1. Don't loop through the cells in a range. Store them in an array and loop through the array. It is a much faster method.

  2. Working with array also lets us know which record we are handling. In case we are working with the last record we can skip adding the life feed.

See this example.

Sub Sample()
    Dim strPath As String
    Dim FSO As Object, oFile As Object
    Dim c As Range
    Dim linefeed_remover
    Dim MyAr

    ActiveWorkbook.Sheets("Sheet1").Activate
    With ActiveSheet
        .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)).Select
    End With

    Set oFile = Nothing

    strPath = "C:\Users\Siddharth\Desktop\"
    filesname = "Sample.Txt"

    '~~> Transfer the entire range in an array
    '~~> For faster performcance
    MyAr = Selection.Value

    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set oFile = FSO.CreateTextFile(strPath & filesname)

    For i = LBound(MyAr) To UBound(MyAr)
        '~~> Check if it is the last record so that
        '~~> We do not add the linefeed
        If i = UBound(MyAr) Then
            oFile.Write Application.WorksheetFunction.Clean(MyAr(i, 1))
        Else
            oFile.Write Application.WorksheetFunction.Clean(MyAr(i, 1)) & vbNewLine
        End If
    Next i

    oFile.Close
End Sub

Upvotes: 2

Related Questions