fixed length file

[Steve S]

I need to tansfer a data file I have created in excel to another program.
I'm required to transfer the file with a 80 byte record length in the ASCII
format. I only have data out to byte 58, position 59 thru 80 must be blank.
How can I save the file with a 80 byte record length? (I'm saving to a space
delimited file .prn)

 

[Dave Peterson]

Saved from a previous post:

There's a limit of 240 characters per line when you save as .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]

 

[Chip Pearson]

Use fixed length strings. Something like the following. Change the folder
name "C:\Test" and the file name "C"\Test\Output.txt" to appropriate names.

Sub AAA()
Dim FNum As Integer
Dim Rng As Range
Dim S As String * 80 '<<<< S is fixed at 80 chars
FNum = FreeFile()
If Dir("C:\Test", vbDirectory) = vbNullString Then
MkDir "C:\Test"
End If
Open "C:\Test\Output.txt" For Output Access Write As #FNum
For Each Rng In Range("A1:A5")
S = Rng.Text
Print #FNum, S
Next Rng
Close #FNum
End Sub

Note that the length of the string (80) must be hard-coded in the procedure.
See http://www.cpearson.com/excel/SizeString.htm for more information about
fixed length strings and how to create a string of a specified length at run
time.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email on the web site)