Using a function inside of a loop

[madrocks87]

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 November 27, 2014 by madrocks87

[TheSaint]

@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.

Edited November 27, 2014 by TheSaint

[madrocks87]

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

[TheSaint]

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 November 27, 2014 by TheSaint

[madrocks87]

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

[TheSaint]

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.

[TheSaint]

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 November 27, 2014 by TheSaint

[232showtime]

@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.

very very nice... I love it..

[madrocks87]

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

[TheSaint]

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 November 27, 2014 by TheSaint