I wrote a couple of weeks ago about converting Excel to .Txt and Mr. Jeffries was very helpful in providing the solution. Now I have another request. Using the same excel data as my previous example, I need to create another .txt with a different variation.
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 The file will contain headings which are standard for Quickbooks files. These are as follow !TRNS,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM,MEMO !SPL,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM,MEMO !ENSTRNS Imediately right after the above headings the following lines will be listed. notice that there are 2 lines for each transaction. TRNS,PAYMENT,MM/DD/YY,UNDEPOSITED,669,170.00,3957, SPL,PAYMENT,MM/DD.YY,RECEIVABLES,669,-170.00,3957, ENDTRNS And the process repeat itself until all the data on the Excel sheet is finish. To explain the transactions above. The date MM/DD/YY is the actual date which is on column 3, (year needs formatting from MM/DD/YYYY to MM/DD/YY ) 669 above is the account number field which is contained in column 2 (no formatting is required) 170.00 represents the amount as listed on column 5 (no formatting required). On the second line for the Split the amount will be negative, example: -170.00 3957 represents the data on column 4 (no formatting required) The process needs to repeat itself until no more data is found in the rows. Below find a sample of an actual file. !TRNS,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM,MEMO !SPL,TRNSTYPE,DATE,ACCNT,NAME,AMOUNT,DOCNUM,MEMO !ENDTRNS TRNS,PAYMENT,04/06/09,UNDEPOSITED,669,170.00,3957, SPL,PAYMENT,04/06/09,RECEIVABLES,669,-170.00,3957, ENDTRNS TRNS,PAYMENT,04/06/09,UNDEPOSITED,668,170.00,899, SPL,PAYMENT,04/06/09,RECEIVABLES,668,-170.00,899, ENDTRNS TRNS,PAYMENT,04/06/09,UNDEPOSITED,623,170.00,4361, SPL,PAYMENT,04/06/09,RECEIVABLES,623,-170.00,4361, ENDTRNS 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 ------------------------------------------------------------------------------------- -~----------~----~----~----~------~----~------~--~---
