I need to know if anyone can help with write a macro or script that will convert an Excel file to a .txt file after changing some of the formats on the data.
this is what I have. 1 excel sheet with rows (they could vary) and 5 columns. The columns do not have headings. Column 1 = 13 digit field ( no changes needed for the field ) Column 2 = an alpha numeric field which can be from 1 digit to 13 digits. Column 3 = date in the MM/DD/YYYY format Column 4 = a numeric field that could be from 1 digit to 10 digits Column 5 = an amount with a 2 decimal representing a dollar amount ( could be from .01 to 999999.99 ) Now, I need to write a macro that will format the fields as follows Column 1 = no changes needed Column 2 = take whatever information on the field and fill to the left with zeros to complete a 13 digit alpha-numeric field. Column 3 = convert the date from MM/DD/YYYY to YYDDMM ( no hyphens or slashes ) Column 4 = take whatever information and pre-fill with zeros (to the left) to complete a 10 digit numeric field. Column 5 = take the amount in the format 99999.99 and eliminate the decimal to convert the amount to 8 digit numeric field without decimals. The process needs to repeat itself until no more data is found in the rows. Using the same order of the columns as described, put all the fields (after conversion) in the same order without spaces and convert (or save) the file into a .txt format. The end result should look like the lines below, where each number represents the column 11111111111112222222222222333333444444444455555555 11111111111112222222222222333333444444444455555555 I know that this might sound like a lot of work, but I figure it is a good challenge for the gifted people on the group. I will be eternally grateful for any help on this subject. Thanks --~--~---------~--~----~------------~-------~--~----~ ------------------------------------------------------------------------------------- Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
