Input/Output text files fill in variables

**cfunky** · 05-03-2010, 07:10 PM

Hello.

I am trying to duplicate a piece of vba code that I have seen in action that will greatly help me in my job. Since I have seen it in action I know it is possible but so far my attempts to duplicate it have come up short. I do have a work around but it is labor intensive and takes me longer than I can afford to finish.

In a nutshell I need to have the user fill out an Excel spread sheet table with different variables. They will then press a macro button that will prompt the user for the “input” and “output” text locations. The code will then look at the input text and replace the variables (which looks like <VAR1>, <VAR2> and so on up to 8 variables) with those from the table the user populated. It will then write that information to the output text. Once that has been done for the first row it will then loop and continue to do that for each row in the table specified by the user. For each row it will need to append the results to the output text. The code will need to be flexible enough that each time it is run the user may have a different number of rows used in the table. Below is an example of what I am looking for.

The input text looks like the following:

send "<VAR1><VAR2>"
wait system
send "enter"
send "<VAR3>"
wait system
send "03<TAB>MSCEFF 04/10/10 INCREASE"
send "<VAR4>"
send "<TAB><VAR5>"
send "<VAR6>"
wait system
send "03/10/09<VAR7>"
send "<VAR8>"

The table in Excel will look like the following:

VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8
574 4565 C31 R4T5 AUB RENTON WAY SOUTH
547 2134 T51 R8X8 FED SEATTLE POINT WEST
345 4543 G61 R4FT RED ISSAQUAH DRIVE EAST

So <VAR1> in the input text would be replaced with the data in row 1 column A, <VAR2> is replaced with the data in row 1 column B and so on and so forth. Once all of the variables has been filled in it would write the results to the output text. Once it has completed the first row it will loop and continue to process all rows until it is has come to the end of the data. Each row needs to append to the output text.

When it is complete the output text looks like the following:

send "5741234565"
wait system
send "enter"
send "C31"
wait system
send "03<TAB>MSCEFF 04/10/10 INCREASE"
send "R4T5"
send "<TAB>AUB"
send "RENTON"
wait system
send "03/10/09WAY"
send "SOUTH"
send "5472134"
wait system
send "enter"
send "T51"
wait system
send "03<TAB>MSCEFF 04/10/10 INCREASE"
send "R8X8"
send "<TAB>FED"
send "SEATTLE"
wait system
send "03/10/09POINT"
send "WEST"
send "3454543"
wait system
send "enter"
send "G61"
wait system
send "03<TAB>MSCEFF 04/10/10 INCREASE"
send "R4FT"
send "<TAB>RED"
send "ISSAQUAH"
wait system
send "03/10/09DRIVE"
send "EAST"

If anyone could help me figure this out I would greatly appreciate it. Thank you for your time.

**Leith Ross** · 05-03-2010, 07:37 PM

Hello cfunky,

Welcome to the Forum!

You will get answer to your question by providing before and after examples of the data. If you have a workbook that is even better. Before posting your workbook, please change or remove any information that is confidential or sensitive in nature. If your workbook is large, you should zip it first. This will prevent you from exceeding the file limits.

**jeffreybrown** · 05-03-2010, 07:40 PM

Post does not comply with forum rules...

x- post

8. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

**Leith Ross** · 05-03-2010, 07:51 PM

Hello cfunky,

Jeffrey is correct about cross posting. Since he was kind enough to provide the link to the cross post, I won't close this post - this time. In the future if you forget, I will close the post. Please be specific and provide clear examples. It is better to error on the side of too much information than not enough.

**cfunky** · 05-03-2010, 08:00 PM

Originally Posted by Leith Ross

Hello cfunky,

Jeffrey is correct about cross posting. Since he was kind enough to provide the link to the cross post, I won't close this post - this time. In the future if you forget, I will close the post. Please be specific and provide clear examples. It is better to error on the side of too much information than not enough.

I didn't realize that posting in more than one location was a bad thing. I thought it would help my chances of getting help. I now understand why this is not a good thing and I will only post at one location in the future. Thank you for the understanding.