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>

 

[cfunky]

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.

I have seen this done in Excel so I am sure that the fault lies in my explaination. I am running a script in Power Term for my as/400 system. I use it to make automated changes to customer accounts. For example I record a script when I enter in an account number, go to the customer information area and then enter in a new address. When I am done recording this script I can view it in text file format. Where I see the customers account number I replace it with VAR1 then where I see the house number portion of the address I replace it with VAR2, then where I see the street number portion I replace it with VAR3 and so on and so forth. This text file is what I labled as "input" in my initial example and I showed a copy of what the text looks. I then have an excel spread sheet that has a huge list of cusotmers account numbers and new addresses in it. The vba code that I saw was able to import the sample script text into excel. It then took the sample script and the table of new account numbers and addresses and was able to create a new text file that replaced the VAR1, VAR2 type variables with the new account numbers and addresses. So once the table of new account numbers and addresses was in place all that needed to be done was to click a macro button in excel and it would prompt you for the "input" text and the "output" text and it would fill in all the data.

 

[GTO]

Now cross-posted at VBAX as well...

Mark

 

[cfunky]

Now cross-posted at VBAX as well...

Mark

Before I was aware of the cross posting rules I had posted this at a total of 3 sites (including this one). No further searching of other sites is necessary. I have already been reprimanded for the offense. Although I have come to the conclusion that my thoughts of trying to get help from multiple places was a good idea. The only help I have received thus far is for people to report that it was posted in multiple places.

 

[cfunky]

This has been solved thanks to rbrhodes at vbaexpress.

http://www.vbaexpress.com/forum/showthread.php?p=213105