Okay, here's a new version for a fixed number of columns.

The line: For intColumn = 1 To 7 is the loop for getting the value from
each column. Change the 7 to the highest column you are using. If you
didn't start in column A or Row 1, change the 1 to the start column, and
change Range("A1").Activate to activate the correct starting cell.
To create the macro, I suggest starting with a copy of your xls file.

In Tools - Options, on the Security Tab (in Excel 2002, maybe different
in other versions) click macro security and set it to medium. If it is
high, you can't run macros. Medium will ask every time you open a file
containing macros. If low, it never asks. Maybe risky for some. I
like medium.

Press Alt+F11 to get into the Visual Basic Editor (VBE).
There should be a project tree visible, probably on the left. If not
visible, press ctrl+R, or View - Project Explorer.

Right click on the line "VBAProject (your file name)", click insert,
click module. That should create a module1 at the bottom of the tree
and open a code window. This is where you paste the macro. Everything
including the End Sub line.

This is a good time to save! Saving in the VBE saves the xls file, just
like in the Excel window.

Make sure the output file name and path are ok. I used
c:\temp\output100.txt. It can also get the file name from a cell if you
like. If the file name is in K1, Use something like strFileName =
Range("K1").value, or if you named a cell, use the cell name in place of K1.
To run it right thru, press F5 while in the VBE. You can also step thru
the code and watch it work using F8 instead of F5. I usually have the
VBE window unmaximized and off to the right with the excel sheet visible
to the left. Be careful about moving the active cell in Excel while
running a macro. Until you are familiar with the macro, it is best not
to click in the Excel window. Back in the VBE, every press of F8 runs
one line. The yellow highlighted line is the next line to be executed.
You can hover the mouse cursor on a variable name (strTextOut is a
good one here) and a tooltip will show the value of the variable. If
you step thru the inner For-Next loop while hovering on strTextOut, you
will see how strTextOut is created one cell at a time.
Press F5 anytime while stepping thru to run to the end. Also right
click on a line gives you some options. A useful one is "Run To
Cursor". That will stop at the line where the cursor is. "Set Next
Statement" makes that line the next one to be run. Pressing F9 makes a
breakpoint on the line with the cursor. The macro will always stop on
that line waiting for an F5 or F8.
Hope this is clear enough and that I didn't miss anything. If you have
to run it often, you can create a toolbar button, or a Ctrl-Shift-letter
combination, or just press Alt + F8 and run the macro from there. No
need to have the VBE open.

Len


Sub MakeTextFile()
Dim strTemp As String
Dim strTextOut As String
Dim strFileName As String
Dim intOutFile As Integer
Dim intColumn As Integer

intOutFile = FreeFile 'get an available file number
strFileName = "c:\temp\output100.txt" 'or whereever/whatever
Open strFileName For Output As intOutFile
Range("A1").Activate 'start at the top
strTemp = ActiveCell.Value
Do While Len(strTemp) 0 'goes until a blank cell in column A
strTextOut = "" 'initialize
'loop thru the columns. Change 7 to the
'highest column number used
For intColumn = 1 To 7
strTextOut = strTextOut & Cells(ActiveCell.Row, intColumn).Value
Next
Print #intOutFile, strTextOut 'send it to the output file
ActiveCell.Offset(1, 0).Activate 'go down a row
strTemp = ActiveCell.Value
Loop
Close intOutFile
End Sub



MojoNixon wrote:
LenB

Thanks for your help. Yes, please I do need help in creating this macro
if you can give a quick hot-to. Also, if there is no "end text", but
there is a fixed amount of column's, can that be used to in place of
the end text?

thanks again,

Scott