Hi Curious,

Try something like:

Sub TextFileExport()
'The next row tells Excel where to save the output. Modify as needed, keeping the trailing backslash.
Const FilePath = "C:\Users\Waramanga\Documents\"
Dim WkSht As Worksheet, ff As Integer
Dim CurrentRow As Long, CurrentCol As Long
Dim MaxRow As Long, MaxCol As Long
Dim strOutput As String
'Loop through all worksheets.
For Each WkSht In ActiveWorkbook.Worksheets
ff = FreeFile
'Open a text file using the current worksheet's name in the nominated path.
Open FilePath & WkSht.Name & ".txt" For Output As #ff
MaxRow = WkSht.Range("A65536").End(xlUp).Row
MaxCol = WkSht.Range("IV1").End(xlToLeft).Column
'The next code line determines the start & end rows. If using the row 1 to hold the column widths
'and row 2 to hold alignment properties, start at row 3. Otherwise start at row 1.
For CurrentRow = 3 To MaxRow
strOutput = ""
'The next line determines the start & end columns.
For CurrentCol = 1 To MaxCol
'Use the value in row 1 to determine column widths and
'the value (if any) in row 2 to determine alignments.
If Left(Trim(UCase(WkSht.Cells(2, CurrentCol))), 1) = "R" Then
strOutput = strOutput & Right(Space(255) & WkSht.Cells(CurrentRow, CurrentCol), _
WkSht.Cells(1, CurrentCol))
Else
strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), _
WkSht.Cells(1, CurrentCol))
End If
'Add a pipe separator
If CurrentCol < MaxCol Then strOutput = strOutput & "|"
Next CurrentCol
'Write the line to the file.
Print #ff, strOutput
Next CurrentRow
'Close the file.
Close #ff
Next WkSht
Set WkSht = Nothing
End Sub

Note: the above code takes the column widths from a value in row 1 for each column. The default column alignment is 'left', but an
'R' or 'r' as the first character in row 2 for a given column forces right alignment for that column.

--
Cheers
macropod
[Microsoft MVP - Word]


"Curious" wrote in message ...
I want to use his procedure "ExportToTextFile". But I need more. The
exported spreadsheet needs to be in text format to satisfy two
conditions: (1) pipe "|" delimiter and (2) fixed width, say each field
must be 10 character wide(including the delimiter).

My understanding is that we can create a text file either (not both)
with fixed width or with delimiter. How can I satisfy both?

Thanks in advance for any clue.

H.Z.