Problem with Saving Excel File as PRN

[DH]

I need to be able to save an excel file in a fixed width format. I saved as a
PRN format, but some of the data is not saving with the file. Out of 30
columns of data only the first 5 saved with the file.

When the warning message that says some data may not be compatible, I
answered YES to proceed. I also tested it answering NO, but still had the
same results - missing data.

Any suggestions would be greatly appreciated !!!

 

[Dave Peterson]

There is a limit on how wide the output file will be in .prn files (I think it's
240 characters).

Maybe the row was split over multiple pieces. You may want to scroll down a bit
to see if that's where your data went.

If you have to create a .prn file that's wider than 240 characters...

Saved from a previous post:

There's a limit of 240 characters per line when you do File|saveas and use .prn
files. So if your data wouldn't create a record that was longer than 240
characters, you can save the file as .prn.

I like to use a fixed width font (courier new) and adjust the column widths
manually. But this can take a while to get it perfect. (Save it, check the
output in a text editor, back to excel, adjust, save, and recheck in that text
editor. Lather, rinse, and repeat!)

Alternatively, you could concatenate the cell values into another column:

=LEFT(A1&REPT(" ",5),5) & LEFT(B1&REPT(" ",4),4) & TEXT(C1,"000,000.00")

(You'll have to modify it to match what you want.)

Drag it down the column to get all that fixed width stuff.

Then I'd copy and paste to notepad and save from there. Once I figured out that
ugly formula, I kept it and just unhide that column when I wanted to export the
data.

If that doesn't work for you, maybe you could do it with a macro.

Here's a link that provides a macro:
http://google.com/[email protected]

 

[DH]

Hi Dave,

Thank you for getting back to me.

I was talking with someone here in my office and they suggested I save it as
a TXT format. Do you know if that would work if my vendor is asking for the
file to be fixed width ASCII?

Dawn

 

[Dave Peterson]

If you mean a tab delimited text file, then this won't be the same as a fixed
width file.

Fixed width fields mean the text file doesn't have its fields separted by
anything. The fields are defined by the columns that they occupy. Field one
could be columns 1-10, field 2 could be columns 11-12, field 3 could be 13-20.

With a tab delimited file, the data is usually trimmed and there is an extra
character (the tab delimiter) between each field.

So if I had this in columns A:C:
aaa b ccccc

The data in the PRN/fixed width file would look like:
aaa_______b_ccccc_____
(where _ represents a space character)

In that tab delimited file, the data would look like:
aaa|b|ccccc
where | represents the tab character

But...

Check with the recipient and ask if they would accept a tab delimited file.
They may surprise you and say yes. I'd bet that they have some conversion
utility (or even import procedure) that can handle that tab delimited file.

(Sometimes requirements are just suggestions <vbg>.)

 

[Gord Dibben]

Earl Kiosterud's free TEXTWRITE.xls workbook should do what you want.

http://www.smokeylake.com/excel/text_write_program.htm

You can save as fixed width *.txt file.

Note the caveat about not padding fields.


Gord Dibben MS Excel MVP