Making a txt file with excel data range

[SSB]

Hello,
Im trying to create a macro that will copy a data range from different tabs in an excel workbook to a txt file. So far, this is what I have.

Please Login or Register  to view this content.

This allows me to create the file, and copies the data from 1 of the tabs. Im working on getting the two other tabs to copy to the same notepad, underneath the text from the tab previously copied. How would I do this?

[Leith Ross]

Hello SSB,

Welcome to the Forum!

I changed your macro to allow you append the sheets of choice and allow each sheet to copy a different range if you need to, Be sure to change the Worksheet names and Ranges in the macro to match what you are using.

Please Login or Register  to view this content.

[SSB]

Thank you Leith for the response. I changed the ranges and the sheet numbers, but nothing will copy over to the text. I placed the macro in sheet 1, and also tried "This workbook", with the same result. Am I doing something wrong?

[Leith Ross]

Hello SSB,

It would help if I could see your workbook because I am not sure where the problem is.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[SSB]

I uploaded a copy of the workbook (excel TEST) and a copy of the text (notepad TEST) file that I create. Let me give you a run down of what im trying to accomplish (Failing so far). The tabs "cash account message", "Custody account message" and "relationship message" needs those formulas (From lines A5-A39 in the cash and custody, and A7 in the relationship tab)to be copied over to a text file.

I will then need the macro to be able to add a -L in the first tab (New accounts entry)under portfolio abbreviation (right now it is displayed TC43T6) and also the custody account number. The two fields need to look like this: TC43T6-L and TC43-L. I would need it to loop the macro and copy the same information as before to the same txt file that was created without the -L.

Lastly I need to the same as above with a -T. The txt file should look like the one I attached when it is complete. Thnaks so much for you help, I have been working on this for a while and can only seem to get pieces to work, not the whole thing.

[Leith Ross]

Hello SSB,

I did not forget about about you. This project is quite complex due to the file's structure. There were many challenges to finding the values and then changing them. It seems here the exception is the rule. While the macro runs correctly on the sample data provided, the real test with be with all the data. The attached workbook has all the changes added.

The macro operates on the three sheets you mentioned: "cash account message", "Custody account message" and "relationship message". The initial values for the Portfolio Code Name "PORTCODENAME" and the Current Account Number "CURRACCTNO" are taken from the first data row on "cash account message". The values are modified and copied to the text file "C:\Myfile.txt". Each sheet is checked for a change in the "CURRACCTNO". If there is no change then the previous value is used.

Changing the values presented a unique set of challenges. There were quite a few rules that had to applied when parsing them. The values had to be separated by parentheses, or spaces, or a combination of the two. To make things more interesting, there instances were the value is combined with a currency code. The suffixes "-L", "-T" had to be inserted between the value and the currency code.

To handle these problems, I enlisted the help of Regular Expressions scripting language and the Dictionary object. Regular Expressions is used to parse and replace complex patterns of text. The Dictionary is used as a lookup for the Currency Codes. Both are very fast and efficient tools. A new sheet has been added named "Currency Codes". This contains all the current country codes and currency names. This range is read into the Dictionary to allow fast checking of values that may have currency codes attached.

The code for the Regular Expressions will not make sense to you as it is a separate scripting language. Most advanced user's don't even know this exists and have trouble using them. Don't worry about it, it does what it supposed to. If you do have questions, I will answer them as best as I can to make them understandable.

All the VBA code is now in "Module1". All other code referring to "MakeTextFile" has been removed. You only need call the macro "MakeTextFile" once and all three sheets will read, modified, and saved to the text file "C:\Myfile.txt". Unfortunately, I can not post the code because it exceeds the character limit for posting. You will have to view it in the workbook.

[SSB]

Thanks a ton Leith. I got everything working as intended now.

[Leith Ross]

Hello SSB,

That's good to hear. Did you use my solution or code your own?

[SSB]

I used yours and played around with it, so a mix of both.

[SSB]

I wouldnt have been able to do it without your help, so thanks so much. Im new at this stuff.

[Leith Ross]

Hello SSB,

I'll keep my fingers crossed it remains trouble free. If you do encounter anything, let me know.

[SSB]

Sounds good!