The attached workbook contains a macro (CvtToText) that does what you describe. You may want to change variable FilePath to define the location of the text file. If you have any questions let me know.
Tom On Wed, Apr 1, 2009 at 4:43 PM, Cesar Delanoval <cdelano...@gmail.com>wrote: > > 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
CvtToText.xls
Description: MS-Excel spreadsheet
