kor Posted January 19, 2011 Share Posted January 19, 2011 I have 2 loops The first loop runs through an excel sheet and reads the data into an array then preforms error checking on all the values. If there is a row that has an error I use an ExcelWriteCell to write a value. The problem is the second loop checks for specific words in a column. If that word is there then the loop runs, else the loop moves on. the problem is the first loop is supposed to change the value of the cell such that the second loop would skip it. using various ArrayDisplays I can see that even at the very end of the script the cells have not been written yet. It seems only when the ExcelBookClose is used is when the cell values are committed. Is there a way to commit the changes in the first loop so the second loop sees the modified cells correctly? I kinda don't want to open the worksheet, then close it then open it again. Seems clunky. Link to comment Share on other sites More sharing options...
ZacUSNYR Posted January 19, 2011 Share Posted January 19, 2011 #Include <Excel.au3> _ExcelBookClose($oExcel [, $fSave = 1 [, $fAlerts = 0]]) Parameters $oExcel Excel object opened by a preceding call to _ExcelBookOpen() or _ExcelBookNew() $fSave [optional] Flag for saving the file before closing (0=no save, 1=save) (default = 1) $fAlerts [optional] Flag for disabling/enabling Excel message alerts (0=disable, 1=enable) (default = 0) You are making the changes, but they're not being saved. _ExcelBookClose will save on exit.. So if you try to open that file it hasn't been saved yet. You should have a file lock on it though. If you want to save the file before closing it - _ExcelBookSave . Are you trying to re-read the document in the script and update the array? Why not keep your other thread going where you were working on this. Because if you're using my later example I gave you, the array is not being modified so it'll never update until you re-read the page. Link to comment Share on other sites More sharing options...
kor Posted January 19, 2011 Author Share Posted January 19, 2011 (edited) Thats the problem I am having. I need to spit out the modifications from the first loop and have them committed, before the second loop reads the worksheet. The second loop needs to see the changes from the first loop before it can proceed. Do I need to save the worksheet in between the loops? Or some other method? New thread for new problem (i thought it was a new problem anyway) EDIT: how can I save the worksheet using _ExcelBookSave then re-read the updated array within the same script? Edited January 19, 2011 by kor Link to comment Share on other sites More sharing options...
ZacUSNYR Posted January 19, 2011 Share Posted January 19, 2011 I'd have to see what you're doing, post what you have for the code now and is the example spreadsheet the same? If it's different post that as well and i'll see what I cna figure out for ya. Link to comment Share on other sites More sharing options...
kor Posted January 19, 2011 Author Share Posted January 19, 2011 expandcollapse popupGlobal $excelsheet = @ScriptDir & "\test.xls" $book = _ExcelBookOpen($excelsheet, 0) ; open excel in background $data = _ExcelReadSheetToArray($book, 1, 1) ; ignores header row _ArrayDisplay($data1, "Array1") For $i = 2 to UBound($data1)-1 $varfirstname = $data[$i][1] $varlastname = $data[$i][2] $varlocation = $data[$i][3] $vartitle = $data[$i][4] $varclass = $data[$i][5] $varaction = $data[$i][6] _ValidateData() Next For $i = 2 to UBound($data)-1 $varfirstname = $data[$i][1] $varlastname = $data[$i][2] $varlocation = $data[$i][3] $vartitle = $data[$i][4] $varclass = $data[$i][5] $varaction = $data[$i][6] Select Case $varaction = "new hire" msgbox(0, '', "new hire" & @CRLF & $varfirstname & @CRLF & $varlastname & @CRLF & $varlocation & @CRLF & $vartitle & @CRLF & $varclass & @CRLF & $varaction) Case $varaction = "termination" msgbox(0, '', "termination" & @CRLF & $varfirstname & @CRLF & $varlastname & @CRLF & $varlocation & @CRLF & $vartitle & @CRLF & $varclass & @CRLF & $varaction) Case $varaction = "move" msgbox(0, '', "move" & @CRLF & $varfirstname & @CRLF & $varlastname & @CRLF & $varlocation & @CRLF & $vartitle & @CRLF & $varclass & @CRLF & $varaction) Case Else ;msgbox(0, '', "error") EndSelect Next Func _ValidateData() ; Check all fields to make sure the data is valid If $valfirstname = "" Or $vallastname = "" Or $vallocation = "" Or $valtitle = "" Or $valclass = "" Or $valaction = "" Then _ExcelWriteCell($book1, "error - " & $data1[$i][6], $i, 6) _ExcelWriteCell($book1, "error 001 - 1 or more fields are blank", $i, $numcolumns + 1) Else _ExcelWriteCell($book1, "passed", $i, $numcolumns + 1) EndIf EndFunc ;==> _ValidateData _ExcelBookClose($book) ; close excel Link to comment Share on other sites More sharing options...
ZacUSNYR Posted January 19, 2011 Share Posted January 19, 2011 I commented inside your script - all comments that start with !! are mine expandcollapse popup#include <Excel.au3> #include <Array.au3> Global $excelsheet = @ScriptDir & "\test.xls" $book = _ExcelBookOpen($excelsheet, 0) $data = _ExcelReadSheetToArray($book, 1, 1) _ArrayDisplay($data, "Array1") For $i = 2 To UBound($data) - 1 ;~ $varfirstname = $data[$i][1] - !! Why are you transferring the contents of the array to individual variables? ;~ $varlastname = $data[$i][2] - !! It's easier to manipulate and handle as an array. ;~ $varlocation = $data[$i][3] ;~ $vartitle = $data[$i][4] ;~ $varclass = $data[$i][5] ;~ $varaction = $data[$i][6] ;~ _ValidateData() - !! Why bother with a function here? Are you planning on reusing it? ;~ - !! Instead of running the loop and running a custom function you could pass the array ;~ - !! to a custom function and do everything you want it to do. Next For $i = 2 To UBound($data) - 1 ;~ $varfirstname = $data[$i][1] - !! Why are you redeclaring this array to the variables? It hasn't changed... ;~ $varlastname = $data[$i][2] - !! And same question as above. ;~ $varlocation = $data[$i][3] ;~ $vartitle = $data[$i][4] ;~ $varclass = $data[$i][5] ;~ $varaction = $data[$i][6] Select Case $varaction = "new hire" MsgBox(0, '', "new hire" & @CRLF & $varfirstname & @CRLF & $varlastname & @CRLF & $varlocation & @CRLF & $vartitle & @CRLF & $varclass & @CRLF & $varaction) Case $varaction = "termination" MsgBox(0, '', "termination" & @CRLF & $varfirstname & @CRLF & $varlastname & @CRLF & $varlocation & @CRLF & $vartitle & @CRLF & $varclass & @CRLF & $varaction) Case $varaction = "move" MsgBox(0, '', "move" & @CRLF & $varfirstname & @CRLF & $varlastname & @CRLF & $varlocation & @CRLF & $vartitle & @CRLF & $varclass & @CRLF & $varaction) Case Else ;msgbox(0, '', "error") EndSelect Next ; - !! Why bother with the 2nd loop when you can do everything within the first loop? Func _ValidateData() ; Check all fields to make sure the data is valid If $varfirstname = "" Or $varlastname = "" Or $varlocation = "" Or $vartitle = "" Or $varclass = "" Or $varaction = "" Then ; !! ^^^ Again you can could just use the array here instead of these variables. _ExcelWriteCell($book, "error - " & $data[$i][6], $i, 6) ; !! So you want to overwrite the 'action' with error here? ;~ _ExcelWriteCell($book, "error 001 - 1 or more fields are blank", $i, $numcolumns + 1) _ExcelWriteCell($book, "error 001 - 1 or more fields are blank", $i, 6 + 1) ; !! And you want to inform the user that the fields are blank? Else ;~ _ExcelWriteCell($book, "passed", $i, $numcolumns + 1) _ExcelWriteCell($book, "passed", $i, 6 + 1) EndIf EndFunc _ExcelBookClose($book) You should be doing this inside the loop since it's all being done in one shot. Handle it all on a single rec. basis instead of a group basis. I'm putting together an example for you but I decided to analyze what you had first for some feedback. Link to comment Share on other sites More sharing options...
kor Posted January 19, 2011 Author Share Posted January 19, 2011 They are read into variables because I am more comfortable with variables than I am with arrays. The _validatedata() is there because i dont know any other way The redeclaration of the array is an error and I agree it doesn't need to be there twice. I just didnt pull it out for the example. Why bother with the second loop? Because I don't know how to do everything with just 1 loop. Yes, I want to append "error" in front of the "new hire" action because my case statement is looking for an exact match of "new hire". thus if there is an error in front of the new hire word then the loop will skip that row and continue on. But after the script is done I want to be able to open the excel sheet and see that a particular row had an error and possibly create that user manually. Or do other troubleshooting to figure out why there was an error. Yes I want to mark on the spreadsheet with an error so if there are programmatic mistakes I can go right to where the code is in the script and see what is going on. Link to comment Share on other sites More sharing options...
ZacUSNYR Posted January 19, 2011 Share Posted January 19, 2011 expandcollapse popup#include <Excel.au3> $file = @ScriptDir & "\test.xls" $oExcel = _ExcelBookOpen($file, 0) ; open excel in the background $data = _ExcelReadSheetToArray($oExcel, 1, 1) For $i = 2 To UBound($data) - 1 ; Loop through the main array. $checkflag = 0 ; created a 'flag' for checking if there was an error. For $n = 1 To UBound($data, 2) - 1 ; loop through each column of the array's row. If $data[$i][$n] = "" Then ; if anything is blank then do below _ExcelWriteCell($oExcel, "error - " & $data[$i][6], $i, $data[0][1]) _ExcelWriteCell($oExcel, "error 001 - 1 or more fields are blank", $i, $data[0][1] + 1) ; write the cells. $checkflag = 1 ; Set the flag to use later on. ExitLoop ; get out of the column check, no need to keep checking if we found one error. EndIf ; If exitloop was ran then this is gone, otherwise it repeats all columns till it finds an error or moves on. Next If $checkflag = 1 Then ; we triggered an error ; keep in mind we are still on the same row. Since there was an error the else below will not be executed. MsgBox(0, 'Error', $data[$i][1] & " Error") ; msgbox to let you know there was an error Else ; there is no error _ExcelWriteCell($oExcel, "passed", $i, $data[0][1] + 1) ; lets write the passed Switch $data[$i][6] ; do you still need to do this? check the action Case "new hire" _NoticeBox("new hire") ; I broke the msgbox down to a function for the sake of repeating it easily. Case "termination" _NoticeBox("termination") Case "transfer" _NoticeBox("transfer") Case "move" _NoticeBox("move") Case Else ; Doesn't match anything goes here - if you don't care about an else statement just remove the case else above as it's not needed. EndSwitch EndIf Next _ExcelBookClose($oExcel) ; close excel Func _NoticeBox($sMsg) MsgBox(0, $sMsg, $sMsg & @CRLF & $data[$i][1] & @CRLF & $data[$i][2] & @CRLF & $data[$i][3] & @CRLF & $data[$i][4] & @CRLF & $data[$i][5] & @CRLF & $data[$i][6]) EndFunc If you encounter an error - move on - the check gets ignored for 'new hire'. Check this out, I commented it for you and hopefully you can make some sense of it and it does what you're looking to do. I'm kinda having a hard time understanding what you're doing - i'd just use an excel macro lol Link to comment Share on other sites More sharing options...
kor Posted January 20, 2011 Author Share Posted January 20, 2011 It works great. The final piece of the puzzle is getting errors from different parts of the script to stack into the same column. The example of what I'm trying to accomplish can be found in the _Test() functions. Pretend the excelwritecell is if there was an error with that particular function. I would want errors to stack. So if there is only error1, than column 7 would only have error1 in it. But if function1 and function3 both had errors, then column 7 would have "error3 - error1", or if another row had 3 errors from 3 different functions it would look like "error3 - error2 - error1" (would be even better if I could append the errors to the end instead of the beginning, error1, 2, 3 instead of 3, 2, 1. expandcollapse popup; Global variables Global $excelsheet = @ScriptDir & "\test.xls" Global $errorcolumn = 7 Global $firstname, $lastname, $displayname, $userexists, $username, $ou, $varerror $book = _ExcelBookOpen($excelsheet, 0) ; open excel in background $data = _ExcelReadSheetToArray($book, 1, 1) ; ignores header row ;_ArrayDisplay($data, "Array") For $i = 2 To UBound($data) - 1 ; Loop through the main array. $error = 0 ; created a 'flag' for checking if there was an error. For $n = 1 To UBound($data, 2) - 1 ; loop through each column of the array's row. If $data[$i][$n] = "" Then ; if anything is blank then do below _ExcelWriteCell($book, "error - " & $data[$i][6], $i, $data[0][1]) _ExcelWriteCell($book, "001x000", $i, $data[0][1] + 1) ; write the cells. $error = 1 ; Set the flag to use later on. ExitLoop ; get out of the column check, no need to keep checking if we found one error. EndIf ; If exitloop was ran then this is gone, otherwise it repeats all columns till it finds an error or moves on. Next If Not $error = 1 Then ; we triggered an error _ExcelWriteCell($book, "passed", $i, $data[0][1] + 1) ; lets write the passed Switch $data[$i][6] Case "new hire" _Test() Case "termination" _Test() _Test1() Case "move" _Test() _Test1() _Test2() Case Else ; Doesn't match anything goes here - if you don't care about an else statement just remove the case else above as it's not needed. EndSwitch EndIf Next Func _Test() $varerror = _ExcelWriteCell($book, "test1", $i, $errorcolumn) EndFunc Func _Test1() $varerror = _ExcelWriteCell($book, $varerror & " test2", $i, $errorcolumn) EndFunc Func _Test2() $varerror = _ExcelWriteCell($book, $varerror & " test3", $i, $errorcolumn) EndFunc _ExcelBookClose($book) ; close excel Link to comment Share on other sites More sharing options...
ZacUSNYR Posted January 20, 2011 Share Posted January 20, 2011 Now i'm confused lol Where are these "errors" coming from? I thought we were skipping everything that had an error? Link to comment Share on other sites More sharing options...
kor Posted January 20, 2011 Author Share Posted January 20, 2011 (edited) some updates. I'm still working out how to stack errors from multiple functions into the same column and just keep appending them. Similar to a $var = $var + $text just over and over again for each error. A bigger problem I have run into though is that if you look at the code below as long as I don't have any of my functions in the case statements the script will run through all the test names in the excel spreasheet as normal and exit properly. However as soon as I try and add my _TerminateUser() function to the "terminate" case (for example) the script then just endlessly loops the first user row over and over and over again and never exiting. Very strange. expandcollapse popupGlobal $excelsheet = @ScriptDir & "\test.xls" Global $logpath = @ScriptDir Global $firstname, $lastname, $displayname, $userexists, $username, $ou $book = _ExcelBookOpen($excelsheet, 0) ; open excel in background $data = _ExcelReadSheetToArray($book, 1, 1) ; ignores header row For $i = 2 To UBound($data) - 1 $error = 0 For $n = 1 To UBound($data, 2) - 1 If $data[$i][$n] = "" Then _ExcelWriteCell($book, "error-" & $data[$i][6], $i, $data[0][1]) _ExcelWriteCell($book, "001x000", $i, $data[0][1] + 1) msgbox(0, '', "a field has a blank value") $error = 1 ExitLoop Else ; continue EndIf Next If Not $error = 1 Then _GetUsername() _DoesUserExist() Switch $data[$i][6] Case "new hire" If $userexists = False Then msgbox(0, '', "run all user create functions") Else msgbox(0, '', "user functions- user already exists") _ExcelWriteCell($book, "004x000", $i, $data[0][1] + 1) EndIf Case "termination" If $userexists = True Then msgbox(0, '', "terminate functions for " & $username) _TerminateUser() Else msgbox(0, '', "cant terminate, user doesnt exist") _ExcelWriteCell($book, "005x000", $i, $data[0][1] + 1) EndIf Case "move" If $userexists = True Then msgbox(0, '', "move user functions") Else msgbox(0, '', "cant move user, user does not exist") _ExcelWriteCell($book, "005x000", $i, $data[0][1] + 1) EndIf Case Else msgbox(0, '', "some other kind of weird error in main function") _ExcelWriteCell($book, "000x000", $i, $data[0][1] + 1) EndSwitch EndIf Next ; Close connection to Excel _ExcelBookClose($book) Func _TerminateUser() ; Change password $randompassword = Random(0000000001, 9999999998, 1) Global $setpassword = _AD_SetPassword($username, $randompassword) If $setpassword = 1 Then ; success msgbox(0, '', "set random password") ElseIf @error = 1 Then _ExcelWriteCell($book, "005x010.1", $i, $data[0][1] + 1) Else _ExcelWriteCell($book, "000x010.1 - " & @error, $i, $data[0][1] + 1) EndIf ; Unexpire password (just in case) Global $unexpirepassword = _AD_SetPasswordExpire($username, "-1") If $unexpirepassword = 1 Then ; success msgbox(0, '', "unexpired password") ElseIf @error = 1 Then _ExcelWriteCell($book, "005x010.2", $i, $data[0][1] + 1) Else _ExcelWriteCell($book, "000x010.2 - " & @error, $i, $data[0][1] + 1) EndIf ; Disable account Global $disableuser = _AD_DisableObject($username) If $disableuser = 1 Then ; success msgbox(0, '', "disabled account") ElseIf @error = 1 Then _ExcelWriteCell($book, "005x010.3", $i, $data[0][1] + 1) Else _ExcelWriteCell($book, "000x010.3 - " & @error, $i, $data[0][1] + 1) EndIf ; Remove user from all groups $groups = _AD_GetUserGroups($username) $i = 1 If $groups = "" Then ; do nothing msgbox(0, '', "user is in no groups") Else While $i <= $groups[0] _AD_RemoveUserFromGroup($groups[$i], $username) $i = $i + 1 WEnd EndIf msgbox(0, '', "removed from all groups") msgbox(0, '', "finished termination") EndFunc ;==> _TerminateUser EDIT: Figured it out. The endless loop was being caused by a While loop inside the function also using the var $i as it's step variable where $i was being used elsewhere. Changed $i to $x and solved problem. Still working on the whole stacking errors though Edited January 20, 2011 by kor 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