Jump to content
Sign in to follow this  
l3ill

Two Consecutive Loops

Recommended Posts

l3ill

Hi All,

Having a problem with 2 pieces of code that work fine themselves but wont work together. There is probably an easy fix but I haven't been able to find it.

The script should fill out a PDF form automatically; the first step is a position counter (first line 1, second line 2 and so on): the next step is a value from an attached excel sheet. and then drop to the next line to continue.

I used a for..next for both of them but when used together only one of them work at a time, and neither of my limiters work correctly, should stop typing when the excel numbers run out or when it reaches the 25th position.

Here is the code I've been testing with, any help would be greatly appreciated

p.s. I did get it working by counting down the numbers first and then going back to fill in the excel values but this way causes other problems.

#include <Excel.au3>

;code for testing
run( "notepad.exe","") ;actually this data should go in a PDF Form but this works for testing purposes
WinWaitActive( "Untitled" )
Sleep(1000)
;end code for testing

$oExcel=_ExcelBookAttach("22022010001.xlsx", "FileName")
;always has different amount of values, but for testing let's say it has 12



;------------------------------------------------
For $c = 1 To 100 ; this is the problem...I cant get this to work together with the other counter
If $c = 25 Then ExitLoop ;doesnt stop at 25
;If $c = 25 Then ExitLoop if ;the counter reaches 25 the loop should stop and contiue with the script
;------------------------------------------------

For $i = 1 To 100
$sCellValue = _ExcelReadCell($oExcel, $i, 1)
If $sCellValue = "" Then ExitLoop ;if the excel file runs out of numbers the loop should stop and contiue with the script

Sleep(100)
Send ($c)
Sleep(100)
Send("{TAB}")
Sleep(300)
Send ($sCellValue)
Sleep(100)
Send("{TAB}")
Sleep(100)
Send ("1")
Send("{ENTER}")

Next
Next

;coded for testing
Sleep(2000)
Send("{ALT}")
Send("F")
Send("X")
Send("N")

Share this post


Link to post
Share on other sites
dani

I don't see two loops? In fact, don't even see 1 finished loop :lol: Please post at least both of the loops then we can have a look. Now you only posted a small snippet.

Oh wait, there are 2 loops. Next time could you please use indentation? It's not used for no reason you know.. I'll have a look now :mellow:

-- edit

As expected, two loops like below work fine, and stops @ c = 25:

For $c = 1 To 100
  ConsoleWrite("c: " & $c & @CR)
  If $c = 25 Then ExitLoop
  For $i = 1 To 100
    ConsoleWrite("i: " & $i & @CR)
  Next
Next

Run it to see it for yourself.

As for the 'stop at last cell', you might want to use the following to determine the last cell by directly using the Excel COM object:

$sLastCell = $oExcel.Cells.SpecialCells($xlCellTypeLastCell).Address()
$iLastCol = StringRegExpReplace($sLastCell, "\$(.*)\$(.*)", "\1")
$iLastRow = Number(StringRegExpReplace($sLastCell, "\$(.*)\$(.*)", "\2"))

The replacement is done as the result of .Address is a string like "$xx$yy" where xx is the last column and yy is the last row. Remember this gives the last cell in the workbook. You can probably also call this on a specific column but I never needed that -- you can dive into the Excel Object Model Reference if you want to find this out :(

With $iLastRow you can use a loop like:

For $i = 0 To $iLastRow
  ; Read from column X and row $i here -- I assume you know which column you need
Next

-- edit2

I just had another look at it and you cannot call it (succesfully) on a single column. It always gives the last cell in the whole sheet. If you, however, have a column with no gaps (= empty cells) you can determine the last cell using:

$sLastCell = $oExcel.Range("A1").End(-4121).Address() ; -4121 = $xlDown -- Use Range("B1") for column B, "C1" for C, etc.
$iLastRow = Number(StringRegExpReplace($sLastCell, "\$(.*)\$(.*)", "\2"))
Edited by dani

Share this post


Link to post
Share on other sites
l3ill

Hi Again,

and thanks for your interest dani. Unfortunately I couldn't get any output with ConsoleWrite. Send does seem to be serving it's purpose well here in any case.

please have another look at the code, I have tried to indent as you requested:

#include <Excel.au3>

;code for testing
run( "notepad.exe","") ;actually this data should go in a PDF Form but this works for testing purposes
WinWaitActive( "Untitled" )
Sleep(2000)
;end code for testing

$oExcel=_ExcelBookAttach("22022010001.xlsx", "FileName")
;always has different amount of values, but for testing let's say it has 12

For $c = 1 To 100
    Send ($c)
    If $c = 25 Then ExitLoop
    Sleep(300)
    Send("{TAB}")
    Sleep(300)
    For $i = 1 To 100
    $sCellValue = _ExcelReadCell($oExcel, $i, 1)
    If $sCellValue = "" Then ExitLoop
    Send ($sCellValue)
    Sleep(300)
    Send("{TAB}")
    Sleep(300)
    Send ("1")
    Send("{ENTER}")

    Next
Next

;coded for testing
Sleep(2000)
Send("{ALT}")
Send("F")
Send("X")
Send("N")

and see below what it should look like: (left column is the 1-100 counter, middle column is the data from excel sheet)

1 0128881192        1
2 9825861154    1
3 0414P91441        1
4 9825858411        1
5 0320882531        1
6 9935836241        1
7 0128881097        1
8 9935836214        1
9   9803801806      1
10  0034821468      1
11  0414P91441  1
12  0414P91441      1

There must be some way to code this as:

Loop 1 then loop 2 then

Loop 1 then loop 2 and so on until

25 times or Excel cell is empty (exit)

The way have it the one loop seems to be canceling the other out...

Share this post


Link to post
Share on other sites
dani

Ah ok, I guess I now understand what you want. I wonder why you want it to quit at $c = 25 but have the For loop from $c = 1 To 100? Why not just let it run till 25 then :mellow:? Also, you need only 1 loop for your purpose, unless I again don't understand it. Try:

For $iCount = 1 To 25
  Send($iCount)
  ; ... some other commands
  $sCellValue = _ExcelReadCell($oExcel, $iCount, 1)
  If $sCellValue = "" Then ExitLoop
  Send ($sCellValue)
  ; ... some other commands
Next

This reads a maximum of 25 entries from the Excel file, or less if the Excel file contains less. Is this what you wanted? In your code, it will take $c = 1 followed by $i = 1 To 100, then $c = 2, again followed by $i = 1 To 100, etc. You have a total of 100 * 100 = 10.000 combinations of $c and $i. In my code it only executes the code in the loop 25 times, which is what you want as I understand it now.

ConsoleWrite works in SciTE, do you use a different editor? It outputs data to the - surprise! - output panel, which you can toggle using F8. It should, however, open automatically.

Edited by dani

Share this post


Link to post
Share on other sites
Fubarable

To the original poster: What does your actual output look like and how is it different from the desired output?

Share this post


Link to post
Share on other sites
l3ill

Sweeet!! That was it!

Thanks a bunch dani !!!

I am still unsure why it works, but it's enough that it works for now....thats why I'm a noob I guess:huh2:

@Fubarable - The desired output was similar to the above text which I typed myself to show what I was looking for.

The actual output was either; the counter was working, 1,2,3 in the left column. or the ean's were working (counting down) middle column.

but never both.

But they do now !:mellow:

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  

×