Exporting from Excel to a Fixed Length Flat File

[bearie]

Is there a way to Export from Excel to a Fixed Length Flat File?

We are currently creating file in Excel and copying data in Access database.
Then exporting from Access to a Fixed Length Flat File.

This is very cumbersome and opens up for error.

Thank you for your help,
Barbara J. Miller

 

[Markus L]

Is there a way to Export from Excel to a Fixed Length Flat File?

File > Save As... > Save as type: Formatted Text (Space delimited)
(*.prn)

Caution:
- insufficient column width leads to truncated cell content
- only the active worksheet is saved.
Make all columns wide enough and you are safe!

 

[bearie]

Markus,

Thank you so much!!

We have spent many months looking for this feature. Could not find it in
Help.

barb

 

[bearie]

We worked with this a bit, and found it works great to a point.

We have files that are 1,200 characters that need to be saved as fixed
length flat files.

Out only idea here is to find a print driver that will accept really large
paper (we tried 17X11 and was still not large enough) to make this work.

Any other ideas?

 

[Dave Peterson]

Saved from a previous post:

There's a limit of 240 characters per line when you save as .prn files.

You have a few choices (try against a copy of your worksheet):

I'd either 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.)

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]

=========

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm