Export excel file to txt

G

Guest

Hi,
I have to submit salary details to bank in txt format with following format.

1st field with 16 char length, 2nd field with 3 char, 3rd with 8 char, 4th
with 1 char, 5th with 17 char & 6th with 30 char.

All columns are to be left aligned except the 5th column which is to be
right aligned.

Sample data is given below:

A B C D
E F
0408040100002788 INR 0408 C 20878.00 Amrendra Pratap Singh
0408040100002798 INR 0408 C 26097.00 Bhan Prakash
0408040100002830 INR 0408 C 28470.00 Chandrakant Chandramouli
0408040100002832 INR 0408 C 36062.00 Gajanan Pranjpe
0408040100002833 INR 0408 C 9490.00 Hari Singh
0408040100002840 INR 0408 C 28944.00 Jitender Rana
0408040100002842 INR 0408 C 39843.00 K K Dutt

Can any one help me with a vb code to export my excel data to txt in the
prescribed format.

thanks
 
D

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]
 
T

tommaso.gastaldi

Hi,
I have to submit salary details to bank in txt format with following format.

1st field with 16 char length, 2nd field with 3 char, 3rd with 8 char, 4th
with 1 char, 5th with 17 char & 6th with 30 char.

All columns are to be left aligned except the 5th column which is to be
right aligned.

Sample data is given below:

A B C D
E F
0408040100002788 INR 0408 C 20878.00 Amrendra Pratap Singh
0408040100002798 INR 0408 C 26097.00 Bhan Prakash
0408040100002830 INR 0408 C 28470.00 Chandrakant Chandramouli
0408040100002832 INR 0408 C 36062.00 Gajanan Pranjpe
0408040100002833 INR 0408 C 9490.00 Hari Singh
0408040100002840 INR 0408 C 28944.00 Jitender Rana
0408040100002842 INR 0408 C 39843.00 K K Dutt

Can any one help me with a vb code toexportmy excel data to txt in the
prescribed format.

thanks
Click to expand...

Try this one:

DataTime Documentation
http://cam70.sta.uniroma1.it/DataTimeDocumentation/

DataTime Universal (downloadable)
http://cam70.sta.uniroma1.it/DataTimeUniversal/
 
E

Earl Kiosterud

OP,

I don't see the original post, only this reply, so I'm replying to the reply.

You want what's called a "Fixed-width" file. Each field has a prescribed count of
characters. No delimiters (such as commas). Read "Space-delimited (prn)" and "Fixed-width"
at http://www.smokeylake.com/excel/textfiles.htm for two ways to accomplish this.

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...

-----------------------------------------------------------------------
 
Top