XL: Formatted Text (.prn) Limited to 240 Characters Per Line
ID: Q131554
|
The information in this article applies to:
-
Microsoft Excel for the Macintosh, versions 5.0, 5.0a
-
Microsoft Excel for Windows, versions 5.0, 5.0c
-
Microsoft Excel for Windows NT, version 5.0
-
Microsoft Excel for Windows 95, version 7.0
-
Microsoft Excel 97 for Windows
SYMPTOMS
In Microsoft Excel, when you save a worksheet as a Formatted Text (Space
Delimited) (.prn) file, characters beyond 240 are wrapped to the next
line.
NOTE: If several rows on the same sheet contain text beyond 240 characters,
the text begins wrapping at the row after the last row that contains text.
Consider the following sheet:
Number of
Cell Characters
-----------------
A1 40
A2 255
A3 10
A4 21
A5 2
A6 52
A7 255
A8 5
A9 3
A20 13
The resulting formatted text file has lines with the following number of
characters.
Line Number Characters
------------------------
1 40
2 240
3 10
4 21
5 2
6 52
7 240
8 5
9 3
10-19 0
20 13
21 0
22 15
23-26 0
27 15
After the last line (in this example, line 20), the line numbering starts
at one for the lines that are wrapped. In effect, line 21 corresponds
with line 1, line 22 corresponds with line 2, and so on.
CAUSE
Formatted Text (Space Delimited) (.prn) files have a limitation of 240
characters per line.
WORKAROUND
Microsoft provides examples of Visual Basic for Applications procedures for
illustration only, without warranty either expressed or implied, including,
but not limited to the implied warranties of merchantability and/or fitness
for a particular purpose. The Visual Basic procedures in this article are
provided 'as is' and Microsoft does not guarantee that they can be used in
all situations. While Microsoft support professionals can help explain the
functionality of a particular macro, they will not modify these examples to
provide added functionality, nor will they help you construct macros to
meet your specific needs. If you have limited programming experience, you
may want to consult one of the Microsoft Solution Providers. Solution
Providers offer a wide range of fee-based services, including creating
custom macros. For more information about Microsoft Solution Providers,
call Microsoft Customer Information Service at (800) 426-9400.
To create a space delimited text file that exceeds the 240-character per
line limitation, use a macro similar to the following sample macro.
NOTE: Before you run this macro, do the following:
- Select the cells to be included in the text file.
- Verify that column widths are wide enough to view the largest string
in each column.
- Use the Style menu command to format the worksheet to use a fixed
width font. For example, Courier is a fixed width font.
The following sample code can be modified to export data delimited with
characters other than a space:
Sub ExportText()
Dim delimiter As String
Dim quotes As Integer
Dim Returned As String
delimiter = " "
quotes = MsgBox("Surround Cell Information with Quotes?", vbYesNo)
' Call the WriteFile function passing the delimiter and quotes options.
Returned = WriteFile(delimiter, quotes)
' Print a message box indicating if the process was completed.
Select Case Returned
Case "Canceled"
MsgBox "The export operation was canceled."
Case "Exported"
MsgBox "The information was exported."
End Select
End Sub
'-------------------------------------------------------------------
Function WriteFile(delimiter As String, quotes As Integer) As String
' Dimension variables to be used in this function.
Dim CurFile As String
Dim SaveFileName
Dim CellText As String
Dim RowNum As Integer
Dim ColNum As Integer
Dim FNum As Integer
Dim TotalRows As Double
Dim TotalCols As Double
' Show Save As dialog box with the .TXT file name as the default.
' Test to see what kind of system this macro is being run on.
If Left(Application.OperatingSystem, 3) = "Win" Then
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"Text Delimited (*.txt), *.txt", , "Text Delimited Exporter")
Else
SaveFileName = Application.GetSaveAsFilename(CurFile, _
"TEXT", , "Text Delimited Exporter")
End If
' Check to see if Cancel was clicked.
If SaveFileName = False Then
WriteFile = "Canceled"
Exit Function
End If
' Obtain the next free file number.
FNum = FreeFile()
' Open the selected file name for data output.
Open SaveFileName For Output As #FNum
' Store the total number of rows and columns to variables.
TotalRows = Selection.Rows.Count
TotalCols = Selection.Columns.Count
' Loop through every cell, from left to right and top to bottom.
For RowNum = 1 To TotalRows
For ColNum = 1 To TotalCols
With Selection.Cells(RowNum, ColNum)
Dim ColWidth as Integer
ColWidth=Application.RoundUp(.ColumnWidth, 0)
' Store the current cells contents to a variable.
Select Case .HorizontalAlignment
Case xlRight
CellText = Space(ColWidth - Len(.Text)) & .Text
Case xlCenter
CellText = Space((ColWidth - Len(.Text))/2) & .Text & _
Space((ColWidth - Len(.Text))/2)
Case Else
CellText = .Text & Space(ColWidth - Len(.Text))
End Select
End With
' Write the contents to the file.
' With or without quotation marks around the cell information.
Select Case quotes
Case vbYes
CellText = Chr(34) & CellText & Chr(34) & delimiter
Case vbNo
CellText = CellText & delimiter
End Select
Print #FNum, CellText;
' Update the status bar with the progress.
Application.StatusBar = Format((((RowNum - 1) * TotalCols) _
+ ColNum) / (TotalRows * TotalCols), "0%") & " Completed."
' Loop to the next column.
Next ColNum
' Add a linefeed character at the end of each row.
If RowNum <> TotalRows Then Print #FNum, ""
' Loop to the next row.
Next RowNum
' Close the .prn file.
Close #FNum
' Reset the status bar.
Application.StatusBar = False
WriteFile = "Exported"
End Function
NOTE: The output file this routine creates is, by definition, different
from a Formatted Text(*.prn) file. By definition, the formatted text file
cannot contain more than 240 characters per line. In addition, the
formatted text file also contains printer font information. This example
does not. This solution is designed to give flexibility when exporting to
text files.
Additional query words:
delimit parse export quote text
Keywords : kbcode kbprg kbusage
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0; winnt:5.0
Platform : MACINTOSH WINDOWS winnt
Issue type : kbprb