Script stops coloring excel rows after 250ish rows

[BrianTheLibrarian]

Hello I am using autoit to read a excel xlsx file into an array - dewey numbers / year published for my collection of books. The other excel file (resource list) is the dewey numbers and year to compare it to.  It compares the dewey number of the book to the resource list, then when it finds a match it colors the excel row of the book based on the years on the resource list.   It works fine but it stops coloring the rows after row 250ish. There are close to 7000 books in the list. Any idea why? here is the code:

expandcollapse popup

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>

Global $z = 0
Global $a = 0
Global $b = 0

Global $keepYear, $usefulMin, $excelRange
; Create application object and open an example workbook
Global $bookData[0][0]

$hFile = FileOpen("log.txt", 2)

Local $oExcel = _Excel_Open()
Local $oDewey = _Excel_BookOpen($oExcel, @ScriptDir & "\dewey.xlsx")
$deweyArray = $oExcel.Activesheet.Range("A2:F508").Value

; Open excel file that has the book data
Local $oExcel2 = _Excel_Open()
Local $oCollection = _Excel_BookOpen($oExcel2, @ScriptDir & "\allbooks2.xlsx")

; add the book dewey number to array
$bookDeweyArray = $oExcel2.Activesheet.Range("A1:A6982").Value

; add book publish year to array
$bookPubArray = $oExcel2.Activesheet.Range("H1:H6982").Value

$deweyCount = UBound($deweyArray,2)
$bookCount = UBound($bookDeweyArray,2)

; go through all the books

while $z <= $bookCount

; the first dewey number to check, always starts at the begining and works through
$a = 0

; check to match the dewey number from the book with the resource chart
; z is the book we are checking

   while $a <= $deweyCount
      $b = $a+1
      ; has to match both conditions
      ;_ArrayDisplay ($deweyArray, 1)

      ; a and b are the dewey numbers from the resource Guide (must be >= a or < b)
      ; it is less than the second and greater or = to the first, so use the data in the first
      ; these years are pulled from the dewey resource alignment list

      if Number($bookDeweyArray[0][$z]) >= Number($deweyArray[0][$a]) and Number($bookDeweyArray[0][$z]) < Number($deweyArray[0][$b]) Then
          ; we have a match for the dewey Number

         FileWriteLine ($hFile, "Current Dewey Number: "& Number($bookDeweyArray[0][$z]) & " is between "& Number($deweyArray[0][$a]) &" and " & Number($deweyArray[0][$b]))
         ; excel row starts on 1
         $y = $z+1

         ; excel range to color when we have a match
         $excelRange =  "A"& $y &":" & "Z" & $y &""

         FileWriteLine ($hFile, "Excel Row: "& $y )

         $keepYear = Number($deweyArray[3][$a])
         $usefulMin = Number($deweyArray[4][$a])

         ; color code the excel document based on the year compared to the year range in the resource document
         Select
            Case Number($bookPubArray[0][$z]) >= $keepYear ; greater or equal to keep year, set green
              FileWriteLine ($hFile, "KEEP - Book Published: " & Number($bookPubArray[0][$z]) & ". GOOD BOOK: >=" & $keepYear)
              $oExcel2.ActiveWorkbook.Sheets(1).Range($excelRange).Interior.Color = 0x40f740

            Case Number($bookPubArray[0][$z]) < $keepYear and Number($bookPubArray[0][$z]) >= $usefulMin ; less than keep, greater or equal to min useful, set yellow
              FileWriteLine ($hFile, "USEFUL - Book Published: " & Number($bookPubArray[0][$z]) & ". Useful Max: <" & $keepYear & " or UsefulMin >= " &  $usefulMin)
              $oExcel2.ActiveWorkbook.Sheets(1).Range($excelRange).Interior.Color = 0x56ffff

            Case Number($bookPubArray[0][$z]) < $usefulMin;below discard range, set red
               FileWriteLine ($hFile, "DISCARD - Book Published: " & Number($bookPubArray[0][$z]) & ". Discard: <" & $usefulMin)
               $oExcel2.ActiveWorkbook.Sheets(1).Range($excelRange).Interior.Color = 0x1302ff

         EndSelect
         ; we have colored the row, exit the loop and go to the next book
         ExitLoop

       EndIf
       $a = $a +1
    WEnd

    FileWriteLine ($hFile, "  ")
    ; go to the next book
    $z = $z+1

 WEnd

 

Edited May 3, 2017 by BrianTheLibrarian