Sign in to follow this  
Followers 0
kor

ExcelWriteCell doesnt actually commit changes until ExcelBookClose ??

11 posts in this topic

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.

Share this post


Link to post
Share on other sites



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

:x 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.

Share this post


Link to post
Share on other sites

#3 ·  Posted (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 :x (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 by kor

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Global $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

Posted Image

Share this post


Link to post
Share on other sites

I commented inside your script - all comments that start with !! are mine :x

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

#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

Share this post


Link to post
Share on other sites

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.

; 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

Share this post


Link to post
Share on other sites

Now i'm confused lol

Where are these "errors" coming from? I thought we were skipping everything that had an error?

Share this post


Link to post
Share on other sites

#11 ·  Posted (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.

Global $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 by kor

Share this post


Link to post
Share on other sites

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 account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0