Solved: Input/Output text files fill in variables

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.

Dear cfunky,

I see you just joined. This is a great forum and I hope you enjoy it. I have met some utterly great folks here, who will go out of their way considerably to help anyone who is 'stuck' with a concept, problematic code, or advanced worksheet functions/formula construction that quite oft boggle me.

That said, most 'answerers' are coders who are hard working folks trying to make an honest living, and then find a bit of time to help others, as well as hopefully pick up a trick or two here or there.

Thus, when one submits an issue to resolve, one is asking for free help, wherein the answerer will receive at most, a thank-you.

As such, there is a reasonable expectation for the 'questioner' to 'play fair' and not take advantage of folks. There is a reasonable expectation of genuineness.

You posted at: ExcelForum

After being advised about cross-posting, you wrote, "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."

reference the cross-post at MrExcel

...where you responded, "Sorry I must have missed that on the posting rules."

I would echo Jeff at MrExcel somewhat in stating that while cross-posting is not against the rules, there is a reasonable expectation that you will give the first answerers a fair shot at providing a solution first, and then provide links at both ends (that is, in the new thread at another site, as well as in the original thread/site), so that answerers are not duplicating efforts and wasting their time.

Rather than stick by what you stated in the other two threads, you are now cross-posting here, again with no advisement(s) or link(s), which I find disingenuous at the minimum.

Now I am just a member, and you are not obliged to follow this advice, but I would strongly urge you read about cross-posting thoroughly (as I think Ken Puls explains it awfully nicely) as well as the forum rules.

Mark

Are you saying that the input is a text file, as will be the output? Do you have an example input file?

Wht do you have multiple pairs per row, why not one pair per row?

Hi cf,

Well it took me a few hours...

and 3 Xposts while I'm at it!!!!!!???!!!?!?!?!?!?!?!?!?!?

So:

Here's a working example (VBA private) but type in as many rows as you want (limited to 8 cols/vars) and see if it does what you want or if you can break it.

Click on my sig or name to check me out if you want a paid solution... and maybe not forgiveness but perhaps support...

Damn<.>

Quote:

---

Originally Posted by rbrhodes

Hi cf,

Well it took me a few hours...

and 3 Xposts while I'm at it!!!!!!???!!!?!?!?!?!?!?!?!?!?

So:

Here's a working example (VBA private) but type in as many rows as you want (limited to 8 cols/vars) and see if it does what you want or if you can break it.

Click on my sig or name to check me out if you want a paid solution... and maybe not forgiveness but perhaps support...

Damn<.>

---

Well I certainly have learned my lesson regarding posting at multiple places. What I think is being missed by everyone is that I initially posted on a total of 3 websites. I did them all within minutes of each other....not spread out. Therefore your assumption that I was informed about the "rules" of cross posting and then going ahead and doing it anyways is false.

Having said that I appreciate your efforts. It is certainly closer than I was able to get although not quite there. Between the 3 sites I have been beat down and will certainly think twice before asking for help in the future. I guess I figured that since I am extremely helpful to those I encounter (albeit at work and not on internet forums) that I could expect to find others out there with a similar willingness to help. I guess it is on to plan B and start studing VBA and figure out the answers myself. Normally I would do that first but under the situation I figured that I wouldn't be to proud to ask for help. Big mistake and lesson learned.

A piece of advice for the future....give the new person a chance. What was being done may be out of ignorance instead of malice. As was my case. If I had been politely informed I could have fixed the mistake. Instead people just pounced on the fact that I had cross posted and leapt to the conclusion that I did it on purpose. I guess these forums are not the place for me.

Hey Seattle (?)


I noticed you quoted me. I guess that was little much.

Sounds like you learned the lesson a little _too_ well but then so did I albeit in the other direction...

The main problem here is that many, many people before you have X posted. Some out of ignorance (in the true sense of the word) it's true but that still means they did not take the time to read the rules or at least think about what they were doing. e.g. the law says 'Ignorance is no excuse." and consequently wasted a lot of time for a lot of people

I've done thousands of hours for free and there is _nothing_ more frustrating than spending hours on a project for free (or on spec) only to find out it was wasted.

I understand the logic that posting the same problem many places may help you get a solution. Sorry if we came off rude, I certainly didn't mean to and I don't believe anybody else meant to either. (I'll check the other sites <grin>)

Perhaps every forum should have an intial expkanation of 'Why Not X Post' as the first thing you see. Would have saved you some grief, for sure.

So if you accept this in lieu of apology -from me for sure and I presume most, if not all the others, what's wrong with what I posted? Or do you need help or just need to see the code (or both).

Please see attached and post back...

What you must understand is that whilst it might seem logical to you to post at multiple forums, getting a free workforce to look at your problem, it means that many of us may start looking at a problem, working n a solution, only to find later that you have got a solution elsewhere. We do this for free, our time is valuable too. It was not beyond the wit to have posted a link everywhere that you also posted elsehwere, I for one would not even have bothered reading your post if you had, saving my time.

I personally make no apologies about how you got beaten up, get over it, and learn from it.

Quote:

---

Originally Posted by xld

I personally make no apologies about how you got beaten up, get over it, and learn from it.

---

I also endorse these comments. There are sufficient links to either rules regarding cross posting or examples in forums of participants getting warnings about cross posting, that in my opinion, to argue ignorance of the rule is to be without much validity.

Dear cfunky,

While I agree with the above, I am not much at excuses from myself either. I erred.

In reference to:

Quote:

---

Originally Posted by cfunky

...What was being done may be out of ignorance instead of malice. As was my case....

---

I re-checked the times (for the thread at MrExcel, I am not a member of Excel Forum, thus cannot see a meaningful time) and I errored in my perception of the time frames. While I do not oft make "jumping the gun" type mistakes, I most certainly did here.

Quote:

---

Originally Posted by GTO

Rather than stick by what you stated in the other two threads, you are now cross-posting here, again with no advisement(s) or link(s), which I find disingenuous at the minimum.

---

I apologize to you for this statement. It was inaccurate and unfair.

Had I correctly caught the post times, I would still have advised other members, though a simple 'cross-posted at xxxx' would have sufficed. I of course would not have made the above, rather blunt statement.

I do know whether it will help, but I would relay that when I joined VBAX, I made mistakes; mine being in PM'ing a member near to death right after joining. It took several increasingly blunt hints for me to 'clue in', and I probably earned a well deserved spot on a member or two's ignore list for a while, but I was certainly forgiven and can tell you that I now consider that member a friend (albeit long-distance), whom I would no doubt enjoy having beers with and 'shooting the bull' should opportunity ever arise.

Again, my apologies for misunderstanding the timeframes,

Mark

First I want to make sure that everyone knows that I accept the mistake I made. I have learned from this situation. I just felt that as a new person to this type of thing that I could have been given a chance instead of being pegged as an uncaring individual who cares nothing for the rules or doesn't respect the time and effort of others. Nothing could be further from the truth. While I may not be able to help a lot of people here I certainly try to "pay forward" any help I receive.

GTO: Thank you for your efforts thus far. I am attaching an Excel file that has what I hope is a more concise explaination of what I am trying to accomplish. It contains a sample Input.txt, Output.txt and the Template Excel spread sheet with explaination. Also contained is an Excel spreadsheet that has the vba code I was able to create. It will reference my input and output texts although I am unable to get it to "prompt" me for the files. I had to explicitly state where the files were located as I lack the knowledge on how to get it to use dialogue boxes to prompt me. Where I was getting stuck was the middle part of this process and that is to use the input text as a template to fill in variables.

I cannot open 2007 wb's at home, and the battery-powered abacus I lovingly refer to as my POL (Poor Ol' Laptop) is not about to accept the converter. If I get a chance, I'll convert the wb's at work tonight where 2003 (and the converter) is available :-)

Mark

Have a look...

[edit] This will open and save as *.pls but you can put in any name you want in the dialogue box and as it's just a text file the results will open in xl just fine

Thank you rbrhodes! I have been playing with it today. I modified it to have 100 variables and am testing it to make sure it works. I will get back to you on Monday. Thank you again for your efforts.

Ok Seattle,

standing by...

Thank you rbrhodes! After playing with it I was able to modify it to use 100 variables which should be enough for any future needs. I really appreciate the time and effort you put into it. I will now be able to get my work done more [FONT='Verdana','sans-serif']efficiently[/FONT]!