Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export excel file to txt
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export excel file to txt
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/groups?threadm=015...0a% 40phx.gbl shaji wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export excel file to txt
On 19 Mag, 08:50, shaji wrote:
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 Try this one: DataTime Documentation http://cam70.sta.uniroma1.it/DataTimeDocumentation/ DataTime Universal (downloadable) http://cam70.sta.uniroma1.it/DataTimeUniversal/ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Export excel file to txt
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... ----------------------------------------------------------------------- " wrote in message oups.com... On 19 Mag, 08:50, shaji wrote: 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 Try this one: DataTime Documentation http://cam70.sta.uniroma1.it/DataTimeDocumentation/ DataTime Universal (downloadable) http://cam70.sta.uniroma1.it/DataTimeUniversal/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to export excel file in dbf format | Excel Discussion (Misc queries) | |||
Excel file export problems | Excel Discussion (Misc queries) | |||
Export Excel File to Ascii | Excel Discussion (Misc queries) | |||
Export notepad file to Excel | Excel Discussion (Misc queries) | |||
Export excel file to semicolon delimited text file | Excel Discussion (Misc queries) |