Two Consecutive Loops

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

expandcollapse popup

#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")

[dani]

I don't see two loops? In fact, don't even see 1 finished loop 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

-- 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 March 7, 2010 by dani

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

[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 ? 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 March 7, 2010 by dani

[Fubarable]

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

[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 !