increment variables in a loop

[sunilvedula]

Hi All,

 

My macro works presently like copying some data from one application and comming to excel and pasting the data and then going back to the application.

 

example: there are 10 users whose data is in the application. Each user can have 1 page or more pages in the application. Each screen in the application displays 10 records and if there are more it gives an option "more" at the end of the screen which i made the macro recognize and it moves to the next screen.

 

Presently my application copies all the data from the screen under user 1 and pastes and then comes back to the application and if it finds more it will go to the next page and copy and paste the data again. it will do this until it finds no " more" then it goes to the next user with number 2.

 

My problem is this takes lot of time. i want it to copy all the data in all the pages and then paste it at one go. we can do this using variables but how to tell the macro to increment variables (as no of pages is not constant) and also even if it copies let us say 10 varaibles how does it paste all the data with x varaibles in the excel sheet.?

 

HOpe i can have some ideas! If it is not clear pls let me know so tht i can try to explain again.

[MacroPolo]

I would recommend using the "Repeat Until" command.

 

The key is to Repeat until some condition is met (you run out of "more" pages for that user).

 

You can use either a string or integer variable, whichever you prefer.

 

Your code might look something like this:

 

Variable Set N1 to 0

Repeat Until N1 = 1

<copy page>; <assign text to String variable>

If Text contains "more" data

{Code here to process data on specific user}

Else

{If "no more", set N1 to 1 to break out of Repeat. (or can use the 'break' command).

End If

End Repeat

 

 

Note: You can put another repeat around the code above to loop through each user.

 

Hope this helps!

[Cory]

In situations like this, if I understand you correctly, I will break things down into separate processes. For instance the first macro will harvest the data then the second macro will massage it to my liking. If you do this you can create a file to store all the data from the harvester and life is cake after that. I find that sometimes it makes life and timing a lot easier. It's something to consider.

 

I'm not sure what your different data screens look like so I'll assume you have a screen that is one record with several fields all of which you want to snatch up. Often newbie users will Alt+Tab back and forth between the apps and this works fine for a quickie but if you want to push any real data you need to have a more sophisticated approach.

 

I wrote a harvesting macro for a bank a while back that's a prime example. It was grabbing order record from a terminal emulator as a way of harvesting the records from an antiquated mainframe system. Here we went screen by screen grabbing data and what I would do is build a TSV file in a variable. BTW a Tab Separated Value (TSV) is like a CSV but with tabs. The first step was to create the special ASCII characters for tab and CRFL. You can steal my macro to do that here. In my macro the Tab is T96 and the CRLF is T95. So let's say I went thru the first page and harvested T1 thru T10. I would then append my accumulator variable T11 with %T1%%T96%%T2%%T96%%T3%....%T10%%T95%. You see? I just created one line of a TSV file. Now once you have repeated this all the times you need you can dump the entire contents of T11 to a file or to your clipboard. If you use the clipboard all you have to do is paste it into Excel and it will all be formatted properly for you in columns and rows. If you go the file route you can simply open it with Excel.

 

The benefit here is that you're not bopping back and forth between apps and you're not writing to disk. For all practical purposes all the processes besides tabbing between fields in your source app is instantaneous. Once I turned this corner with my macro development I never looked back. There are no timing issues and life is simple and predictable. If this isn't sinking in your noggin just let me know and I can write a simlple example for you.