Sign in to follow this  
Followers 0
madrocks87

Using a function inside of a loop

10 posts in this topic

#1 ·  Posted (edited)

Hey guys, total newbie here, hoping you guys could lend me a hand. I've done some Googling, but I think I am having trouble phrasing what I am trying to do.

Basically, I have a task that involves copying data from a spreadsheet and copying it into another sheet on a daily basis. The two sheets are structured exactly the same every day, so I figured it would be a good candidate for an AutoIT script. Essentially, I am trying to create a loop that will go through each column and copy out the data that I need. While creating the loop, I found that there were several sections where 4-10 of the steps were exactly the same, before going to a new line and having to select different cells/areas. What I would like to do is create functions for these repetitive steps, call them, then continue on with the loop. Example pasted below:

Function:

Func Excel()
   $i=0

 while $i = 0
   Send("^c")
   $i=$i+1
   sleep(300)
WEnd

while $i = 1
   MouseClick("left", 450, 1060, 1)
   $i=$i+1
   sleep(300)
WEnd

while $i = 2
   Send("^v")
   $i=$i+1
   sleep(300)
WEnd

while $i = 3
   Send("{RIGHT}")
   $i=$i+1
   sleep(300)
WEnd

while $i = 4
   MouseClick("left", 450, 1060, 1)
   $i=$i+1
   sleep(300)
WEnd
EndFunc
 
 
Then, after writing the function, I am trying to call it in a loop as seen below:
 
while $i = 1
   MouseClick("left", 450, 1060, 1)
   $i=$i+1
   sleep(300)
WEnd

while $i = 2
   Send("^v")
   $i=$i+1
   sleep(300)
WEnd

while $i = 3
   Send("{RIGHT}")
   $i=$i+1
   sleep(300)
WEnd

while $i = 4
   call("excel")
WEnd
EndFunc

while $i = 5
   MouseClick("left", 450, 1060, 1)
   $i=$i+1
   sleep(300)
WEnd
 
ETC...
 
 
Essentially, the issue I am having, is that the function is successfully called and executes properly, but the loop stops at the function and will not continue on to the next step ($i = 5 in this case).
 
That is the gist of it, hopefully I explained it adequately. I am just starting out on this, so I'm sure the code could be tightened up, and I appreciate any time and responses I receive.
 
Thanks,
Madrocks

 

Edited by madrocks87

Share this post


Link to post
Share on other sites



#2 ·  Posted (edited)

@Madrocks - When you post code, please use the provided code quotes, which is to the immediate left of the normal quotes button in the post editor. This makes it far easier for us to read. Don't highlight your code and then use the button, as you need to tell the code editor, it is AutoIt code ... use copy and paste.

That said, I don't see where you are calling a function in a loop, just a lot of steps within a function.

Admittedly I have only taken a quick look at your code, and not taken it apart.

You should use MsgBox's throughout your code, to check on variables etc, to test your logic flow.

Here is a very simple representation of using a Loop and a function, where the row number for instance keeps increasing by one.

Global $i, $row

For $i = 1 To 5
     $row = $i
     MyFunction()
Next

Exit

Func MyFunction()
     do this to $row
     do this to $row
     do this to $row
     etc
EndFunc

P.S. Welcome to the forum. :D

Edited by TheSaint
1 person likes this

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

I added the code quotes and separated them a little bit better above. I see what you are saying with the counting row numbers. Let me see if I can iterate it a little bit better below.

I essentially am trying to go row by row to perform this task, but there are certain blocks where it is the same steps, something like this:

1

2

3

A

B

C

D

E

F

4

5

6

A

B

C

D

E

F

7

8

9

etc

What I am trying to do is essentially create a function for A-F, then write it as

1

2

3

Call function

4

5

6

Call function

etc

in order to save time writing out all of that duplicate code.

Please let me know if that helps.

Thanks,

Madrocks

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

I'm still not seeing the representation of your function within a loop, just loops within a function.

I added an Exit etc to try and make the logic a little clearer.

When the process gets to Exit your script/program quits.

Row number always remains the same number during the function stage of the process, incrementing each time the function is called during the For...Next loop.

Edited by TheSaint

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

while $i = 4

   call("excel")

WEnd

EndFunc

Line 35 of the second box above, during the while loop I try to call the function "Excel" that I created in the first box. Perhaps I am not using the right word, I am just trying to nest several commands into a single line so that I don't have to type all of the duplicate code.

Thanks,

Madrocks

Share this post


Link to post
Share on other sites

If that was there all along, then sorry, I missed it.

I never use Call (well not in recent history anyway), so perhaps it just didn't register when I went zipping through.

You can just use - excel()

Saves on typing.


AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Are you copying data from one Excel spreadsheet to another?

If so, then I'm left wondering why you are using AutoIt for most of this, and not just running a VBA macro within Excel?

If need be, you can use AutoIt, to run that macro, easily enough.

Far better to do things within Excel, using its own VBA commands, especially as you can avoid using the Clipboard and mouse clicks, which can be prone to errors.

Edited by TheSaint

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

@Madrocks - When you post code, please use the provided code quotes, which is to the immediate left of the normal quotes button in the post editor. This makes it far easier for us to read. Don't highlight your code and then use the button, as you need to tell the code editor, it is AutoIt code ... use copy and paste.

That said, I don't see where you are calling a function in a loop, just a lot of steps within a function.

Admittedly I have only taken a quick look at your code, and not taken it apart.

You should use MsgBox's throughout your code, to check on variables etc, to test your logic flow.

Here is a very simple representation of using a Loop and a function, where the row number for instance keeps increasing by one.

Global $i, $row

For $i = 1 To 5
     $row = $i
     MyFunction()
Next

Exit

Func MyFunction()
     do this to $row
     do this to $row
     do this to $row
     etc
EndFunc

P.S. Welcome to the forum. :D

very very nice... I love it..

1 person likes this

ill get to that... i still need to learn and understand a lot of codes graduated.gif

Correct answer, learn to walk before you take on that marathon.

Share this post


Link to post
Share on other sites

It's not actually Excel, its a proprietary application that I can't really get too much into the details of. This particular task is pretty much just copy and pasting, and using AutoIT to automate all the clicks will take a ~1.5 hour task down to about ~10 minutes. I actually have the script mostly written and working, I just really want to trim some lines of code out of the script before I present it to anyone. 

So the format you're saying would be the following?

while $i = 4
- excel()
WEnd

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

Yes that is correct ... obviously without that dash in front of excell()

(I just use 5 spaces etc to give indents to code in the code browser.)

As I did in my example above, I had some of the necessary work, like the row number, done in the main loop that calls the function you want repeated. Any differences between rows (lines, etc) should be done in the main loop, before the function is called. If you make you variables Global, as I did, probably at the beginning of your script, then they are passed around very effectively. The function should only really work on one row (line) at a time.

I know you are using While...Wend, but it may be more effective to use For...Next as I did. But that depends on the rest of your code. The former can problematic and if you make a coding error, and $i never equals 4, then you can have the process stuck in a loop. That said, I use both methods all the time.

If you are troubleshooting, and using a MsgBox is an issue, as it can be, and using the SciTE console is not an option, then I find, that writing values from variables to a text or INI file, at key moments, can be very useful.

Where a MsgBox can be an issue, is where your active window then loses focus, and is no longer active.

Most code using Clipboard and Mouse functions, often require you to re-establish the active window from time to time, using WinActivate etc. A lot of issues can be traced to that.

Edited by TheSaint

AutoIt.4.Life Clubrooms - Life is like a Donut (secret key)

Make sure brain is in gear before opening mouth!
Remember, what is not said, can be just as important as what is said.

Spoiler

What is the Secret Key? Life is like a Donut

If I put effort into communication, I expect you to read properly & fully, or just not comment.
Ignoring those who try to divert conversation with irrelevancies.
If I'm intent on insulting you or being rude, I will be obvious, not ambiguous about it.
I'm only big and bad, to those who have an over-active imagination.

I may have the Artistic Liesense ;) to disagree with you. TheSaint's Toolbox (be advised many downloads are not working due to ISP screwup with my storage)

userbar.png

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0