Who Can Expand Chip Pearson's procedure ExportToTextFile?

[Curious]

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.

 

[macropod]

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.

 

[Jacob Skaria]

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

 

[HONG]

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 nominatedpath.
  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 row1 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]

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.

 

[keiji kounoike]

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

 

[macropod]

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]



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.

 

[HONG]

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







- 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.

 

[HONG]

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 wantto change:
For CurrentRow = 3 To MaxRow
to
For CurrentRow = 2 To MaxRow

--
Cheers
macropod
[Microsoft MVP - Word]



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.

 

[keiji kounoike]

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

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]



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.