Create txt file from excel sheet

I am a beginner in excel macro, and hope to find an answer to my question in this great forum.
I want to create APRIL.txt file from the data in my spreadsheet.

The sample Sheet is attached here with.

If I may ask, what is the complication about this? I mean, if it's me I would just copy that and paste into a notepad and save.

This question's been asked a few times recently - so I've written a quick post to cover it:

http://excel.solutions/2014/04/using...-to-text-file/

yaa, it s work,

Thanks a lot all above my welwisher,

Hi apo,
will u add code if any row in a column A is mark "X" can skip in above code?

You can easily adapt my code to do that - see highlights in red:

Sub WriteTextFile()

Dim rng As Range, lRow As Long
Dim stOutput As String, stNextLine As String, stSeparator As String
Dim stFilename As String, stEncoding As String
Dim fso As Object

'-------------------------------------------------------------------------------------
'CHANGE THESE PARAMETERS TO SUIT
Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text file
stFilename = "C:\Temp\TextOutput.txt" 'this is the text file path / name
stSeparator = vbTab 'e.g. for comma seperated value, change this to ","
stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
'-------------------------------------------------------------------------------------

For lRow = 1 To rng.Rows.Count
    If rng.Cells(lRow, 1).Value <> "X" Then
        If rng.Columns.Count = 1 Then
            stNextLine = rng.Rows(lRow).Value
        Else
            stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
        End If
        If stOutput = "" Then
            stOutput = stNextLine
        Else
            stOutput = stOutput & vbCrLf & stNextLine
        End If
    End If
Next lRow

Set fso = CreateObject("ADODB.Stream")
With fso
    .Type = 2
    .Charset = stEncoding
    .Open
    .WriteText stOutput
    .SaveToFile stFilename, 2
End With
Set fso = Nothing

End Sub

Hi olly,

Thanks for prompt reply,
its work fine,

hi olly,
your code is perfect but its generate all column while i want only column "B" data only

Originally Posted by hkbhansali

hi olly,
your code is perfect but its generate all column while i want only column "B" data only

Come on, you need to start understanding and tweaking the code to suit your workbook.

Change the line highlighted in blue, to the range you actually want this to apply to:

Sub WriteTextFile()

Dim rng As Range, lRow As Long
Dim stOutput As String, stNextLine As String, stSeparator As String
Dim stFilename As String, stEncoding As String
Dim fso As Object

'-------------------------------------------------------------------------------------
'CHANGE THESE PARAMETERS TO SUIT
Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text file
stFilename = "C:\Temp\TextOutput.txt" 'this is the text file path / name
stSeparator = vbTab 'e.g. for comma seperated value, change this to ","
stEncoding = "UTF-8" 'e.g. "UTF-8", "ASCII"
'-------------------------------------------------------------------------------------

For lRow = 1 To rng.Rows.Count
    If rng.Cells(lRow, 1).Value <> "X" Then
        If rng.Columns.Count = 1 Then
            stNextLine = rng.Rows(lRow).Value
        Else
            stNextLine = Join$(Application.Transpose(Application.Transpose(rng.Rows(lRow).Value)), stSeparator)
        End If
        If stOutput = "" Then
            stOutput = stNextLine
        Else
            stOutput = stOutput & vbCrLf & stNextLine
        End If
    End If
Next lRow

Set fso = CreateObject("ADODB.Stream")
With fso
    .Type = 2
    .Charset = stEncoding
    .Open
    .WriteText stOutput
    .SaveToFile stFilename, 2
End With
Set fso = Nothing

End Sub

So in your case, you may change this to something like:

Set rng = ActiveSheet.Range ("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row)

Hi,olly, & apo
THANKS,
my problem solved, thanks for kind help.

Hi,olly, & apo
THANKS,
my problem solved, thanks for kind help.

Hi, apo
u r right my data is in 1 column only and its "B" column,
above code is not working.