How to save particular column data of an excel worksheet to .txt file including line breaks in each cell content

Question

I am pretty new to VBA. I have a requirement where I need to save the data in a particular column of an Excel worksheet (let's say used range of C column data) to a .txt file. After some web searching I got the VBA code, but the issue is, if a cell (say Cell(1,3)) is having data in multiple lines as shown below

Cell(1,3):

I got the VBA but I have a issue.

The VBA my using is saving the cell(1,3) content in a single line -'i got the vba but I have a issue help me out' I don't want it to be in a single line.

My requirement is very simple.

I have an activex button in the sheet.
when I click the button, data in C column must be saved to .txt file including line breaks. so if cell(1,3) has 4 lines and cell(2,3) has 2 lines then .txt file should have 6 lines. .txt file should exactly replicate my C column.

3.And this text file must be saved to "C"\vbacodes\" path.

Below is this code I found on internet

Private Sub CommandButton1_Click()
'Update 20130913Dim wb As Workbook
Dim saveFile As String
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Application.Workbooks.Add
WorkRng.Copy
wb.Worksheets(1).Paste
saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
wb.Close
Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Happy New Year but you really do need to post the code that you are currently using. — barryleajo

Gary's Student Gary's Student · Accepted Answer · 2015-01-01T13:25:47

First change the path and filename to match your requirements and then assign this macro to a button:

Sub TextFileMaker()

    Dim MyFilePath As String, MyFileName As String
    Dim N As Long, nFirstRow As Long, nLastrow As Long

    MyFilePath = "C:\TestFolder\"
    MyFileName = "whatever"
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set a = fs.CreateTextFile(MyFilePath & MyFileName & ".txt", True)
    nLastrow = Cells(Rows.Count, "C").End(xlUp).Row
    nFirstRow = 1

    For N = nFirstRow To nLastrow
        t = Replace(Cells(N, "C").Text, Chr(10), vbCrLf)
        a.WriteLine (t)
    Next
    a.Close

End Sub

How to save particular column data of an excel worksheet to .txt file including line breaks in each cell content

1 Answers