Looping through Array multiple times in For/Next loop

[Lotheron]

Hello all!

So I wrote script that loops through a column in a spreadsheet that is prepopulated in an array. Everything works great, except for that it will only run once without error. I've double and triple checked that I'm not replacing the array variable with anything, so the array object should still be intact. However, the second time the loop runs in a While 1 loop, it gives the error indicating that the array doesn't have any data. I've been through the code numerous times and cannot for the life of me figure out why it's not seeing any data in the array the second time.

expandcollapse popup

;Attempt to Reinstall Client on machines

#include <File.au3>
#Include <Date.au3>
#Include <Excel.au3>
#include <Array.au3>


;Get Non Compliant List
$sFilePath = @ScriptDir & "\SCCM_Client_Reinstall_log.xls"
$oExcel = _ExcelBookOpen($sFilePath, 1, False)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist")
    Exit
EndIf

;Read Contents to Array


 $nonCompliantList = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 2, True)
   
    If IsArray($nonCompliantList) Then 
     Local $iMax = UBound($nonCompliantList)
 EndIf
 

 
While 1
   If IsArray($nonCompliantList) Then 
    FOR $i =  1 to $iMax
        $timeElapsed = 0
        If NOT StringInStr($nonCompliantList[$i][1], "Client") Then
          WinWaitClose("Remote Script", "" , 60)
          Run(@ScriptDir & "\RemoteScript.exe")
         WinWait("Remote Script")
          WinActivate("Remote Script")
         ControlFocus("Remote Script", "", 1001)
          Send($nonCompliantList[$i][0])
          ControlClick("Remote Script", "OK" ,1007)
              While NOT StringinStr(WinGetText("Remote Script", ""), "end thread")
                Sleep(1000)
                If $timeElapsed >= 1000 Then ExitLoop
                $timeElapsed += 1
           WEnd
                If StringInStr(WinGetText("Remote Script", ""), "(0x0)") Then
                        _ExcelWriteCell($oExcel, "Client Reinstalled", $i, 2)
                    ElseIf StringInStr(WinGetText("Remote Script", ""), "(0x574)") Then
                        _ExcelWriteCell($oExcel, "Logon Error", $i, 2)
                    ElseIf StringInStr(WinGetText("Remote Script", ""), "(0x35)") Then
                        _ExcelWriteCell($oExcel, "Unable to Connect / Offline", $i, 2)
                    Else 
                        _ExcelWriteCell($oExcel, "Unknown Error", $i, 2)
                    EndIf
            _ExcelWriteCell($oExcel, @YEAR & "-" & @MON & "-" & @MDAY & "  " & @HOUR & ":" & @MIN, $i, 3)       
            _ExcelBookSave($oExcel)
            Sleep(2000)
            WinClose("Remote Script")
         EndIf
        Next
    EndIf
WEnd
   _ExcelBookClose($oExcel, 1, 0)

The array is populated at the beginning and From what I can tell I haven't made the mistake of replacing the data with nothing, so unless looping through the data removes the data from the array, I can't tell why it would say there's not data in there the second time. If anyone can help, I would surely appreciate it!

Edited December 30, 2009 by Lotheron

[PsaltyDS]

Well, right off the bat you are exceeding the array index by using this:

$iMax = UBound($nonCompliantList)

Remember Ubound() is a 1-based count, but the array uses 0-based indexes. So use one of these:

$iMax = UBound($nonCompliantList) - 1

;  - or - 

$iMax = $nonCompliantList[0][0] ; Contains row count from _ExcelReadSheetToArray(), column count is in [0][1].

[KaFu]

Try this:

expandcollapse popup

;Attempt to Reinstall Client on machines

#include <File.au3>
#include <Date.au3>
#include <Excel.au3>
#include <Array.au3>


;Get Non Compliant List
$sFilePath = @ScriptDir & "\SCCM_Client_Reinstall_log.xls"
$oExcel = _ExcelBookOpen($sFilePath, 1, False)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist")
    Exit
EndIf

;Read Contents to Array


$nonCompliantList = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 2, True)
If Not IsArray($nonCompliantList) Then
    _ExcelBookClose($oExcel, 1, 0)
    MsgBox(0, "Error", "Values could not be read to array from Excel.")
    Exit
EndIf


While 1
    For $i = 1 To UBound($nonCompliantList) - 1

        $timeElapsed = TimerInit()

        If Not StringInStr($nonCompliantList[$i][1], "Client") Then

            WinWaitClose("Remote Script", "", 60)
            Run(@ScriptDir & "\RemoteScript.exe")
            WinWait("Remote Script")
            WinActivate("Remote Script")
            ControlFocus("Remote Script", "", 1001)
            Send($nonCompliantList[$i][0])
            ControlClick("Remote Script", "OK", 1007)

            While Not StringInStr(WinGetText("Remote Script", ""), "end thread")
                Sleep(1000)
                If TimerDiff($timeElapsed) > 1000 * 1000 Then
                    MsgBox(0, "Error", "Remote script did not end thread within 1000 seconds.")
                    ExitLoop 2
                EndIf
            WEnd

            If StringInStr(WinGetText("Remote Script", ""), "(0x0)") Then
                _ExcelWriteCell($oExcel, "Client Reinstalled", $i, 2)
            ElseIf StringInStr(WinGetText("Remote Script", ""), "(0x574)") Then
                _ExcelWriteCell($oExcel, "Logon Error", $i, 2)
            ElseIf StringInStr(WinGetText("Remote Script", ""), "(0x35)") Then
                _ExcelWriteCell($oExcel, "Unable to Connect / Offline", $i, 2)
            Else
                _ExcelWriteCell($oExcel, "Unknown Error", $i, 2)
            EndIf

            _ExcelWriteCell($oExcel, @YEAR & "-" & @MON & "-" & @MDAY & "  " & @HOUR & ":" & @MIN, $i, 3)
            _ExcelBookSave($oExcel)

            Sleep(2000)

            WinClose("Remote Script")

        EndIf
    Next

WEnd
_ExcelBookClose($oExcel, 1, 0)

[Lotheron]

Well, right off the bat you are exceeding the array index by using this:

$iMax = UBound($nonCompliantList)

Remember Ubound() is a 1-based count, but the array uses 0-based indexes. So use one of these:

$iMax = UBound($nonCompliantList) - 1

;  - or - 

$iMax = $nonCompliantList[0][0] ; Contains row count from _ExcelReadSheetToArray(), column count is in [0][1].

Try this:

expandcollapse popup

;Attempt to Reinstall Client on machines

#include <File.au3>
#include <Date.au3>
#include <Excel.au3>
#include <Array.au3>


;Get Non Compliant List
$sFilePath = @ScriptDir & "\SCCM_Client_Reinstall_log.xls"
$oExcel = _ExcelBookOpen($sFilePath, 1, False)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist")
    Exit
EndIf

;Read Contents to Array


$nonCompliantList = _ExcelReadSheetToArray($oExcel, 1, 1, 0, 2, True)
If Not IsArray($nonCompliantList) Then
    _ExcelBookClose($oExcel, 1, 0)
    MsgBox(0, "Error", "Values could not be read to array from Excel.")
    Exit
EndIf


While 1
    For $i = 1 To UBound($nonCompliantList) - 1

        $timeElapsed = TimerInit()

        If Not StringInStr($nonCompliantList[$i][1], "Client") Then

            WinWaitClose("Remote Script", "", 60)
            Run(@ScriptDir & "\RemoteScript.exe")
            WinWait("Remote Script")
            WinActivate("Remote Script")
            ControlFocus("Remote Script", "", 1001)
            Send($nonCompliantList[$i][0])
            ControlClick("Remote Script", "OK", 1007)

            While Not StringInStr(WinGetText("Remote Script", ""), "end thread")
                Sleep(1000)
                If TimerDiff($timeElapsed) > 1000 * 1000 Then
                    MsgBox(0, "Error", "Remote script did not end thread within 1000 seconds.")
                    ExitLoop 2
                EndIf
            WEnd

            If StringInStr(WinGetText("Remote Script", ""), "(0x0)") Then
                _ExcelWriteCell($oExcel, "Client Reinstalled", $i, 2)
            ElseIf StringInStr(WinGetText("Remote Script", ""), "(0x574)") Then
                _ExcelWriteCell($oExcel, "Logon Error", $i, 2)
            ElseIf StringInStr(WinGetText("Remote Script", ""), "(0x35)") Then
                _ExcelWriteCell($oExcel, "Unable to Connect / Offline", $i, 2)
            Else
                _ExcelWriteCell($oExcel, "Unknown Error", $i, 2)
            EndIf

            _ExcelWriteCell($oExcel, @YEAR & "-" & @MON & "-" & @MDAY & "  " & @HOUR & ":" & @MIN, $i, 3)
            _ExcelBookSave($oExcel)

            Sleep(2000)

            WinClose("Remote Script")

        EndIf
    Next

WEnd
_ExcelBookClose($oExcel, 1, 0)

PsaltyDS: I got it, it's not failing at the start of the next round but the end of the first round.

Kafu: I will try that, Thanks! I didn't know Timerinit() existed and makes much more sense to accomplish what I want it to do.

I'm currently running through a manual loop in my list (because this project has to get done). I just want it to run indefinetly. And no I realize my _ExcelBookClose($oExcel, 1, 0) is pointless . I believe I put it there before I wrapped it all into a While 1.

Thanks all!