Loop until a cell in Excel contains a different value

dm83737 · February 20, 2009

I am trying to loop a certain list of steps until a value in Excel changes, then it would start over and run the loop again with the new value until it changes again (I hope that makes sense). I think I am going to have to use the Do... Until loop statement, but I can not seem to come up with the proper formatting of that statement to make it do what I want.

Here is the piece of code I talking about:

; Tells the loop to run from row # to #
    For $iE1 = $iB1 To $iC1
       ; Each policy number in that column is now referred to as the variable $sCellValue
       $sCompanyCode = _ExcelReadCell($oExcel, $iE1, 1)
       $sPolicyNumber = _ExcelReadCell($oExcel, $iE1, 2)
       $iClicks = _ExcelReadCell($oExcel, $iE1, 4)
       $sTotalDebits = _ExcelReadCell($oExcel, $iE1, 6)
       $sTotalCredits = _ExcelReadCell($oExcel, $iE1, 7)
       $sFuncCode = ("A")
       $sTran1Code = _ExcelReadCell($oExcel, $iE1, 9)
       $iTran1Amount = _ExcelReadCell($oExcel, $iE1, 10)
       $iTran1Date = _ExcelReadCell($oExcel, $iE1, 10)
       $sTran2Code = _ExcelReadCell($oExcel, $iE1, 9)
       $iTran2Amount = _ExcelReadCell($oExcel, $iE1, 10)
        
        
       ; Procedures
    ; Goes to the ACAS screen
        WinActivate("The ADMINISTRATOR")
        WinMove("The ADMINISTRATOR", "", 0, 0)
        MouseClick("left", 110, 65, 1)
        Sleep(25)
        Send("ACAS")
        Sleep(25)
        Send($sPolicyNumber)
        Send("{TAB}")
        Sleep(25)
        Send($sCompanyCode)
        Sleep(25)
        Send("{F11}")
        Sleep(3000)

    Do
       ; Creates and enters the information in the ACAS blanks
        WinActivate("The ADMINISTRATOR")
        Mouseclick("left", 312, 540, $iClicks)
        Mouseclick("left", 80, 308, 1)
        Send($sFuncCode)
        Send("{ENTER}")
        Send("{TAB}")
        Send($sTran1Code)
        Send("{ENTER}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send($iTran1Amount)
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send($sTran1Date)
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send($iTran1Amount)
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
    
    Until $sPolicyNumber ???

So it would loop the Do... Until statement until the $sPolicyNumber value changes. Then it would go back up to the For $iE1 = $iB1 To $iC1 at the top

Here's the entire code sheet:

; Includes and Options
#include <Excel.au3>
#include <Array.au3>
#include <GUIConstantsEx.au3>
#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <StaticConstants.au3>
#include <WindowsConstants.au3>

Opt("GUIOnEventMode", 1); Change to OnEvent mode
Opt("GUIResizeMode", 1); Keeps the GUI window from being resized by the user

; Variable List
Global $iB1, $iC1, $iE1
Global $sCompanyCode, $sPolicyNumber, $iClicks, $sTotalDebits, $sTotalCredits, $sFuncCode, $sTran1Code, $iTran1Date, $sTran2Code, $iTran2Amount
Global $sMessage, $vPath, $sFilePath1, $oExcel, $fVisible, $sPolicyNumber, $sCompanyCode, $bDelete
Global $Form1_1, $iRowStartInput, $iRowEndInput, $sRowStartLabel, $sRowEndLabel
Global $OKButton, $CancelButton

; --------------------------------------------------------------------------------------------------------------------------------------------;

; Makes sure the ADMINISTRATOR is open so test can proceed
MsgBox(64, "The ADMINISTRATOR", "You must be logged into The ADMINISTRATOR in order to proceed", 30)

If WinExists("The ADMINISTRATOR") Then
    Sleep(100)
Else
    MsgBox(16, "Error", "The ADMINISTRATOR is not open.  Exiting...", 5)
    Exit
EndIf

; Choose the Excel Spreadsheet you would like to reference; opens after the GUI interaction
$message = "Please choose the file with the policy listing"

$var = FileOpenDialog($message, @DesktopCommonDir & "\", "Excel Spreadsheet (*.xls)", 1 + 4)

; --------------------------------------------------------------------------------------------------------------------------------------------;

; This creates the window GUI form
#Region ### START Koda GUI section ### Form=c:\documents and settings\sadmm\my documents\my dropbox\work\gpin\form1.kxf
$Form1_1 = GUICreate("ACAS Entry Form", 349, 185, 315, 167)

; Tells which row is the first row to read (aka the beginning of your list)
$sRowStartLabel = GUICtrlCreateLabel("Which row would you like to start reading from?", 72, 24, 261, 19)
GUICtrlSetFont(-1, 9, 400, 0, "Arial")
$iRowStartInput = GUICtrlCreateInput("", 16, 24, 33, 23)
GUICtrlSetFont(-1, 9, 400, 0, "Arial")

; Tells which row is the last row to read (aka the end of your list)
$sRowEndLabel = GUICtrlCreateLabel("What is the last row to read from?", 72, 80, 187, 19)
GUICtrlSetFont(-1, 9, 400, 0, "Arial")
$iRowEndInput = GUICtrlCreateInput("", 16, 80, 33, 22)
GUICtrlSetFont(-1, 9, 400, 0, "Arial")


; Creates the OK and Cancel buttons on the GUI
$OKButton = GUICtrlCreateButton("OK", 73, 136, 75, 25, 0)
GUICtrlSetFont(-1, 9, 400, 0, "Arial")
GUICtrlSetOnEvent($OKButton, "OKButton")
$CancelButton = GUICtrlCreateButton("Cancel", 209, 136, 75, 25, 0)
GUICtrlSetFont(-1, 9, 400, 0, "Arial")
GUICtrlSetOnEvent($CancelButton, "CancelButton")

; Shows the GUI window to the user
GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

; Dictates the behavior of the window
While 1
    Sleep(1000); Sets window to idle
WEnd

Func OKButton(); When you press the 'OK' button
    $iB1 = GUICtrlRead($iRowStartInput);
    $iC1 = GUICtrlRead($iRowEndInput);
    GUISetState(@SW_HIDE, $Form1_1)
    Run_Excel($iB1, $iC1)
EndFunc  ;==>OKButton

Func CancelButton()
   ;Note: at this point @GUI_CTRLID would equal $CancelButton,
   ;and @GUI_WINHANDLE would equal $mainwindow
    Exit
EndFunc  ;==>CancelButton

Func SpecialEvents()
    Select
        Case @GUI_CtrlId = $GUI_EVENT_CLOSE
            Exit

        Case @GUI_CtrlId = $GUI_EVENT_MINIMIZE

        Case @GUI_CtrlId = $GUI_EVENT_RESTORE

    EndSelect
EndFunc  ;==>SpecialEvents

; --------------------------------------------------------------------------------------------------------------------------------------------;

Func Run_Excel($iB1, $iC1)
   ; Opens the Add_Contract script
    $sFilePath1 = $var
   ; Sets the spreadsheet to appear or stay invisible
    $fVisible = 1
    $oExcel = _ExcelBookOpen($sFilePath1, $fVisible)
   ;Sets the Sheet to the actual script and not other sheets within the script
    _ExcelSheetActivate($oExcel, 1)

   ; Tells the loop to run from row # to #
    For $iE1 = $iB1 To $iC1
       ; Each policy number in that column is now referred to as the variable $sCellValue
       $sCompanyCode = _ExcelReadCell($oExcel, $iE1, 1)
       $sPolicyNumber = _ExcelReadCell($oExcel, $iE1, 2)
       $iClicks = _ExcelReadCell($oExcel, $iE1, 4)
       $sTotalDebits = _ExcelReadCell($oExcel, $iE1, 6)
       $sTotalCredits = _ExcelReadCell($oExcel, $iE1, 7)
       $sFuncCode = ("A")
       $sTran1Code = _ExcelReadCell($oExcel, $iE1, 9)
       $iTran1Amount = _ExcelReadCell($oExcel, $iE1, 10)
       $iTran1Date = _ExcelReadCell($oExcel, $iE1, 10)
       $sTran2Code = _ExcelReadCell($oExcel, $iE1, 9)
       $iTran2Amount = _ExcelReadCell($oExcel, $iE1, 10)
        
        
       ; Procedures
    ; Goes to the ACAS screen
        WinActivate("The ADMINISTRATOR")
        WinMove("The ADMINISTRATOR", "", 0, 0)
        MouseClick("left", 110, 65, 1)
        Sleep(25)
        Send("ACAS")
        Sleep(25)
        Send($sPolicyNumber)
        Send("{TAB}")
        Sleep(25)
        Send($sCompanyCode)
        Sleep(25)
        Send("{F11}")
        Sleep(3000)

    Do
       ; Creates and enters the information in the ACAS blanks
        WinActivate("The ADMINISTRATOR")
        Mouseclick("left", 312, 540, $iClicks)
        Mouseclick("left", 80, 308, 1)
        Send($sFuncCode)
        Send("{ENTER}")
        Send("{TAB}")
        Send($sTran1Code)
        Send("{ENTER}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send($iTran1Amount)
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send($sTran1Date)
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send($iTran1Amount)
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
        Send("{TAB}")
    
    Until $sPolicyNumber =
        
        
        

    Next

   ; Saves the cuesheet to the CUE's directory
   ;WinClose("Untitled - Notepad")
   ;WinWaitActive("Notepad", "Do you want to save")
   ;Send("!y")
   ;Send(@DesktopCommonDir)
   ;Send("No GPIN policies")
   ;Send(".txt")
   ;Send("{ENTER}")

    _ExcelBookClose($oExcel, 1, 0)
    
    GUISetState(@SW_SHOW, $Form1_1)

EndFunc  ;==>Run_Excel

Thanks for your help

Melba23 · February 20, 2009

dm83737,

One way to do this would be to set a flag variable to the original value of your cell. Then you could loop until the cell value changed. The code for the Do...Until loop would look like this:

Read cell
Store current value of cell into flag

Some kind of loop
    ...
    ...
    Do
        ...
        ...
        Read cell
    Until flag <> cell; you keep looping if the value has not changed
    flag = cell
Return to the top with new cell value

I think that should do the trick.

M23

ToyBoi · February 20, 2009

would be much easier to do in vba

dm83737 · February 20, 2009

OK, I tried to do what M23 said, and maybe I am making this harder than it really is, but I am stuck as to how I would get the Do... Until to move on to the next row of the excel spreadsheet. I am attaching a brief pic of what the spreadsheet looks like just so you can get the idea of what I am trying to do.

I hope I interpreted your flag suggestion correctly.

Here is the code as I have it now:

For $iE1 = $iB1 To $iC1
      ; Variables involved in successfully filling out an ACAS acreen
           $sCompanyCode = _ExcelReadCell($oExcel, $iE1, 1)
       $sPolicyNumber = _ExcelReadCell($oExcel, $iE1, 2)
       $iClicks = _ExcelReadCell($oExcel, $iE1, 4)
       $sTotalDebits = _ExcelReadCell($oExcel, $iE1, 6)
       $sTotalCredits = _ExcelReadCell($oExcel, $iE1, 7)
       $sFuncCode = ("A")
       $sTran1Code = _ExcelReadCell($oExcel, $iE1, 9)
       $iTran1Amount = _ExcelReadCell($oExcel, $iE1, 10)
       $iTran1Date = _ExcelReadCell($oExcel, $iE1, 12)
       $sTran2Code = _ExcelReadCell($oExcel, $iE1, 17)
       $iTran2Amount = _ExcelReadCell($oExcel, $iE1, 19)
       $sPolicyNumber = $iV1;  <====================I ADDED THIS PER YOUR ADVICE
        
        
       ; Procedures
    ; Goes to the ACAS screen
        WinActivate("The ADMINISTRATOR")
        WinMove("The ADMINISTRATOR", "", 0, 0)
        MouseClick("left", 110, 65, 1)
        Sleep(25)
        Send("ACAS")
        Sleep(25)
        Send($sPolicyNumber)
        Send("{TAB}")
        Sleep(25)
        Send($sCompanyCode)
        Sleep(25)
        Send("{F11}")
        Sleep(500)
        Mouseclick("left", 312, 540, $iClicks)
        Mouseclick("left", 80, 308, 1)
        
        Do
        ; Creates and enters the information in the ACAS blanks
            WinActivate("The ADMINISTRATOR")
            Sleep(25)
            Send($sFuncCode)
            Send("{ENTER}")
            Send("{TAB}")
            Sleep(25)
            Send("CC")
            Send($sTran1Code)
            Send("{ENTER}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Sleep(25)
            Send($iTran1Amount)
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Sleep(25)
            Send($iTran1Date)
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Sleep(25)
            Send($iTran1Amount)
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
            Send("{TAB}")
        ; <<<<<<<<<<<DOES SOMETHING NEED TO GO HERE TO MAKE IT GO ON TO THE NEXT ROW?>>>>>>>>>>>>
        
        Until $sPolicyNumber <> $iV1;  <======== I ADDED THIS PER YOUR ADVICE

    
    Send("{F3}")
    Sleep(3000)
    
    ; Goes to the DSCM screen
        WinActivate("The ADMINISTRATOR")
        WinMove("The ADMINISTRATOR", "", 0, 0)
        MouseClick("left", 110, 65, 1)
        Sleep(25)
        Send("DSCM")
        Sleep(25)
        Send($sPolicyNumber)
        Send("{TAB}")
        Sleep(25)
        Send($sCompanyCode)
        Sleep(25)
        Send("{F11}")
        Sleep(500)
        
        MsgBox(262192, "Screenshot Message", "Please take a screenshot of the DSCM <if necessary> and press OK to move on.  ", 0)
        

    Next

Melba23 · February 20, 2009

dm83737,

I am not very familiar with the Excel functions in Autoit, but I think you need a couple of changes. The Do...Until loop needs to be amended along these lines:

$sPolicyNumber = $iV1 ----- The flag needs to be the VALUE of the cell, not the cell itself. So something like $sPolicyNumber = _ExcelReadCell($oExcel, $iV1, ?)

Then where you have ; <<<<<<<<<<<DOES SOMETHING NEED TO GO HERE TO MAKE IT GO ON TO THE NEXT ROW?>>>>>>>>>>>> you would need another read of the cell: $sTempread = _ExcelReadCell($oExcel, $iV1, ?)

Next, where you have Until $sPolicyNumber <> $iV1, you would have Until $sPolicyNumber <> $sTempread , ie the VALUE of cell has changed.

Finally, reset the flag to the new value of the cell so you can pick up the next change: $sPolicyNumber = $sTempread

That should then keep you in Do...Until loop until the $sPolicyNumber cell changes value. I am assuming that this happens because of what you are doing in the loop - if not, we need to think again!

-----

You say that you then need to go back to the top of your "; Tells the loop to run from row # to #" code, so you need to have another loop enclosing everything from there until the end of the loop we have just been discussing, as I suggested earlier. The next question is: What is telling you when to get out of this second loop? Because that will determine what kind of loop you use.

Hope this is clear - please ask again if not.

M23

dm83737 · February 20, 2009

dm83737,

I am not very familiar with the Excel functions in Autoit, but I think you need a couple of changes. The Do...Until loop needs to be amended along these lines:

$sPolicyNumber = $iV1 ----- The flag needs to be the VALUE of the cell, not the cell itself. So something like $sPolicyNumber = _ExcelReadCell($oExcel, $iV1, ?)

Then where you have ; <<<<<<<<<<<DOES SOMETHING NEED TO GO HERE TO MAKE IT GO ON TO THE NEXT ROW?>>>>>>>>>>>> you would need another read of the cell: $sTempread = _ExcelReadCell($oExcel, $iV1, ?)

Next, where you have Until $sPolicyNumber <> $iV1, you would have Until $sPolicyNumber <> $sTempread , ie the VALUE of cell has changed.

Finally, reset the flag to the new value of the cell so you can pick up the next change: $sPolicyNumber = $sTempread

That should then keep you in Do...Until loop until the $sPolicyNumber cell changes value. I am assuming that this happens because of what you are doing in the loop - if not, we need to think again!

-----

You say that you then need to go back to the top of your "; Tells the loop to run from row # to #" code, so you need to have another loop enclosing everything from there until the end of the loop we have just been discussing, as I suggested earlier. The next question is: What is telling you when to get out of this second loop? Because that will determine what kind of loop you use.

Hope this is clear - please ask again if not.

M23

Wow, this is getting pretty hairy now. I may be over my head on this with how this is looking. Here is what I have tried:

; Tells the loop to run from row # to #
    For $iE1 = $iB1 To $iC1
      ; Variables involved in successfully filling out an ACAS acreen
       $sCompanyCode = _ExcelReadCell($oExcel, $iE1, 1)
       $sPolicyNumber = _ExcelReadCell($oExcel, $iE1, 2)
       $iClicks = _ExcelReadCell($oExcel, $iE1, 4)
       $sTotalDebits = _ExcelReadCell($oExcel, $iE1, 6)
       $sTotalCredits = _ExcelReadCell($oExcel, $iE1, 7)
       $sFuncCode = ("A")
       $sTran1Code = _ExcelReadCell($oExcel, $iE1, 9)
       $iTran1Amount = _ExcelReadCell($oExcel, $iE1, 10)
       $iTran1Date = _ExcelReadCell($oExcel, $iE1, 12)
       $sTran2Code = _ExcelReadCell($oExcel, $iE1, 17)
       $iTran2Amount = _ExcelReadCell($oExcel, $iE1, 19)
       $iV1 = $iE1+$iClicks
    
    For $iZ1 = $iE1 To $iV1
       ; Procedures
; Goes to the ACAS screen
        WinActivate($Admin)
        WinMove($Admin, "", 0, 0)
        MouseClick("left", 110, 65, 1)
    Sleep(25)
        Send("ACAS")
        Sleep(25)
        Send($sPolicyNumber)
        Send("{TAB}")
        Sleep(25)
    Send($sCompanyCode)
    Sleep(25)
        Send("{F11}")
        Sleep(500)
    Mouseclick("left", 312, 540, $iClicks)
    Mouseclick("left", 80, 308, 1)
        
            Do
        ; Creates and enters the information in the ACAS blanks
            WinActivate($Admin)
            Sleep(25)
            Send($sFuncCode)
            Send("{ENTER}")
            Send("{TAB}")
            Sleep(25)
            Send("CC")
            Send($sTran1Code)
            Send("{ENTER}")
            Send("{TAB 3}")
            Sleep(25)
            Send($iTran1Amount)
            Send("{TAB 4}")
            Sleep(25)
            Send($iTran1Date)
            Send("{TAB 4}")
            Sleep(25)
            Send($iTran1Amount)
            Send("{TAB 6}")
            $sPolicyNumber1 = _ExcelReadCell($oExcel, $iZ1, 2)
            
            MsgBox (262144, "", "Now " & $sPolicyNumber & "  Next " & $sPolicyNumber1 & "  Row " & $iE1 & "  Next " & $iZ1)
        
            Until $sPolicyNumber <> $sPolicyNumber1; <========= May need to be changed
    
    Send("{F3}")
    Sleep(3000)
    
; Goes to the DSCM screen
        WinActivate($Admin)
        WinMove($Admin, "", 0, 0)
        MouseClick("left", 110, 65, 1)
    Sleep(25)
        Send("DSCM")
        Sleep(25)
        Send($sPolicyNumber)
        Send("{TAB}")
        Sleep(25)
    Send($sCompanyCode)
    Sleep(25)
        Send("{F11}")
        Sleep(500)
        
        MsgBox(262192, "Screenshot Message", "Please take a screenshot of the DSCM <if necessary> and press OK to move on.  ", 0)
        
        $sPolicyNumber = $sPolicyNumber1
        
        Next
        
    Next

Now I know some of you are laughing at this as I know that I am making this entirely too complicated. I just need to figure out a way to have AutoIT realize that the cell value has changed from T0001AMS40 to L2002AAMS40 and more of the like. After trying everything I knew and was reading in the replies above, I tried making the loop continue for the value found in $iClicks. The reason for this is that program that I reading the data from Excel and then entering it into makes you create the lines for said data before you do anything (I hope that make sense). Meaning, I have to create 9 lines in the window before I place any data in it.

Now I wish to go back to the way things were before, in that I want to figure out a way to make Autoit realize that the cell value has changed, exit the Do... Until loop and finish the larger For... To... Next loop and then begin it all over again.

Would there be any value from getting this information in an array as opposed to reading it from the spreadsheet? I am still a bit foggy as to why an array is beneficial, but if it makes this easier, I'm all for it.

I know this can work, I think I am just confusing myself and do not have a complete understanding of how I can make this happen.

Just to let you know what happened in the past, When I tried M23's suggestions, it seems to stay hung up on line 2 and never progresses down the rows.

I appreciate all your help and I hope (for my sanity's sake), I can get this figured out before Monday.

Melba23 · February 20, 2009

dm83737,

Sorry if this is not getting done as quickly as you would like - but it is not easy debugging your large chunks of code without either Excel or the worksheet! It is quite late here now - I will have another look in the morning. But if you would rather wait for someone else to reply, given my lack of experience with the Excel UDF, please say so.

M23

dm83737 · February 21, 2009

dm83737,
Sorry if this is not getting done as quickly as you would like - but it is not easy debugging your large chunks of code without either Excel or the worksheet! It is quite late here now - I will have another look in the morning. But if you would rather wait for someone else to reply, given my lack of experience with the Excel UDF, please say so.
M23

I apologize if I came off as that I am in a hurry for you to solve my issue. I have been given a deadline of Wed of next week and am more frustrated with myself for not being able to get this right on my own. I feel bad that I have to keep pestering you guys for assistance.

I really do appreciate all of your expertise as I really have none to speak of; any help is good help in my book. :-)

I plan to take this script apart piece by piece and get each part working the way I want it to. Maybe by getting the Excel piece working on it's own, I can then put it together with all the loops and make a complete script... that's the only way I think I can get my head around all the Do... Until's and For... To... Next's.

Melba23 · February 21, 2009

dm83737,

No need to apologise - I was only pointing out my limitations when it comes to the Excel UDF. Happy to keep trying as long as you feel it is useful.

Re-reading your code from scratch this morning after a good night's sleep, and focusing only on the code segment in your last post (ie your Run_Excel() function), I am becoming convinced that we are dealing with a logic problem rather than a coding problem. Even with my limited knowledge of spreadsheets, I cannot understand quite what you are trying to do in logical terms.

I have produced the following logic flow from the code as posted and added a few questions along the way:

Opening statements to open the correct sheet, keep it visible and activate it

While

For (a range of row values)

Read in a number of values from the specified row
These include $SPolicyNumber and $iClicks

Open "The Administrator" (known as "Admin" from here on!)
Enter some of the values read earlier into fields within "Admin"

Do

From your penultimate post, you here create $iClicks lines within "Admin"
Enter other values read earlier into these lines

Until [Q1: Why do you need to loop here? You have only one set of values to feed into the fields because you are not re-reading them from the sheet at any point.]
[Or do you feed the same values $iClick times into "Admin" to fill the lines you created earlier?]
[Q2. You originally said you wanted to carry on until $sPolicyNumber changed. How does this value change? Does "Admin" do something to the sheet?]
[Or should this be part of the re-reading we seem to be missing? i.e. you read through the rows until $sPolicyNumber changes]

Next [Q3. Is this iteration to the next row the "missing" re-reading where we get new values to enter into the $iClicks lines of "Admin"?]
[Is this where we might get the new $sPolicyNumber at some point?]

WEnd [Q4. How do you exit this While...WEnd loop? What is your criterion for ending the function and returning to the main script?]

I hope you can see what I am trying to do here - help you rethink what it is you are trying to achieve. As I said earlier, I am convinced that we have a logic problem here - as is so often the case in programming. Trying to get the correct logic flow should clarify the process and let you progress. At the moment there does not seem to be a logical flow to the code - unless I have entirely misunderstood the function of "The Administrator", which I freely admit is quite possible!

As you mentioned in your last post, I would suggest that you leave the pure code to one side for the moment and try and produce a similar logic flow of your own. Hope you had a good night's sleep as well - we both need clear heads!

M23

dm83737 · February 23, 2009

M23,

Thank you so much for your help once again, after a good weekend, I think I am ready to get this knocked out.

I will answer your questions so this does not seem as foggy as I have made it seem.

Q1: Why do you need to loop here? You have only one set of values to feed into the fields because you are not re-reading them from the sheet at any point. Or do you feed the same values $iClick times into "Admin" to fill the lines you created earlier?

What is happening is that we are applying payments (usually in a monthly fashion from the policy creation date to a specified testing date (i.e. - From creation date to 9 months ago for instance); hence the iClicks variable creating 9 lines in Admin. So while most of the values do not change as I loop through the iClicks lines, the date is changing with each instance.

Q2. You originally said you wanted to carry on until $sPolicyNumber changed. How does this value change? Does "Admin" do something to the sheet? Or should this be part of the re-reading we seem to be missing? i.e. you read through the rows until $sPolicyNumber changes

When we hit the line where the policy number changes, I want to get out of the loop and process those iClicks lines by having AutoIT hit the F3 key. Those payments we just looped through then are applied to the policy and we are taken to the DSCM screen to take a screenshot of the individual payments and any flags that are created in addition to those payments. A Msgbox is created upon entering this screen that tells the user to take the screens and to hit OK when finished.

Q3. Is this iteration to the next row the "missing" re-reading where we get new values to enter into the $iClicks lines of "Admin"? Is this where we might get the new $sPolicyNumber at some point?

Once the user finishes taking the screens and hits OK, they are entered back into the loop for the next policy number and this continues to happen until the policy number blank in Excel is blank (however now, because I am unsure of how to have it stop when the Policy Number column goes blank, I have the GUI for the user to enter the last row that has a policy number in it).

Q4. How do you exit this While...WEnd loop? What is your criterion for ending the function and returning to the main script?

Answered above (Q3)

I hope this clarifies my intentions a bit more than I had before. If I am still unclear what I am trying to achieve for any part of this script, then please let me know (I tend to type slower than I think and therefore leave out important details at times). I am now using your outline to go over it all with a fine-toothed comb and get this finished soon *fingers-crossed.

Thanks again,

dm83737 · February 23, 2009

I forgot to say that the Excel row advance needs to occur at the end of the Do... Until loop until the Policy number is different... or maybe I didn't. Basically I just wanted to make that clear as I am still unsure of how I can make that advance occur.

Melba23 · February 23, 2009

dm83737,

Thank you for those posts - they make the logic flow much clearer - even if I have no idea what you are doing! ;-) I have combined your code and my logic flow diagram into what I hope will help you produce a successful script.

Have a look at this new version of your Run_Excel() function:

Func Run_Excel($iB1, $iC1)

;Opening statements to open the correct sheet, keep it visible and activate it
;....

;While   I do not think you need this

    For $iE1 = $iB1 To $iC1;(a range of row values)

    ;Read in some values from the specified row
        $sCompanyCode
        $SPolicyNumber
        $iClicks
        
    ;Store $sPolicyNumber in a variable
        $sStored_PolicyNumber = $SPolicyNumber

    ;Open "The Administrator" (known as "Admin" from here on!)
    ;Enter $sCompanyCode and $SPolicyNumber into fields within "Admin"
    ;....
    ;....
    ;Create $iClicks lines within "Admin"
    ;....

        $iV1 = $iE1+$iClicks
    
        For $iZ1 = $iE1 To $iV1
        
        ;Read in from the current line:
            $sPolicyNumber = 
            
        ; A bit of errorchecking - break out of this For...Next loop if we reach the next policy
            If $sPolicyNumber <> $sStored_PolicyNumber Then ExitLoop
            
        ; Then read in:
            $sFuncCode
            $sTran1Code
            $iTran1Amount
            $iTran1Date
            $sTran2Code
            $iTran2Amount
           
        ;Put these values into "Admin"
        ;....
        ;....
            
    ; Go and read the next line
        Next
    
    ;We are now out of the loop because:
    ; 1 - we have a new policy number, or
    ; 2 - we have filled the $iClick number of lines that we created in "Admin"
    ; In either case, $iZ1 points to the first line of the next policy - or past the end of the lines we were to read
        
    ; Now go to the DSCM screen and do whatever you need to do there!
    ;....
    ;....
    
    ; Now we go back up to the top and start with the next policy -if there is one!
    ; So more errorchecking
    ; Firstly - are we over the limit of the lines we set to loop through?
        If $iV1 > $iC1 Then ExitLoop
    ; Next - in case the user enterd the lines wrongly, is there a policy number to read? 
    ; read the policynumber cell on line $iV1 and exit if it is blank
        If _ExcelReadCell(suitable code) = "" Then ExitLoop
        
    ; So there is another policy to read in
    ; But first we must reduce the count value by one, because the Next statement below will increase it by one and so we would skip a line if we did not!
    ; So set the loop variable as so:
        $iE1 = $iV1 - 1
    
    ; Go back up and read the next set of lines
    Next
    
;WEnd   As you do not need the While, you do not need the WEnd either!
    
; OK we are now here because:
; 1. We have read all the lines, or
; 2. We have run out of PolicyNumbers

; You have some lines dealing with Notepad here
;....

    _ExcelBookClose($oExcel, 1, 0)
        
    GUISetState(@SW_SHOW, $Form1_1)

EndFunc ;==>Run_Excel
    
; Note: You read these, but do not use them as far as I can see
$sTotalDebits = _ExcelReadCell($oExcel, $iE1, 6)
$sTotalCredits = _ExcelReadCell($oExcel, $iE1, 7)

dm83737 · February 24, 2009

M23,

Thats not good; hurt calf muscles is no good as they flex with every step and therefore cause you to be miserable sorry to hear about that? How was your score at the time? ;-)

Back to the script, I have tried this code and it all seems to make sense now as far how to have the loops set and the coding worded, etc. But for some reason it does not progress through the rows of the spreadsheet the way I had hoped; it seems to stay on row 1 for everything. The script stops looping when it is supposed to, but only because it is going off of the $iClicks variable (we set up $iV1 and such). It is strange that it is not progressing through the rows because I have another script formed just like this, and it progresses down the sheet just fine. So now I am lost as to why one works and the other does not. They both pull from more than one column and have $iE1 as the same type of variable (row number), the one that works only has the one loop however.

So to cover the outstanding issues I am still having:

1) Getting Excel to progress down the rows correctly

2) Getting the loop/script to stop when it hits a blank cell

And without Excel on your side, I am not sure how well you can troubleshoot my lack of expertise; although I hope you can. The reason I am hoping for a blank-cell-stop for the script is that I am trying to develop this script to not have any GUI input to make it go faster, but that not a big deal at the moment. I just want this script to go through the excel spreadsheet and not stay hung up on row 1.

I am just going to attach the au3 file this time as attaching code over and over can probably get annoying on your end

Just to ask, do you have any expertise with that JAVA.au3 package that is in the User-Submitted Scripts area? I ask because the ADMINISTRATOR is a JAVA app, and therefore the Window Info window in AutoIT is blank. Hence the Msgbox reference below for a different, I can not get AutoIT to recognize that the results of the GPIN transaction are now showing as opposed to when they are not. So I have created a Msgbox to keep AutoIT from getting ahead of the ADMINSTRATOR.

; Because the calling of the GPIN screen can be a random act time-wise, this msgbox allows the script to pause until it is finished 
;     (or it times out at 60 seconds, whatever comes first)
MsgBox(262192, "GPIN", "Click OK when the GPIN screen has appeared", 60)
[\code]

I hope you get well soon, but only after we get this stuff figured out, agreed?  ;-)ACAS_Clone___Revise.au3


            
        

        

        
    

    

    




    Link to comment
    
        
    
    
    

    
    Share on other sites
    

    
        
            

    

        
            

    

        
            

    

        
            

    

        
    


    
    More sharing options...

    


    

                    
                    
                    
                

                    

                    
                    





    

    
        
            
                
                    Moderators
                
                
                
                
            
        
    

    
        
            
                


    
        
    

                
                
                
                
                    
                        

                    
                
            
        
        
            
                


Melba23
            
            
                Posted February 24, 2009
                
            
        
    
    
        


Melba23
            
        
        
            
                
                    


    
        
    

                    
                        
                    
                    
                        

                    
                
            
            
                Moderators
                
                    
                
            
            
                
                    
                        
                            
                                
                            
                                 31.1k
                            
                                
                            
                        
                        
                            
                                
                                    
                                        
                                        477
                                
                                    
                                
                            
                        
                    
                
            
            
                

    
    
        
I'm old, what's your excuse?
    
    

            
        
    
    
        



    
        
            
                
                    
                    
                        Moderators
                    
                    
                    
                    
                
            
            
                
                    
                    
                        
                        
                            Share
                        
                        
                        
                        
                        
                            
                                
                            
                            
                            
                            
                            
                            
                        
                    
                
                
            
        

        
            Posted February 24, 2009
            
            
                
                
            
        
    

    

    

    
        
        
            dm83737,Thanks for the sympathy - and yes I was playing the best I had for a while and was looking forward to handing in a good card!  An old skiing injury from 20-odd years ago resurfacing I am afraid, although it has never acted up like this before - felt like someone had hit me on the back of my leg with a hammer!But to work....  There are a few things I would like to change in your script.  Let us run through them from the top:1.  You need some errorchecking after the Spreadsheet selection on line 49.  At the moment you can still proceed even if you click 'Cancel'.  A simple If @error = 1 Then will suffice here.2.  Let us move to the INNER loop, line 166.  You have read in $sPolicyNumber on line 129 and stored it in $sTemPolNumber on line 137 - so far so good!  But on line 166 you are merely comparing these values - which are obviously the same because you made it so!  What you need to do is read in the $sPolicyNumber on the ACTUAL line and compare that value to the stored one:; Exits the loop when the policy numbers no longer match
;If $iZ1 = $iE1 + 4 Then $sPolicyNumber = 2
If _ExcelReadCell($oExcel, $iZ1, 2)<> $sTempPolNumber Then ExitLoop

3. Next, you are using the wrong count variable when you check for the end of the OUTER loop at lines 227 to 236. You are using $iV1 which is the UPPER LIMIT for the INNER loop - what you need to look at is $iZ1 which is the ACTUAL row number from within the loop. So rewrite those lines like so:

; Checks to see if we have gotten to the end of the range entered by the user
If $iZ1 > $iC1 Then ExitLoop  ;##################################################################################   $iZ1 not $iV1

; Next - in case the user entered the lines wrongly, is there a policy number to read? 
; Read the policynumber cell on line $iZ1 and exit if it is blank
If _ExcelReadCell($oExcel, ($iZ1), 2) = "" Then ExitLoop;################################################################################## $iZ1 not $iV1 + 1
    
; So there is another policy to read in
; But first we must reduce the count value by one, because the Next statement below will increase it by one and so we would skip a line if we did not!
; So set the loop variable as so:
$iE1 = $iZ1 - 1 ;################################################################################## $iZ1 not $iV1

I have been running this with dummy variables and getting it to work perfectly. Give it a go and see how it runs and get back to me. As you know, I am going nowhere......

M23

dm83737 · February 24, 2009

; Exits the loop when the policy numbers no longer match
;If $iZ1 = $iE1 + 4 Then $sPolicyNumber = 2
If _ExcelReadCell($oExcel, $iZ1, 2)<> $sTempPolNumber Then ExitLoop

Should I uncomment the:

;If $iZ1 = $iE1 + 4 Then $sPolicyNumber = 2

?

dm83737 · February 24, 2009

M23,

I ran it with the enhancements you noted and it seems to go almost perfectly. Then to make sure it was indeed going down the line, I changed each Tran!Amount in the Excel file to be progressively bigger (i.e. - 65, 66, 67, etc.) to make sure it would enter those values and thus ensure it is going line by line in the spreadsheet and not just repeating the first line as it had in the past; but it kept repeating 65. that and tran date stayed the same as well. This is all strange as it must be going down the line policy number-wise because it knows when it hits a new number and moves on. :-(

So I am including the au3 again to make sure I implemented your suggestions correctly. I am also including a png of how the spreadsheet looks for your reference.

Thanks for about the 50th time, this would not be possible without your help as it would have taken me forever to get this right.

ACAS_Clone___Revise.au3

Melba23 · February 24, 2009

dm83737,

Replying to 2 posts - both positively (I hope!).

The earlier one - That commented line was part of my "dummy variables" run - please delete it, which I see you have done in your later script!

The last one: Very simple response - you are not reading in the new values! My fault - I left this as "an exercise for the student"! Although to be honest, it was part of my Q3 a while ago.....

If you look at your script, you never re-read those values in the INNER loop, so they stay the same as when you first read them in the OUTER loop. You must re-read them at every iteration of the INNER loop. Put the re-reads after the $sPolicyNumber check around line 170:

; Exits the loop when the policy numbers no longer match
If _ExcelReadCell($oExcel, $iZ1, 2)<> $sTempPolNumber Then ExitLoop

; ########################################### New bit - you can delete these attention getters :-)
; Read values from this line
$sTran1Code = _ExcelReadCell($oExcel, $iZ1, 9)
$iTran1Amount = _ExcelReadCell($oExcel, $iZ1, 10)
$iTran1Date = _ExcelReadCell($oExcel, $iZ1, 12)
; ###########################################

; Creates and enters the information in the ACAS blanks until the next policy number
WinActivate($Admin)
; etc............

This also means that you should be able to delete the initial reads from line 136 to 138 as they serve no purpose given that you are reading on every iteration.

Implement that quickly and let me know how it goes - Jose Mourinho's Inter Milan are playing Man Utd (spit!) tonight in the Champions' League and I want to watch my favourite manager thrash my most hated team! So you might find my replies a bit intermittent, given the difficulty I currently have in moving from TV to PC!!!!!

M23

dm83737 · February 24, 2009

dm83737,

Replying to 2 posts - both positively (I hope!).

The earlier one - That commented line was part of my "dummy variables" run - please delete it, which I see you have done in your later script!

The last one: Very simple response - you are not reading in the new values! My fault - I left this as "an exercise for the student"! Although to be honest, it was part of my Q3 a while ago.....

If you look at your script, you never re-read those values in the INNER loop, so they stay the same as when you first read them in the OUTER loop. You must re-read them at every iteration of the INNER loop. Put the re-reads after the $sPolicyNumber check around line 170:
; Exits the loop when the policy numbers no longer match
If _ExcelReadCell($oExcel, $iZ1, 2)<> $sTempPolNumber Then ExitLoop

; ########################################### New bit - you can delete these attention getters :-)
; Read values from this line
$sTran1Code = _ExcelReadCell($oExcel, $iZ1, 9)
$iTran1Amount = _ExcelReadCell($oExcel, $iZ1, 10)
$iTran1Date = _ExcelReadCell($oExcel, $iZ1, 12)
; ###########################################

; Creates and enters the information in the ACAS blanks until the next policy number
WinActivate($Admin)
; etc............
This also means that you should be able to delete the initial reads from line 136 to 138 as they serve no purpose given that you are reading on every iteration.

Implement that quickly and let me know how it goes - Jose Mourinho's Inter Milan are playing Man Utd (spit!) tonight in the Champions' League and I want to watch my favourite manager thrash my most hated team! So you might find my replies a bit intermittent, given the difficulty I currently have in moving from TV to PC!!!!!

M23

Much appreciated and a bit embarrassing as I should have picked up on your teaching effort. I am not a big Man U fan either; despite being from the states, I have watched a premier game here and there on FOX Sports International (now FOX Soccer Channel) and for quite a while, I have been a Pompey fan. I really couldn't tell you why though. It could be they were the first team I had ever seen and I like Harry Redknapp as I think he's a pretty funny and talented manager (lacks a bit in the loyalty area though)

I will let you know in the next 5-10 mins if this works as I am putting it and running it right now.

dm83737 · February 24, 2009

OMG, it works!

Thank you so much for helping me out with this, I couldn't have done it without you. Have fun watching the game and now you can forget all about this ;-)

Take care

Melba23 · February 24, 2009

dm83737,

Great news - glad I was able to help. Now go and work on your presentation for tomorrow. ;-)

M23

Loop until a cell in Excel contains a different value

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members