11 thoughts on “Fixed Width Text Files”

1. I prefer using the below snippet to pad cells out with spaces:

   =LEFT(A1&REPT(” “,50),20)& LEFT(A2&REPT(” “,50),20)

2. I’m guessing this is another route. When I need a fixed width text file I just set the column widths to the number of characters I need (even if this hides some of my proportional font data) and then save the sheet as a space delimited *.prn file. Or am I missing something?

3. […] Fixed Width Text Files automation blogroll book list bugs dose dose of excel downloads files fixed width text printing […]

4. @Nick:
   I have used the same technique with *.prn files. The only issue we had was with not checking that all data in long files conforms to the field widths (e.g. a 10 character value in an 8 character field gets truncated) – though I suppose all three of the above techniques will have issues with this. One advantage of Dick’s method is that the cell will return a “#VALUE” error, which I suppose is at least easy to spot..

5. I’ve done this many times in the past. I would also format the worksheet with a fixed width font like courier and supply the first three rows with Hundreds (99 space 1, 99 space 2, ect), Tens (9 spaces 1, 9 spaces 2, ect), Units (12345678901234567890). This aided as a check for proper alignment.

6. I’m still a big fan of separating “my” users from “their” data… meaning that I would employ a user form to input the data. All error checking is complete before the user can exit the form. If a cell in a column needs to be 50 characters, then the form will not let them enter more than that. If they enter less than 50 characters, then the code behind the form does the required string padding. We get the added benefit of being able to do any other needed error checking at that time too.

7. The secret is Courier New, size 8. Do that and it becomes WYSIWIG, and you can save as *.prn to your heart’s content. I must done this literally tens of thousands of times.

   Here’s a routine I wrote to export the current sheet as a PRN file at the click of a button, without having to navigate through all of the messages Excel hurls at you.

   Sub ExportPRN()
   ‘Saves the active sheet is a text file
   ‘in prn format, without all the nag messages.
   
   Dim sPath As String
   Dim sFileName As String
   Dim sSheetName As String

   ‘Set application settings so that it runs without nag messages
   With Application
     .DisplayAlerts = False
     .ScreenUpdating = False
   End With

   ‘Default save location is the same folder as the workbook.
   sPath = ActiveWorkbook.Path

   ‘If it is a new (unsaved) workbook, save to the Desktop.
   If sPath = “” Then
       Dim WSHShell As Object
       Set WSHShell = CreateObject(“Wscript.Shell”)
       sPath = WSHShell.SpecialFolders(“Desktop”)
       Set WSHShell = Nothing
   End If

   ‘File name will be “Sheet Name.prn”
   sSheetName = ActiveSheet.Name
   sFileName = sPath & “” & sSheetName & “.prn”

   ‘Make a duplicate copy of the active sheet to a new workbook
   Sheets(sSheetName).Copy

   ‘Save the new, single-sheet workbook as a fixed-width text file (*.prn)
   ActiveWorkbook.SaveAs FileName:=sFileName, FileFormat:=xlTextPrinter, CreateBackup:=False

   ‘Close the workbook without saving
   ActiveWindow.Close (False)

   ‘Restore application settings
   With Application
     .DisplayAlerts = True
     .ScreenUpdating = True
   End With

   End Sub

8. oops, looks like you’ll have to change the amp; to an ampersand to get it to work!

9. Excellent tip. Working with pure text files is often cumbersome because of the lack of formatting.

10. The fixed text file I need to create has a total of 303 characters so saving as a .prn wraps the last columns to the bottom of the file.

    How do I get around this?

11. In my limited experience, it doesn’t seem like the font is significant. The column width setting seems to adjust for it. Maybe not. My problem is that if I have a column at the end with nothing in it, Excel seems to insert the CR/FL at the end of the last column with data. So I’m asked for a 177 character record, but the last 48 character column (actually 3 or 4 “fields” that are all empty) is empty. When I look at the .prn file, it appears the CR/LF is at the end of the penultimate column, ignoring the 48 character column. I don’t know how to insert blanks in there (as opposed to spaces). I’m just doing this for a non-technical user who can’t tell me for sure if it matters what happens after the last bit of available data or if filling it with spaces is going to cause a problem. The last “field” is a date field and I don’t know what the import routine does. Does it just accept the data it’s given and ignore the missing fields? Does it fail if the record length is too short? Does it overwrite some field it shouldn’t if it finds a space instead of a blank?

    I was just wondering if there was a way to tell Excel to include 48 blanks, not spaces, before the CR/LF.

Posting code? Use <pre> tags for VBA and <code> tags for inline.