Input/Output text file and fill in variables

[cfunky]

Hello.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
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.<o></o>
<o> </o>
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. <o></o>
<o> </o>
The input text looks like the following:<o></o>
<o> </o>
send "VAR1VAR2"<o></o>
wait system<o></o>
send "enter"<o></o>
send "VAR3"<o></o>
wait system<o></o>
send "03<TAB>MSCEFF 04/10/10 INCREASE"<o></o>
send "VAR4"<o></o>
send "TABVAR5"<o></o>
send "VAR6"<o></o>
wait system<o></o>
send "03/10/09VAR7"<o></o>
send "VAR8"<o></o>
<o> </o>
<o> </o>
The table in Excel will look like the following:<o></o>
<o> </o>
<TABLE style="MARGIN: auto auto auto 4.95pt; WIDTH: 616pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 5.4pt 0in 5.4pt" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=821><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR1<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR2<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR3<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR4<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR5<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR6<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR7<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; BACKGROUND: #a5a5a5; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

VAR8<o></o>​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 1"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

574<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

4565<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

C31<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

R4T5<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

AUB<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

RENTON<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

WAY<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

SOUTH<o></o>​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 2"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

547<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

2134<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

T51<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

R8X8<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

FED<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

SEATTLE<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

POINT<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

WEST<o></o>​

</TD></TR><TR style="HEIGHT: 15pt; mso-yfti-irow: 3; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

345<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

4543<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

G61<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

R4FT<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

RED<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

ISSAQUAH<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

DRIVE<o></o>​

</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 5.4pt; WIDTH: 77pt; PADDING-RIGHT: 5.4pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" vAlign=bottom width=103 noWrap>

EAST<o></o>​

</TD></TR></TBODY></TABLE><o> </o>
<o> </o>
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. <o></o>
<o> </o>
When it is complete the output text looks like the following:<o></o>
<o> </o>
send "5741234565"<o></o>
wait system<o></o>
send "enter"<o></o>
send "C31"<o></o>
wait system<o></o>
send "03<TAB>MSCEFF 04/10/10 INCREASE"<o></o>
send "R4T5"<o></o>
send "<TAB>AUB"<o></o>
send "RENTON"<o></o>
wait system<o></o>
send "03/10/09WAY"<o></o>
send "SOUTH"<o></o>
send "5472134"<o></o>
wait system<o></o>
send "enter"<o></o>
send "T51"<o></o>
wait system<o></o>
send "03TABMSCEFF 04/10/10 INCREASE"<o></o>
send "R8X8"<o></o>
send "TABFED"<o></o>
send "SEATTLE"<o></o>
wait system<o></o>
send "03/10/09POINT"<o></o>
send "WEST"<o></o>
send "3454543"<o></o>
wait system<o></o>
send "enter"<o></o>
send "G61"<o></o>
wait system<o></o>
send "03TABMSCEFF 04/10/10 INCREASE"<o></o>
send "R4FT"<o></o>
send "TABRED"<o></o>
send "ISSAQUAH"<o></o>
wait system<o></o>
send "03/10/09DRIVE"<o></o>
send "EAST"<o></o>
<o></o>
If anyone could help me figure this out I would greatly appreciate it. Thank you for your time.<o></o>

 

[Norie]

Is that 'pseudo' code?

 

[cfunky]

Is that 'pseudo' code?

None of it is code. Just what the input text, output text and excel spread sheets look like before and what they need to look like after.

 

[Norie]

Sorry I don't understand.

What is this? Code/data/pixiedust?

send "VAR1VAR2"<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
wait system<o:p></o:p>
send "enter"<o:p></o:p>
send "VAR3"<o:p></o:p>
wait system<o:p></o:p>
send "03<TAB>MSCEFF 04/10/10 INCREASE"<o:p></o:p>
send "VAR4"<o:p></o:p>
send "TABVAR5"<o:p></o:p>
send "VAR6"<o:p></o:p>
wait system<o:p></o:p>
send "03/10/09VAR7"<o:p></o:p>
send "VAR8"

 

[cfunky]

This is a sample of the text I would have in the Input.txt file. It is a script that I will ultimately run on my system. It has no relevance other than where I have <VAR1>VAR1 I want it replaced with what is in the VAR1 column of the excel spread sheet.

 

[Norie]

Sorry but you've really lost me now - what you've posted has no relevance?

Do you want to create some sort of file that you can use as a script for another application with values/variables based on your data in Excel?

 

[jeffreybrown]

Norie, just so you know this question is active and cross posted at...

cfunky, it is not an expressed rule not to cross post, but now you have two different people looking at your post and they may overlap each other...not a good practice.

 

[cfunky]

Norie, just so you know this question is active and cross posted at...

cfunky, it is not an expressed rule not to cross post, but now you have two different people looking at your post and they may overlap each other...not a good practice.

Sorry I must have missed that on the posting rules.

 

[cfunky]

Sorry but you've really lost me now - what you've posted has no relevance?

Do you want to create some sort of file that you can use as a script for another application with values/variables based on your data in Excel?

Yes I would like to create a script. I will have first run a "sample" script in my system and then replaced the variables with VAR1, VAR2 and so on. Once I have done that I will populate an excel spread sheet with many rows of variables that I need to have filled in using the sample script as a template. The output then will have the final script I can use in my system. I posted no VBA code as I am not sure how to do it. I just posed what my sample script text looked like, what the excel spread sheet with the varibles looked like and finally what I need the final script to look like. I apologize if I am not explaining this properly.

 

[Norie]

There doesn't seem to be any logic that connects the data in Excel to the script you want to create.

In fact there seems to be data in the script that isn't in Excel and probably vice versa.