dm83737 Posted February 20, 2009 Share Posted 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: expandcollapse popup; 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: expandcollapse popup; 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 Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 20, 2009 Moderators Share Posted 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 valueI think that should do the trick.M23  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
ToyBoi Posted February 20, 2009 Share Posted February 20, 2009 would be much easier to do in vba Link to comment Share on other sites More sharing options...
dm83737 Posted February 20, 2009 Author Share Posted 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: expandcollapse popupFor $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 Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 20, 2009 Moderators Share Posted 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 = $sTempreadThat 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  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
dm83737 Posted February 20, 2009 Author Share Posted 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: expandcollapse popup; 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. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 20, 2009 Moderators Share Posted 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  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
dm83737 Posted February 21, 2009 Author Share Posted 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.M23I 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. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 21, 2009 Moderators Share Posted 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  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
dm83737 Posted February 23, 2009 Author Share Posted 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 changesWhen 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, Link to comment Share on other sites More sharing options...
dm83737 Posted February 23, 2009 Author Share Posted 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. Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 23, 2009 Moderators Share Posted 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:expandcollapse popupFunc 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)  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
dm83737 Posted February 24, 2009 Author Share Posted 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. expandcollapse popup ; 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 478 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 ExitLoop3. 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 $iV1I 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  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
dm83737 Posted February 24, 2009 Author Share Posted 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 ? Link to comment Share on other sites More sharing options...
dm83737 Posted February 24, 2009 Author Share Posted 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 Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 24, 2009 Moderators Share Posted 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  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
dm83737 Posted February 24, 2009 Author Share Posted 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. Link to comment Share on other sites More sharing options...
dm83737 Posted February 24, 2009 Author Share Posted 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 Link to comment Share on other sites More sharing options...
Moderators Melba23 Posted February 24, 2009 Moderators Share Posted February 24, 2009 dm83737, Great news - glad I was able to help. Now go and work on your presentation for tomorrow. ;-) M23  Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind Open spoiler to see my UDFs: Spoiler ArrayMultiColSort ---- Sort arrays on multiple columnsChooseFileFolder ---- Single and multiple selections from specified path treeview listingDate_Time_Convert -- Easily convert date/time formats, including the language usedExtMsgBox --------- A highly customisable replacement for MsgBoxGUIExtender -------- Extend and retract multiple sections within a GUIGUIFrame ---------- Subdivide GUIs into many adjustable framesGUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView itemsGUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeViewMarquee ----------- Scrolling tickertape GUIsNoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxesNotify ------------- Small notifications on the edge of the displayScrollbars ----------Automatically sized scrollbars with a single commandStringSize ---------- Automatically size controls to fit textToast -------------- Small GUIs which pop out of the notification area  Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now