MrCheese Posted January 8, 2018 Posted January 8, 2018 (edited) Hey Guys, So, the functions work, where primarygui() accurately determines the evaluation of the status of the checkboxes - the msgbox picks this up. However, later on, when we re-enter a 'for $i = 0 to ubound($checkbox)' loop, then in the 'batchinitial' function it doesn't picked up that the status&$i = 1, so it jumps out, then within the While 1 loop, it exits the loop in the first row, again because the status& $i= 0 The "assign" line within the primarygui funtion, is this only a local assignment? if so, how can I make it cross function? Thank in adv for your help expandcollapse popupFunc excelsheetlist() $i = 0 Global $aWorkSheets = _Excel_SheetList($oWorkbook1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1") ;_ArrayDisplay($aWorkSheets, "Array") Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10. Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20. Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional. MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _ $iRows & " row(s) & " & $iCols & " column(s).") Dim $checkbox[$iRows] EndFunc ;==>excelsheetlist Func primarygui() ; Create a GUI with various controls. Local $hGUI = GUICreate("Script Controller", 300, ($iRows * 24)) ; Create a checkbox control. ;Local $idCheckbox = GUICtrlCreateCheckbox("Standard Checkbox", 10, 10, 185, 25) Local $Button2 = GUICtrlCreateButton("Close", 210, 200, 85, 25) Local $Button3 = GUICtrlCreateButton("Run", 210, 170, 85, 25) Local $Button1 = GUICtrlCreateButton("Discharge", 210, 140, 85, 25) For $i = 0 To UBound($checkbox) - 1 $checkbox[$i] = GUICtrlCreateCheckbox($aWorkSheets[$i][0], 8, 0 + ($i * 24)) ;, 81, 17) Next ; Display the GUI. GUISetState(@SW_SHOW, $hGUI) ; Loop until the user exits. While 1 $nMsg = GUIGetMsg() Select Case $nMsg = $GUI_EVENT_CLOSE Exit Case $nMsg = $Button2 ;Close Exit Case $nMsg = $Button1 MsgBox(0, "Discharge Button not configured", "Now Exiting") Exit Case $nMsg = $Button3 ;Run $fSelection = False For $i = 0 To UBound($checkbox) - 1 If BitAND(GUICtrlRead($checkbox[$i]), $GUI_CHECKED) Then $fSelection = True ExitLoop EndIf Next If $fSelection Then For $i = 0 To UBound($checkbox) - 1 Assign("status" & $i, GUICtrlRead($checkbox[$i])) Next $batchcount = 0 For $i = 0 To UBound($checkbox) - 1 If Eval("status" & $i) = 1 Then $batchcount = $batchcount + 1 ;Call ("o" & $i & "copy") ; if you want to call the functions directly, remove ; before the call and comment or delete the following DirCopy statement MsgBox(0, "Checking", "Checking that: " & $checkbox[$i] & " no, with title: " & $aWorkSheets[$i][0] & " was selected, Batch count: " & $batchcount) ; if you need only the DirCopy EndIf Next ExitLoop Else MsgBox(48, 'No Items Selected', 'You have not selected any Patients to Load, Please select from the list') EndIf EndSelect WEnd ; Delete the previous GUI and all controls. GUIDelete($hGUI) EndFunc ;==>primarygui Func _IsChecked($idControlID) Return BitAND(GUICtrlRead($idControlID), $GUI_CHECKED) = $GUI_CHECKED EndFunc ;==>_IsChecked Func batchinitial() If Eval("status" & $i) = 1 Then debugbox() $oWorkbook1.Sheets(1).Activate $bigloop = $bigloop + 1 $sheet = $aWorkSheets[$i][0] $oWorkbook1.Sheets($sheet).Activate debugbox() EndIf EndFunc ;==>batchinitial OpenExcel() excelsheetlist() primarygui() For $i = 0 To UBound($checkbox) - 1 batchinitial() While 1 If Eval("status" & $i) = 0 Then ExitLoop ;all the rest of my script loops etc WEnd Next Edited January 8, 2018 by MrCheese
MrCheese Posted January 8, 2018 Author Posted January 8, 2018 (edited) Okay - so I added '2' at the end of "assign" Assign("status" & $i, GUICtrlRead($checkbox[$i]), 2) Now, it doesn't proceed past the 'batchinitial()' function: Func batchinitial() If Eval("status" & $i) = 1 Then debugbox() $oWorkbook1.Sheets(1).Activate $bigloop = $bigloop + 1 $sheet = $aWorkSheets[$i][0] $oWorkbook1.Sheets($sheet).Activate debugbox() EndIf EndFunc ;==>batchinitial but it does proceed passed the if -> exitloop For $i = 0 To UBound($checkbox) - 1 batchinitial() While 1 If Eval("status" & $i) = 0 Then ExitLoop Which would suggest that the status$i value is neither 1 nor 0. I inserted: Dim $status[0] at the top to see if that helped, but it didn't. Any help would be great! Thanks Edited January 8, 2018 by MrCheese
MrCheese Posted January 8, 2018 Author Posted January 8, 2018 Noting - I picked up eval and assign from this thread - and that perhaps theres a better way to do what I want to do?
benners Posted January 8, 2018 Posted January 8, 2018 (edited) Do you have a script that compiles without errors? One thing that stands out is in the batchinitial function you specifiy If Eval("status" & $i) = 1 Then But $i is local to primarygui. You also specifiy $bigloop = $bigloop + 1 but $bigloop is not declared anywhere . Normally, if adding lots of controls I'll use an array (as suggested by others in the post above) to keep a record of the ControlIDs . Then you can loop through this array to action the controls. Edited January 8, 2018 by benners
MrCheese Posted January 8, 2018 Author Posted January 8, 2018 (edited) yes - sorry, i removed the rest of the code, as the entire script is about 1500 lines long. but yes, it compiles fine. would it work if I move the gui stuff outside of the function into the main script? Or should I use a different $i? $i is used to initially run through a spreadsheet to create all the respective checkboxes on the GUI, then its used to determine which checkboxes have been checked, then it ideally documents, stores as "status0 = 1" if the first box is checked. I thought that this 'assign' function would be global since I marked it as such. - or am I not understanding how it works? edit: I have tested moving the GUI and batch initial out of their functions. It works if and only if i select the first checkbox i.e. status0, if any of the others are selected, it doesn't pick up on this. indicating that I'm either prasing the Ifs wrong, or I don't have the eval right. Edited January 8, 2018 by MrCheese
benners Posted January 8, 2018 Posted January 8, 2018 (edited) I don't know much about assign\eval\isdeclared so my lack of knowledge makes me think they are evil , I normally find another way like an array. Looking at the Assign statement, it just uses the default as the 3rd parameter, should this not be added to make it Global?. That said, with the line "If Eval("status" & $i) = 1 Then", if it was declared as Global such as, status0, The Eval should return an error as $i has no value so it should just be trying to return the value of "status". I'm not experienced enough at look at code and pick out the issues, I normally need a runnable script to play with. Again I would try to use an array and condem the Assign\Eval usage to an early grave Edited January 8, 2018 by benners
MrCheese Posted January 8, 2018 Author Posted January 8, 2018 mmmm okay. So, I should commit these values to an array, can I add them to an exisiting array; by, replacing assign/eval here: If $fSelection Then For $i = 0 To UBound($checkbox) - 1 Assign("status" & $i, GUICtrlRead($checkbox[$i]), 2) Next $batchcount = 0 For $i = 0 To UBound($checkbox) - 1 If Eval("status" & $i) = 1 Then $batchcount = $batchcount + 1 ;Call ("o" & $i & "copy") ; if you want to call the functions directly, remove ; before the call and comment or delete the following DirCopy statement MsgBox(0, "Checking", "Checking that: " & $checkbox[$i] & " no, with title: " & $aWorkSheets[$i][0] & " was selected, Batch count: " & $batchcount & " / state: " & $i & " = " & $status[$i]) ; if you need only the DirCopy EndIf Next can i commit the above into the previous array here: Func excelsheetlist() $i = 0 Global $aWorkSheets = _Excel_SheetList($oWorkbook1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1") ;_ArrayDisplay($aWorkSheets, "Array") Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10. Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20. Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional. MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _ $iRows & " row(s) & " & $iCols & " column(s).") Dim $checkbox[$iRows] EndFunc ;==>excelsheetlist Or won't that work?
MrCheese Posted January 9, 2018 Author Posted January 9, 2018 (edited) Okay - I can get the status into the array: - code is below for those who might find this useful later on. However, I'm getting the occasional error where the script just bombs out: >Running:(3.3.14.2):C:\Users\25126209\Documents\autoit-v3-SciTE\autoit-v3\install\autoit3.exe "D:\scripts\PatientConversation_v0.7.au3" --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop "D:\scripts\PatientConversation_v0.7.au3" (514) : ==> The requested action with this object has failed.: $oWorkbook1.Sheets($sheet).Activate $oWorkbook1^ ERROR ->17:15:34 AutoIt3.exe ended.rc:1 This refers to the $oWorkbook1.Sheets($sheet).Activate section below. I also attempted to use $oWorkbook1.Sheets($i + 1).Activate, as this refers to the numbered row + 1 in the array that is referred to: if $aWorkSheets[$i][2] = 1 A few questions - i) how can I msgbox up if it fails here, and ii) how can I fix this? ConvoL() For $i = 0 To UBound($checkbox) - 1 If $aWorkSheets[$i][2] = 1 Then $bigloop += $bigloop $sheet = $aWorkSheets[$i][0] $oWorkbook1.Sheets($sheet).Activate While 1 ttipscalc() reset() If $secondgo = False Then $run = $run + 1 $ptno = $ptno + 1 EndIf If $run > $ttlrun Then ExitLoop If $ptno > $ttlrun Then ExitLoop $row = $ptno + $rrdif If $restarttrigger = True Then ;restartpc() EndIf While 1 Checkstatus() ;funcs etc etc WEnd WEnd EndIf Next EDIT: I am also getting this error: --> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop "D:\scripts\PatientConversation_v0.7.au3" (663) : ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR ->17:44:19 AutoIt3.exe ended.rc:1 Which relates to - which is in the primary loop. I ended up removing this, and just calling the array where this information had previously been stored: $ttlrun = $aWorksheets[$i][3] Func checkstatus() $msg = "Checking status" ttips() $column = $ptComment1 Local $oRangeRead = _Excel_RangeRead($oWorkbook1, Default, $column & $row) If $oRangeRead = "Completed" Or $oRangeRead = "Completed - existing encounter" Then $exitloop = True EndIf ; **SCRIPT NEEDED** insert script to determine total number of rows With $oWorkbook1.ActiveSheet ; process active sheet $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) ; get a Range that contains the last used cells $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count ; get the the row count for the range starting in row/column 1 and ending at the last used row/column $iLastCell = .Cells($iRowCount + 1, $columnfind).End($xlUp).Row ; start in the row following the last used row and move up to the first used cell in column "B" and grab this row number ;MsgBox(0, "", "Last used cell in column A: " & $iLastCell & @CRLF & "row count: " & $iRowCount) EndWith $ttlrun = $iRowCount - 1 EndFunc ;==>checkstatus scripts for info: ----- Func excelsheetlist() $i = 0 Global $aWorkSheets = _Excel_SheetList($oWorkbook1) If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_SheetList Example 1", "Error listing Worksheets." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ;_ArrayDisplay($aWorkSheets, "Excel UDF: _Excel_SheetList Example 1") ;_ArrayDisplay($aWorkSheets, "Array") Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10. Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20. Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional. MsgBox($MB_SYSTEMMODAL, "", "The array is a " & $iDimension & " dimensional array with " & _ $iRows & " row(s) & " & $iCols & " column(s).") Dim $checkbox[$iRows] _ArrayColInsert($aWorkSheets, 2) ; added to create a new col EndFunc ;==>excelsheetlist expandcollapse popupexcelsheetlist() ;primarygui() ; Create a GUI with various controls. Local $hGUI = GUICreate("Script Controller", 300, ($iRows * 24)) ; Create a checkbox control. ;Local $idCheckbox = GUICtrlCreateCheckbox("Standard Checkbox", 10, 10, 185, 25) Local $Button2 = GUICtrlCreateButton("Close", 210, 200, 85, 25) Local $Button3 = GUICtrlCreateButton("Run", 210, 170, 85, 25) Local $Button1 = GUICtrlCreateButton("Discharge", 210, 140, 85, 25) For $i = 0 To UBound($checkbox) - 1 $checkbox[$i] = GUICtrlCreateCheckbox($aWorkSheets[$i][0], 8, 0 + ($i * 24)) ;, 81, 17) Next ; Display the GUI. GUISetState(@SW_SHOW, $hGUI) ; Loop until the user exits. While 1 $nMsg = GUIGetMsg() Select Case $nMsg = $GUI_EVENT_CLOSE Exit Case $nMsg = $Button2 ;Close Exit Case $nMsg = $Button1 MsgBox(0, "Discharge Button not configured", "Now Exiting") Exit Case $nMsg = $Button3 ;Run $fSelection = False For $i = 0 To UBound($checkbox) - 1 If BitAND(GUICtrlRead($checkbox[$i]), $GUI_CHECKED) Then $fSelection = True ExitLoop EndIf Next If $fSelection Then $batchcount = 0 For $i = 0 To UBound($checkbox) - 1 $sFill = GUICtrlRead($checkbox[$i]) $aWorkSheets[$i][2] = $sFill $batchcount += (GUICtrlRead($i) = 1 ? 1 : 0) Next ExitLoop Else MsgBox(48, 'No Items Selected', 'You have not selected any Patients to Load, Please select from the list') EndIf EndSelect WEnd ; Delete the previous GUI and all controls. GUIDelete($hGUI) Edited January 9, 2018 by MrCheese
benners Posted January 9, 2018 Posted January 9, 2018 Here is one way I would add the checkboxes to an array and the find out which ones are selected (code below). As mentioned It would be better to post a minimal script that does the basics and people can test, rather than snippets of code that are of no use to anyone. If the code is 1500 lines long, go through it and remove the stuff you don't need/use. One example is the excelsheetlist function, Don't declare global variables inside functions. If it's only used in the function make it Local. Global $iRows = UBound($aWorkSheets, $UBOUND_ROWS) ; Total number of rows. In this example it will be 10. Global $iCols = UBound($aWorkSheets, $UBOUND_COLUMNS) ; Total number of columns. In this example it will be 20. Global $iDimension = UBound($aWorkSheets, $UBOUND_DIMENSIONS) ; The dimension of the array e.g. 1/2/3 dimensional. You only use the $iRows inside the function so make it local, and the other two aren't used anywhere I can see, (as there are only snippets posted) expandcollapse popup#include <Excel.au3> #include <GUIConstantsEx.au3> #include <Array.au3> Opt('GUIOnEventMode', 1) ; array for ther sheetnames ; returns a 2D array, we will use the 2nd column where the Object of the worksheet should be ; to hold the checkbox checked state Global $as_SheetNames = Excel_GetSheetList() ; returns a 2D array, If @error Then CloseProgram() ; array for the checkbox controlIDs Global $aid_Checkboxes[UBound($as_SheetNames)] DrawGUI() While 1 Sleep(50) WEnd Func CloseProgram() Exit EndFunc ;==>CloseProgram ; update the array when a checkbox is selected Func Checkbox_Clicked() ; search the checkbox array and get the index of the clicked checkbox Local $i_Index = _ArraySearch($aid_Checkboxes, @GUI_CtrlId) ; update the sheets array with the checked state $as_SheetNames[$i_Index][1] = GUICtrlRead(@GUI_CtrlId) EndFunc ;==>Checkbox_Clicked ; get the sheet names Func Excel_GetSheetList() Local $oExcel = _Excel_Open(False) If @error Then Return SetError(1, MsgBox(0, '_Excel_Open', 'Error: ' & @error), 0) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx") If @error Then Return SetError(2, MsgBox(0, '_Excel_BookOpen', 'Error: ' & @error), 0) Local $aWorkSheets = _Excel_SheetList($oWorkbook) If @error Then Return SetError(2, MsgBox(0, '_Excel_SheetList', 'Error: ' & @error), 0) _Excel_Close($oExcel) If Not IsArray($aWorkSheets) Then Return SetError(4, MsgBox(0, '$aWorkSheets', 'Unable to get worksheet names'), 0) Return $aWorkSheets EndFunc ;==>Excel_GetSheetList ; draw the gui Func DrawGUI() GUICreate("test", 200, 200, 1026, 420) GUISetOnEvent($GUI_EVENT_CLOSE, 'CloseProgram') GUICtrlCreateButton("Open Sheets", 10, 150, 90, 25) GUICtrlSetOnEvent(-1, 'GetChecked_BtnClicked') For $i = 0 To UBound($as_SheetNames) - 1 $aid_Checkboxes[$i] = GUICtrlCreateCheckbox($as_SheetNames[$i][0], 8, 10 + ($i * 24)) GUICtrlSetOnEvent(-1, 'Checkbox_Clicked') Next GUISetState() EndFunc ;==>DrawGUI ; get the selected checkboxes Func GetChecked_BtnClicked() ; find all the selected checkboxes Local $ai_Selected = _ArrayFindAll($as_SheetNames, $GUI_CHECKED, 0, 0, 0, 0, 1) If Not IsArray($ai_Selected) Then Return MsgBox(0, 'No Results', 'No check boxes were selected') Local $oExcel = _Excel_Open() If @error Then Return SetError(1, MsgBox(0, '_Excel_Open', 'Error: ' & @error), 0) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\test.xlsx") If @error Then Return SetError(2, MsgBox(0, '_Excel_BookOpen', 'Error: ' & @error), 0) For $i = 0 To UBound($ai_Selected) - 1 ; activate the sheet $oWorkbook.Sheets($as_SheetNames[$ai_Selected[$i]][0]).Activate Sleep(2000) Next _Excel_Close($oExcel, True, True) EndFunc ;==>GetChecked_BtnClicked test.xlsx
MrCheese Posted January 9, 2018 Author Posted January 9, 2018 Hi - thanks for your reply, I can see how you've improved the flow and the functions. I'll see what I can incorporate. Yeah, I see your point, Just most (~90%) of the script is utilised, and some of the content is commercially sensitive. makes pulling that out tricky. I should be able to simulate my issues in a more simple script and then copy them here.
benners Posted January 9, 2018 Posted January 9, 2018 No problem. If you post a file (like a spreadsheet) it doesn't need to be the one you use, just one that mimics it so function can be tested. It makes it easier to produce errors as it might run error free on one persons machine but no on another.
MrCheese Posted January 9, 2018 Author Posted January 9, 2018 In this component: For $i = 0 To UBound($ai_Selected) - 1 ; activate the sheet $oWorkbook.Sheets($as_SheetNames[$ai_Selected[$i]][0]).Activate Sleep(2000) Next Compared to mine below, the primary point of difference is that you avoid the need for the if statement as you have already extracted the active checkboxes? For $i = 0 To UBound($checkbox) - 1 ; loops through the checkboxes If $aWorkSheets[$i][2] = 1 Then ; reads where in the array is stored if the checkbox is checked $sheet = $aWorkSheets[$i][0] ; sheet name is located here $oWorkbook1.Sheets($sheet).Activate ; activated based on sheet name ;blar blar blar endif next The Sheet.activate component is giving me the odd failure - but more so on one laptop then another. Same vr of WIn and Office.
benners Posted January 10, 2018 Posted January 10, 2018 No idea why that would be. If your method is OK just use that. The sleep is only to wait so you can see the sheets being selected or Excel would just flash up and close. It wouldn't be needed in your script. I have had a few blips with the Excel UDF as well but have never been able to reproduce enough to get info
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