Jump to content
Sign in to follow this  

Script stops coloring excel rows after 250ish rows

Recommended Posts


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:

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

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

       $a = $a +1

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



Edited by BrianTheLibrarian

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