sunilvedula

hi cory.,

was unable to locate the attachement in the mail sent to you. i am forwarding the attachement. Can we copy all the data from the excel and process each row at a time. is it possible using the concept of tsv or anything else. it is not allowing to upload as it says not allowed to upload this type of file .

account no udf no trans amt trandt financharge account secure overlimit fees

123456789 451245 150 March 24, 2008 0.25 2 30

Generally not. For instance a web browser is the same pixel dimensions on either resolution but I have one I'm working on right now where inexplicably the beginning of a sometimes invisible progress indicator moved over one pixel on a different machine. Strange things will make minor differences so hitting the middle of a button is usually easy but grabbing the edge of something or finding a black pixel in text can be tricky. Usually I write a simple macro to check the display to make sure everything's aligned before running the real macro and save the locations in the local registry instead of the macro. Also I've made some macros that will scan an area with Get Pixel Color tests or have it scan a region instead of looking for an exact pixel. For instance the progress indicator I told you about that moved... I know scan a line of pixels looking for the edge of the progress indicator. As soon as it sees the edge it will bail. But a good safeguard is to add a Get Pixel Color after your mouse move and before clicking. If it's a gray button you're looking to click and the pixel color is something else you're in trouble.

the problem i face is it is neither a button nor a text box or a drop down. it is a text on the webpage. the controls are useless (or i dont know how to use them properly) but it did not make a huge difference lucky me . But it is making a issue where the web page(chordiant) uses java. the positioning of the text changes with the screen resolution. Any ideas??

sorry for the delay in replying. was stuck with a project. I think what you suggested makes sense. will try and let u know

Hi,

Cant put the screen shots. sorry! ALso i wanted to know if we chage the screen resolution will the data that is being captured with mouse would that get affected. IF i change screen from TFT OR LCD OR NORMAL SCREEN would that effect the macro (mouse pointer) captures the data. Pls advised ASAP! If it does any way to avoid it.

Meh... you can figure that out. But if it's possible to have an interruption you should instead output a record for each iteration. This is often the safe bet.

sorry but i had it done. As soon as i put this post i tried saving the file after each iteration though to a diff name.txt. it did work wonderfully. now even if my macro stops or somehting goes wrong i will have the data till the time it is processed.

JUst adding to that we can also i think save the output directly to an excel sheet. i will be trying it now to see if it happens without any erros. it wuld save a lot of time and rework. Anyhow it helped me a lot!

HI ,

just wanted to know how do we declare env. variables and how do we use them? can they be used like normal variables.

HI cory,

your tsv files is working exceedingly good and thanks a lot!. I have an issue here. As you already know i am running for around 5-10hrs the macro which repeats in the loop and it saves to the file only when it completes. Now if my system gets hanged due to some reasons or it gets stuck then i have no data that is saved and i have to repeat the entire process again which is wasting lot of time. i have read the one post which u said to use breaks but i am bit confused. Can we see something like it saves the data that is processed to the file after let us say 30min and then runs and adds to the one it is saved after another 30min. Advise please

yeah i have seen it and i have tried it. That is when i had the question. Somehow or i dont know but when i imported the macro into my macro it never worked.

hi

just wanted to know if i am upgrading my macro express which is presently 3.2 to the lates version will my macros work ? will there be any problems to use those macros. just wante to make sure. please reply ASAP

hey that was great!. this is the same question that was in my mind (saving a single variable) and use it later. thanks kevin.

Did you try CTRL+A to select all and then parse that? How about File > Save as and save it to a plain HTML file?

i think i will need to explain in detail. we cannot do parse or ctrl a the reason solely because the macro will be used realtime ( the associates will be processing cases). so what i was trying to do was make the associate work easy. we have a tool called chordiant which we process cases and it is on a server which is accessed through internet. we use mouse most of the time . now what have i done is automated most of the stuff. But the only problem i am facing is that when the macro or associate click on a button (the control is not being recognized, so i use tabs to reach the buttons) a screen opens within the main screen and i cannot make the macro recognize tht screen. i have tried various ways but no result. so what i was trying was to find a unique letter and do a ctrl f and copy it and do that until it finds it and then let the macro work again. this is because the amount of time it takes for the screen to come depends on various factors (like computer speed, inernet speed etc....). it can vry from 2 sec to 20 sec. if it comes early within 2 sec and i make the macro wait (by giving time delay) then the associate feels it is a waste of time and he can do it faster. So, this is the situation i am stuck in and i dont know how to make the subwindow (or subscreen) to recognize so that the waiting time for my macro becomes less and my macro will work more efficiently.

Here's another 'trick' I learned. Initially my impulse was to do everything in one big macro. Later, due to inevitable glitches and such, I learned to break it into stages. So, for instance, I might have one macro that snatches the screen capture and dumps it into a text file with the account number in a folder. Then I might have another macro that parses those files and yet another that runs off and does something with the parsed information. This also allows more than one machine to run at the same time as well as isolating problems. Also you can have Macro 1 running while you develop macro 2.

very true. will try to implement in those lines in future of my projects. will discuss in detail later.

If you're doing a lot of records and you have access to more than one computer you might consider parallel tasking. I once wrote a harvesting macro that ran on several machines at many different locations and coordinated their work thru FTP. You see I had a deadline and after I did the initial macro I did the math and realized I was going to be short about 3 days! So to cut the time I ran it on several machines. Worked great.

Need to learn still how to work through FTP. if i know will also help me a great deal as i am hadling 5-6 projectes in a day/month.

Oh, here's another trick. If running unattended I add little emails to myself of progress and such. But what if the macro halts because one time it couldn't find C1? Well I set the option to clear error messages after 10 seconds and then have a macro that scheduled to run every minute that takes a screen shot and emails it to my email account and sends an SMS message to my cell phone. Oh, and disables itself or hits a pause. If it's a huge macro with thousands of iterations it should run for a long time preventing any other macro from running. So if the other macro is allowed to run then that means the first one blew up. This way I know right away if I have a problem instead of hours later when I check myself.

Cory i know of the option (i have seen it) of send emails but never used it. will try it . also just intereseted how do we send SMS to our phones (which if possible) will be of great help as u said.

Also cory the 11hr macro has run well without any erros and the result was delivered. it actually ran for more than 12hrs.

hi cory,

i will try to send you some screen shots. what have i done is i captured around 4000 acccounts which were to be processed and the result to be found (case ids) captured in MESample. passed it tosamplesource. then made it process. it is being processed for 11hrs. today when i get back to office maybe i will get the result. will let u know today once i am in office.

it was in error tht so many posts got posted ! SORRY! my internet was slow

HI all,

i have tried tab, arrow down etc.. with keyboard to caputre data on a web page (kind of application) but the only thing it allows me is to move mouse and copy with mouse. I can do ctrl f (find option) to find the letter associated with it like :

Cardholder Name: sunil. the find option will take me to cardholder name but i dont know how to make the mouse or anything to realise to make it goto that location as tab or arrow wont work.

any ideas?????/ Also by the way controls are not working in those areas. i tried beneat mouse, control focussed etc... but to no vail.

Hey marco polo ,

thanks for that!. though it doesnt solve all problems at least the other users are aware that they dont need to meddle with it and will be careful. i run my macros for 11hr or more and i wont be there (logoff) and other users come in and hence this helped me .

saying that if it could be available(locking keyboard and mouse) it would be great! and also some may to (probably when the macro ends or i dont know just thinbking) make the keyboard else mouse to get disabled and active.

hi cory,

it is of great help and i cant tell u how imp it is. it jsut changed the way i look at it. Few doubts:

how many cells or number can it process and store at a time.

can i process a txt file containg 1000 number /accounts or what is the max limit for the tsv file. now i can accomplish huge tasks with ease. thanks for ur tutorial. it was GREAT!

NEXT is the problem with the repeat until is not resolved. we have screen like DOS which work on mainframes and we call them sessions or pega.

now when i input the account number and if the account number is found (assume) then it has to search for ordltr and then copy the case id adjacent to it and paste it. i tried various options as i mentioned earlier. strip cr/lf worked fine but alas! it didnt when i changed the computer (used on another system). i will try again and let u knwo the result asap.

HI cory,

sorry for the earlier post. I had to change few thins like the location of the source file etc.. and it works fine.

Hi cory,

checked your example. Maybe i am wrong but it does not display the results. It shows only the original data but not the square roots. u have the copied the results to t2 and then appended it to t3 (along with t1). saved it to text file and clipboard.

the text file for display only shows t1 and when i try using the clipboard in excel no data shows up. ????

Then you should hire me post haste. Seriously I wish I had more time but I have things I need to get done today. Having said that paying customers always are prioritized before volunteer work...

Hi cory,

you are as usual wonderful. i wish i could hire you than ask u for simply advice. i need to wait a little more time till i get that official authority to be able to pay or else get a hike (or promotion which is due).

Now if I were you I would not use Excel but rather CSV or better TSV files and the ASCII or Text File Process command. You will need to take about 5 minutes to learn it but once you do you will realize a huge benefit that will reap instant rewards. Also I would output the results to a TSV file as well instead. Again it takes a few minutes to understand it but once you do you will realize it's a far superior way to accomplish the task. If you are interested start by looking at the help for these commands and I'll explain how you can create these as your output as well. I think you have Joe's book as well? He has a good writeup there. Also check out my page with the section on Tab CRLF and Hex editing.

 

How this would work is you would use the TSV file as a list of account numbers and each repeat will pick up the next number in the var. Then you look it up in your terminal emulators, grab what you need, and push it back out to a results TSV or even simple text file.

 

I'm guessing you're using a terminal emulator? The last bank I was working with was using a TE to access the mainframe and we simply polled the entire screen with a CTRL+A and parsed out the data we needed. Worked slick and fast especially since we didn't have to keep switching back to Excel.

 

You know I was thinking of creating a simple macro to demonstrate how to do this. Hmmmm.... perhaps now is the time...

will try to understand the funcitonin of CSV or TSV. JOe's book has not still reached me. waiting eagerly for that. but if you see my early post u will understand where i am stuck with. and as u said it is true that converting to ASCII doesnt help much here.

If u have time look into the post and advice (will pay u later once i get my dues; )

But guys on a serious note i am indebted to all of you for ur help withut which i would not have reached where i am today. thanks for all .

Not without more or clearer information. Give us something to go on.

hi kevin,

i find this stupid but alas! that is what happened. i changed the computer from one desk to another. the logic i wrote

repeat until t1="ordltr"

texttype <arrow down> <arrowdown> arrowdown>

select the case id shiftd<aroow riht> <arrowright> etc...

clipboard copy

save the variable to t1

repeat end

once it finds the ordltr it would just copy the adjacent case id and go to excel and paste it else it would go on searching for the ordltr.

i used strip cr/lf for the variable and it worked. as i said when i changed the computer (i went to another desk) IT DOESNT WORK.

let me give u another example. in another project of mine i was copying data from e-champs(application of ours) and one particular data it was copying but was not inputting in excel. i found this funny and absurd as it worked last time.

so i checked in another computer and to my surprise it did work. what makes these thing happen like this.

??????

Hi Kevin,

First of all thanks for that input. IT worked. Now comes the issue. i changed the system and used the macro with the same logic it doesnt work. it makes no sense to me. i will be testing on other system to check if it is that kind of issue. i am surprised and confused. i used the Variable Modify String: Strip CR/LF from %T1% and it did work but when changed the system it gives error. Can u help me understand!

Thanks cory for that insight. never knew that. that is quite helpful in all my projects which i am doing. will keep that in mind

Hi cory,

i am sorry if it got u confused. i am working on a mainframe sessions. i have an number (approx 4000 numbers/accounts)) in an excel sheet. with that number i need to pull some cased ids in the sessins. i do an a command in sessions (ifnd) which takes me to those case ids. now i need to locate the case id which has the name "ordltr". what my macro does is that it copies the "ordltr" onto a clipboard t1. NOw i was asking to compare if t1= "ordltr". if not to go to the next case id and do the same until it finds. Once it finds i want it go to the left of the screen (for which i used arrow left) to copy the case id where the ordltr is found. NOw the case id which it copied it has to go to excel and paste the id next to the number it copied.

what i have written is

repeat until t1="ordltr"

texttype <arrow down> <arrowdown> arrowdown>

select the case id shiftd<aroow riht> <arrowright> etc...

clipboard copy

save the variable to t1

repeat end

then i asked to copy the case id by going left in session and then go to excel and paster beside the number.

this one did not work because it went on repeating in the loop.

i also tried using t1<>"ordltr" thinking maybe the logic was not right. then it was copying the next case id but not the one beside the ordltr. it was going one step down and copying.

then i tried to convert the ordltr to ascii which gave me 79 and i asked to do the same then it was copying the next case id but not the one beside the ordltr. it was going one step down and copying.

i need to get it done by today EOD. see if you can reply fast as i am waiting for your guidance.

HI All,

stuck with something imp . my loop when i use repeat until does not work. i am giving the code, if any ideas pls forward. I tried for around 5.5 hrs unable to resolve it. Also converted the text to ascii but still did not work. I have initally tried saying Repeat until T1="ORDLTR" it was copying that text and going down without stooping. Can anyone help me out

<TEXTTYPE:<SHIFTD><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><SHIFTU>><MSD:500><CLIPC><TVAR2:01:03:><TMVAR2:01:01:00:000:000:><ASCIIV:2:0:1>
<REP3:06:000001:000002:0002:0:01:79><TEXTTYPE:<ARROW DOWN><ARROW DOWN><ARROW DOWN>><MSD:500><TEXTTYPE:<SHIFTD><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><ARROW RIGHT><SHIFTU>><MSD:500><CLIPC><TVAR2:01:03:><TMVAR2:01:01:00:000:000:><ASCIIV:2:0:1><ENDREP>