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.

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.

Try the below. You need to adjust the endrow and end column to suit your
requirement OR you can set that to the last row, last col...

Sub OutputAsFixedWidthPipe()

Dim lngRow As Long
Dim lngCol As Long
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim intFile As Integer
Dim strData As String
intFile = FreeFile

lngLastRow = 20
lngLastCol = 10

Open "c:\test.txt" For Output As #intFile
For lngRow = 1 To lngLastRow
strData = ""
For lngCol = 1 To lngLastCol
strData = strData & "|" & Cells(lngRow, lngCol) & _
Space(10 - Len(Cells(lngRow, lngCol)))
Next lngCol
Print #intFile, Mid(strData, 2)
Next lngRow
Close #intFile

End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Curious" wrote:

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.

On May 10, 9:17*pm, "macropod" wrote:
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 ...
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.

Dear macropod:

Your code works like a chime. May I ask one more help: can you make
all the fields right-alignment?

Thank you again.

H.Z.

To make such a file could be possible, but I wonder why do you need
fixed width records with a delimiter? what application is supposed to
read such a file? what style do you want to have in fixed width with a
delimiter, 1) or 2) or 3)?

1) abcd(SP)(sp)(sp)(sp)(sp)|
2) abcd|(SP)(sp)(sp)(sp)(sp)
3) other style

(sp) means space character.

keiji

Curious wrote:
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.

Hi Hong,

You could simply insert 'R' into all cells in row 2. Alternatively, simply delete:

If Left(Trim(UCase(WkSht.Cells(2, CurrentCol))), 1) = "R" Then

and
Else
strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), _
WkSht.Cells(1, CurrentCol))
End If

Having done that and, assuming you don't want the 'R' row, you might want to change:
For CurrentRow = 3 To MaxRow
to
For CurrentRow = 2 To MaxRow

--
Cheers
macropod
[Microsoft MVP - Word]


"HONG" wrote in message ...
On May 10, 9:17 pm, "macropod" wrote:

Dear macropod:

Your code works like a chime. May I ask one more help: can you make
all the fields right-alignment?

Thank you again.

H.Z.

On May 10, 10:29*pm, keiji kounoike <"kounoike AT mbh.nifty.com"
wrote:
To make such a file could be possible, but I wonder why do you need
fixed width records with a delimiter? what application is supposed to
read such a file? what style do you want to have in fixed width with a
delimiter, 1) or 2) or 3)?

1) abcd(SP)(sp)(sp)(sp)(sp)|
2) abcd|(SP)(sp)(sp)(sp)(sp)
3) other style

(sp) means space character.

keiji



Curious wrote:
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 wecancreate a text file either (not both)
with fixed width or with delimiter. HowcanI satisfy both?

Thanks in advance for any clue.

H.Z.- Hide quoted text -

- Show quoted text -

Your question is a legitimate one. I was asking myself why this format
is demanded. My head quarter requires this format for all reports from
all the subsidiaries. I tried to export my spreadsheet to Access
without success. And I tried to save the spreadsheet as a text file,
but Excel only gives me three choises: tab delimited, comma delimited
and another one. None of them helps.

Thank you for your response.

H. Z.

On May 11, 7:20*am, "macropod" wrote:
Hi Hong,

You could simply insert 'R' into all cells in row 2. Alternatively, simply delete:

If Left(Trim(UCase(WkSht.Cells(2, CurrentCol))), 1) = "R" Then

and
Else
* strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), _
* WkSht.Cells(1, CurrentCol))
End If

Having done that and, assuming you don't want the 'R' row, you might want to change:
For CurrentRow = 3 To MaxRow
to
For CurrentRow = 2 To MaxRow

--
Cheers
macropod
[Microsoft MVP - Word]

"HONG" wrote in ...

On May 10, 9:17 pm, "macropod" wrote:

Dear macropod:

Your code works like a chime. May I ask one more help:canyou make
all the fields right-alignment?

Thank you again.

H.Z.

Thank you, macropod, Jacob, and Keiji for your generous help.
Macropod, your code saves hours of my head-scratching.

H.Z.

Hi HONG

I have one more question. you said all the fields need to be
right-alignment. Does that mean if the length of data is longer than 9
like "1234567890", what you want is not "123456789" but "234567890"?
this is what this code gives you.

Keiji

HONG wrote:
On May 11, 7:20 am, "macropod" wrote:
Hi Hong,

You could simply insert 'R' into all cells in row 2. Alternatively, simply delete:

If Left(Trim(UCase(WkSht.Cells(2, CurrentCol))), 1) = "R" Then

and
Else
strOutput = strOutput & Left(WkSht.Cells(CurrentRow, CurrentCol) & Space(255), _
WkSht.Cells(1, CurrentCol))
End If

Having done that and, assuming you don't want the 'R' row, you might want to change:
For CurrentRow = 3 To MaxRow
to
For CurrentRow = 2 To MaxRow

--
Cheers
macropod
[Microsoft MVP - Word]

"HONG" wrote in ...

On May 10, 9:17 pm, "macropod" wrote:

Dear macropod:

Your code works like a chime. May I ask one more help:canyou make
all the fields right-alignment?

Thank you again.

H.Z.

Thank you, macropod, Jacob, and Keiji for your generous help.
Macropod, your code saves hours of my head-scratching.

H.Z.