Speed of excel_rangeread

Flaccid_Jack · November 8, 2018

I have a script that reads four columns for each of about 1600 lines of an excel file and then re-writes the data. I have noticed that the excel_rangeread is much slower than _rangewrite, and overall it takes my script about 20 minutes to run.

Is there anyway to read excel documents faster?

Thanks in advance!

PS: I can put my script here if that’s helpful, but it probably looks like chicken scratch to anybody who’s competent; this is only my second script and I have no programming experience.

Subz · November 8, 2018

Can you post your script and an example Excel file?

Basic example for a spreadsheet with 1800 lines of data, in 6 columns, takes less than a second.

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

Local $sWorkbook = @ScriptDir & "\Workbook.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:F"))
_ArrayDisplay($aWorkbook)

Edited November 8, 2018 by Subz
Added example

Flaccid_Jack · November 8, 2018

Timesheets.xls

Edited November 9, 2018 by Flaccid_Jack

Flaccid_Jack · November 8, 2018

15 minutes ago, Subz said:
Can you post your script and an example Excel file?

Basic example for a spreadsheet with 1800 lines of data, in 6 columns, takes less than a second.
#include <Array.au3>
#include <Excel.au3>

Local $sWorkbook = @ScriptDir & "\Workbook.xlsx"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:F"))
_ArrayDisplay($aWorkbook)

How do I post my script to be more legible like this?

Subz · November 8, 2018

Just edit your post and use the <> in the toolbar

Flaccid_Jack · November 8, 2018

Well I cannot for the life of me figure out how to edit or delete my posts.

I will just repost it here and see if I can figure out how to delete my pervious post.

#include <Array.au3>
#include <Excel.au3>
#include <GUIConstantsEx.au3>
#include <Constants.au3>
#include <MsgBoxConstants.au3>
#include <WindowsConstants.au3>
#requireadmin

ProcessSetPriority(@ScriptName, $PROCESS_HIGH)

; Create application object and open an example workbook
Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Timesheets.xls")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Workbook needs to be named 'timsheets'" & @ScriptDir & "\Extras\_test.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
 EndIf

HotKeySet("{CAPSLOCK}", "TogglePause")
HotKeySet("{ESC}", "Terminate")

Local $gui = GUICreate("Welcome to Pay-Bot 9000", 300, 200)
GUICtrlCreateLabel("Enter Week# for Data", 30, 10)
Local $idFile = GUICtrlCreateInput("", 200, 5, 60, 20)
Local $idBtn = GUICtrlCreateButton("Submit Entry and next", 30, 115, 150, 30)
GUISetState(@SW_SHOW)

; Loop until the user exits.
While 1
   Switch GUIGetMsg()
      Case $GUI_EVENT_CLOSE
            $loop = 2
            Exit
      Case $idBtn
      Global $sWeekNum = GUICtrlRead($idFile)
      If $sWeekNum <> 1 and $sWeekNum <> 2 Then MsgBox("","ERROR!", "You have entered an incorrect week number. You entered: " & $sWeekNum)
            ExitLoop
   EndSwitch
WEnd
GUIDelete($gui)
Local $aSped[400] = [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,27,28,30,31,33,34,35,36,43,47,50,54,62,65,99,101,102,103,104,106,107,108,110,112]
Local $aBigBus[400] = [26,29,32,37,38,39,40,41,42,44,45,46,48,49,51,52,53,55,56,57,58,59,60,61,63,64,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,100,105,109,111]
$sEnd2 = _Excel_RangeRead($oWorkbook, Default, "BW1") ;blank cell for reference
$sEnd = False
Global $sTNT, $sJobtype, $sTime
$sEnd3 = 0
;Global $aMG2[3000][25] DISABLED
Global $sX = 6, $sX2 = 2, $sMG=0, $sEIN = 0, $sMGBool = False
Global $sWeeklyHoursDriverSchool = 0, $sWeeklyHoursSchoolCharter = 0, $sWeeklyHoursPublicCharter = 0, $sWeeklyHoursAide = 0, $sWeeklyHoursAidePublic = 0, $sWeeklyHoursWash = 0, $sWeeklyHoursTraining = 0, $sWeeklyHoursAdmin = 0, $sWeeklyHoursShuttle = 0, $sOvertimeHours = 0
Global $aEIN[3000], $aBoolean[3000], $aWAideCharter[3000], $aMGA[3000], $aMGD[3000], $aMGSC[3000], $aWHDS[3000], $aWHSC[3000], $aWHAide[3000], $aWHM[3000], $aWHW[3000], $aWHWO[3000], $aWHT[3000], $aWHB[3000], $aWHA[3000], $aWHC[3000], $aWHH[3000], $aWHV[3000], $aWHO[3000], $aCHours[3000], $aCPay[3000], $aPD[3000]
MsgBox("","WARNING","Warning: All Vacation and Holiday hours need to be input manually at this time.")
MsgBox("","FYI","All West Fargo drivers are getting 2 hours MG.")
MsgBox("","FYI","The newest bus is bus 112.")
MsgBox("","FYI","Maria requires manual entry.")
While $sEnd = False
   Local $sJobtypebool = False
   Local $sBigBusBool = False
   Local $sSpedBool = False
   $sJobtype = _Excel_RangeRead($oWorkbook, Default, "M" & $sX) ;read job type
   $sTime = _Excel_RangeRead($oWorkbook, Default, "L" & $sX) ;check shift time
   If $sJobtype = $sEnd2 Then
      $sEnd3 = $sEnd3 + 1 ;If blank space is found, then terminate
      If $sEnd3 = 5 Then $sEnd = True
   Else
      If $sTime > .08 Then
         $sBustype = _Excel_RangeRead($oWorkbook, Default, "AF" & $sX) ;read bus type
         $sEIN = _Excel_RangeRead($oWorkbook, Default, "B" & $sX) ;read employee id number
         fPositions() ;add shift time to appropriate position
         $sSped = _ArraySearch($aSped, $sBustype) ;find out if sped
         If Not @error Then $sSpedBool = True ;set bustype boolean (sped)
         $sBigBus = _ArraySearch($aBigBus, $sBustype) ;find out if big bus
         If Not @error Then $sBigBusBool = True ;set bustype boolean(bigbus)
         If $sBustype <> "N/A" And $sBustype <> 766 And $sBustype <> " " And $sBigBusBool = False And $sSpedBool = False Then MsgBox("", "BE ALERTED!", "An unknown bus number has been encountered on line " & $sX)
         $aBoolean[$sEIN] = 1
         $sTNT = _Excel_RangeRead($oWorkbook, Default, "P" & $sX) ;read notes for TNT
         If $sTNT <> "tnt" And $sTNT <> "no min" And $sTNT <> "sb" And $sTNT <> "no minimum" And $sTNT <> "nomin" Then
            If $sJobtype = 2 Or $sJobtype = 3 Then ;if route is a charter, calculate charter MG and skip other mg calculations
               $sMG = 1 - $sTime ;compute MG
               If $sMG > 0 Then $aMGSC[$sEIN] = $aMGSC[$sEIN] + $sMG ;add Driver MG to employee ID
            ElseIf $sJobtype = 20 Then ;MG for West Fargo
               $sMG = 2 - $sTime ;compute MG
               If $sMG > 0 Then $aMGD[$sEIN] = $aMGD[$sEIN] + $sMG ;add Driver MG to employee ID
            ElseIf $sJobtype = 11 Then ;Skip training
            Else
               If $sSpedBool = True Then ;calculate MG for sped
                  $sMG = 1.5 - $sTime ;compute MG
                  If $sMG > 0 Then ;record MG
                     Global $sMGRate = _Excel_RangeRead($oWorkbook, Default, "M" & $sX)
                     If $sMGRate = 1 Then
                        $aMGD[$sEIN] = $aMGD[$sEIN] + $sMG ;add Driver MG to employee ID
                     ElseIf $sMGRate = 4 Then
                        $aMGA[$sEIN] = $aMGA[$sEIN] + $sMG ;add Aide MG to employee ID
                     ElseIf $sMGRate = 23 Then
                        $aMGA[$sEIN] = $aMGA[$sEIN] + $sMG ;add Aide MG to employee ID
                     Else
                         MsgBox("", "MG Anomaly", "An MG cannot be calculated for line " & $sX)
                     EndIf
                  EndIf
               ElseIf $sBigBusBool = True Then ;calculate MG for bigbus
                  $sMG = 2 - $sTime ;compute MG
                  If $sMG > 0 Then ;record MG
                     Global $sMGRate = _Excel_RangeRead($oWorkbook, Default, "M" & $sX)
                     If $sMGRate <> 1 And $sMGRate <> 23 And $sMGRate <> 4 And $sMGRate <> 6 Then fMGAnomaly()
                     If $sMGRate = 1 Then
                        $aMGD[$sEIN] = $aMGD[$sEIN] + $sMG ;add Driver MG to employee ID
                     ElseIf $sMGRate = 4 Then
                        $aMGA[$sEIN] = $aMGA[$sEIN] + $sMG ;add Aide MG to employee ID
                     ElseIf $sMGRate = 23 Then
                        $aMGA[$sEIN] = $aMGA[$sEIN] + $sMG ;add Aide MG to employee ID
                     Else
                         MsgBox("", "MG Anomaly", "An MG cannot be calculated for line " & $sX)
                     EndIf
                  EndIf
               EndIf
            EndIf
         EndIf
      EndIf
   EndIf
   $sX = $sX + 1
WEnd

fGUI()
fOvertime()
fImport()

MsgBox($MB_SYSTEMMODAL, "Payroll", "Process is finised." & @CRLF & "Have a beautiful day!")

Func fMGAnomaly()
EndFunc

Func fPositions()
   $aEIN[$sEIN] = $sEIN
   If $sJobtype = 10 Then
      If $sEIN <> 1108 And $sEIN <> 1281 And $sEIN <> 1354 And $sEIN <> 650 And $sEIN <> 1044 Then
         MsgBox("", "Washbay OT", "An employee has clocked in for Washbay OT on line " & $sX & ". Please make sure the person has an alternate payrate for Washbay OT")
      EndIf
   EndIf
   If $sJobtype = 1 or $sJobtype = 20 Or $sJobtype = 22 Then
      $aWHDS[$sEIN] = $aWHDS[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 2 Or $sJobtype = 3 Then
      $aWHSC[$sEIN] = $aWHSC[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 4 Or $sJobtype = 6 Or $sJobtype = 23 Then
      $aWHAide[$sEIN] = $aWHAide[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 7 Then
      $aWHM[$sEIN] = $aWHM[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 9 Then
      $aWHW[$sEIN] = $aWHW[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 5  Then
      $aWAideCharter[$sEIN] = $aWAideCharter[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 10 Then
      $aWHWO[$sEIN] = $aWHWO[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 11 Then
      $aWHT[$sEIN] = $aWHT[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 12 Then
      $aWHB[$sEIN] = $aWHB[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 13 Then
      $aWHA[$sEIN] = $aWHA[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 14 Then
      $aWHC[$sEIN] = $aWHC[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 15 Then
      $aWHH[$sEIN] = $aWHH[$sEIN] + $sTime ;calculate weekly hours
   ElseIf $sJobtype = 16 Then
      $aWHV[$sEIN] = $aWHV[$sEIN] + $sTime ;calculate weekly hours
   Else
      If $sJobtype <> $sEnd2 Then MsgBox("","ATTENTION!", "Unknown job code encountered on line" & $sX)
   EndIf
EndFunc

Func fOvertime()
   $sY = 0
   For $sY = 0 To 2999
      $sWHO1 = $aWHDS[$sY] + $aWHSC[$sY] + $aWHAide[$sY] + $aWHM[$sY] + $aWHW[$sY] + $aWHT[$sY] + $aWHB[$sY] + $aWHA[$sY] + $aWHC[$sY] + $aCHours[$sY] + $aWAideCharter[$sY]
      $sWHO2 = $sWHO1 - 40
      If $sWHO2 > 0 Then
         $aWHO[$sY] = $sWHO2 + $aWHO[$sY]
      EndIf
   Next
EndFunc

 Func fImport()
   $sY = 0
   Local $aSheet2 = _Excel_SheetAdd($oWorkbook, -1, False, 1, "Import")
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "Key", "A1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Hourly Regular_Hours", "B1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Blended Overtim_Hours", "C1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Min Guaran_Hours", "D1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Holiday_Hours", "E1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Vacation_Hours", "F1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Hourly Regular_WeekNumber", "G1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Blended Overtim_WeekNumber", "H1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Driver Coach_Hours", "I1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Hourly Regular_Dollars", "J1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "E_Per Diem_Dollars", "K1") ;write headers
   _Excel_RangeWrite ($oWorkbook, $aSheet2, "LaborValue1", "L1") ;write headers
   For $sY = 0 To 2999
      If $aEIN[$sY] <> 768 And $aEIN[$sY] <> 956 And $aEIN[$sY] <> 839 And $aEIN[$sY] <> 503 And $aEIN[$sY] <> 1244 And $aEIN[$sY] <> 123 Then ;Employee exceptions (salaried): Bill Muck, Julie Tackett, Sue Jensen, Maria Dumenko(not salaried), John McLaughlin, and Cordell Sinding
         If $aBoolean[$sY] = 1 Then
            If $aMGA[$sY] <> 0 Then  ;if MG for aides <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aMGA[$sY], "D" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "AidDlyScho", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aMGD[$sY] <> 0 Then ;if MG for drivers <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aMGD[$sY], "D" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "DrvrDlySch", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aMGSC[$sY] <> 0 Then ;if hours for mechanics <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aMGSC[$sY], "D" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "DrvrSchool", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHDS[$sY] <> 0 Then ;if hours for drivers <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHDS[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "DrvrDlySch", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWAideCharter[$sY] <> 0 Then ;if hours for aide charters <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWAideCharter[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "AidDlyChrt", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHSC[$sY] <> 0 Then ;if hours for charters <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHSC[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "DrvrSchool", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHAide[$sY] <> 0 Then ;if hours for aides <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHAide[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "AidDlyScho", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHM[$sY] <> 0 Then ;if hours for mechanics <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHM[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Mechanic", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHW[$sY] <> 0 Then ;if hours for wash bay <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHW[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Wash Bay", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHWO[$sY] <> 0 Then ;if hours for wash bay overtime <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHWO[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Wash BayOT", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHT[$sY] <> 0 Then ;if hours for training <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHT[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Training", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHB[$sY] <> 0 Then ;if hours for body shop <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHB[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Body Shop", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHA[$sY] <> 0 Then ;if hours for Admin <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHA[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Admin", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHC[$sY] <> 0 Then ;if hours for Cleaning <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHC[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Cleaning", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aWHH[$sY] <> 0 Then ;if hours for holiday <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHH[$sY], "E" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Needs Manual Entry", "L" & $sX2)
               MsgBox("","","WARNING, HOLIDAY NEEDS Manual Entry")
               $sX2 = $sX2 + 1
            EndIf
            If $aWHV[$sY] <> 0 Then ;if hours for vacation <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHV[$sY], "F" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Needs Manual Entry", "L" & $sX2)
               MsgBox("","","WARNING, VACATION NEEDS Manual Entry")
               $sX2 = $sX2 + 1
            EndIf
            If $aWHO[$sY] <> 0 Then ;if hours for overtime <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aWHO[$sY], "C" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "OT", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "H" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
            If $aCHours[$sY] <> 0 Then ;if hours for coaches <> 0 then write
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aEIN[$sY], "A" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aCHours[$sY], "B" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aCPay[$sY], "J" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $aPD[$sY], "K" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, "Driver Coa", "L" & $sX2)
               _Excel_RangeWrite ($oWorkbook, $aSheet2, $sWeekNum, "G" & $sX2)
               $sX2 = $sX2 + 1
            EndIf
         EndIf
      EndIf
   Next
EndFunc

Func fGUI()
   $loop = 1
   While $loop = 1
      Local $gui = GUICreate("Coaches for Week " & $sWeekNum, 300, 200)
      GUICtrlCreateLabel("Enter Employee ID Number", 30, 10)
      Local $idFile = GUICtrlCreateInput("", 200, 5, 60, 20)
      GUICtrlCreateLabel("Enter Coach Hours for Week " & $sWeekNum, 30, 30)
      Local $idFile2 = GUICtrlCreateInput("", 200, 25, 60, 20)
      GUICtrlCreateLabel("Enter Coach Pay for Week " & $sWeekNum, 30, 50)
      Local $idFile3 = GUICtrlCreateInput("", 200, 45, 60, 20)
      GUICtrlCreateLabel("Enter Per Diem for Week " & $sWeekNum, 30, 70)
      Local $idFile4 = GUICtrlCreateInput("", 200, 65, 60, 20)
      Local $idBtn = GUICtrlCreateButton("Submit Entry and next", 30, 105, 120, 20)
      Local $idBtn2 = GUICtrlCreateButton("Finish Report", 30, 180, 90, 20)
      GUISetState(@SW_SHOW)

      ; Loop until the user exits.
      While 1
         Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
               $loop = 2
               ExitLoop
            Case $idBtn
               $sEIN = GUICtrlRead($idFile)
               $aEIN[$sEIN] = $sEIN
               $aCHours[$sEIN] = GUICtrlRead($idFile2) + $aCHours[$sEIN]
               $aCPay[$sEIN] = GUICtrlRead($idFile3) + $aCPay[$sEIN]
               $aPD[$sEIN] = GUICtrlRead($idFile4) + $aPD[$sEIN]
               $aBoolean[$sEIN] = 1
               ExitLoop
            Case $idBtn2
               $sEIN = GUICtrlRead($idFile)
               $aEIN[$sEIN] = $sEIN
               $aCHours[$sEIN] = GUICtrlRead($idFile2) + $aCHours[$sEIN]
               $aCPay[$sEIN] = GUICtrlRead($idFile3) + $aCPay[$sEIN]
               $aPD[$sEIN] = GUICtrlRead($idFile4) + $aPD[$sEIN]
               $aBoolean[$sEIN] = 1
               $loop = 2
               ExitLoop
         EndSwitch
      WEnd
      GUIDelete($gui)
   WEnd
EndFunc   ;==>Example

Func TogglePause ()
   MsgBox($MB_SYSTEMMODAL, "Payroll", "Working on line: " & $sX)
EndFunc

Func Terminate ()
   Exit
EndFunc

Timesheets.xls

Payroll bot 2.32.au3

water · November 8, 2018

To edit your own posts you have to have at least a post count of 5.

water · November 8, 2018

To enhance the speed of your script you need to reduce the number of calls to _Excel_RangeRead/_Excel_RangeWrite.
Don't read cell by cell in a loop. Read the whole used range into an array, then process the array and rewrite the array to Excel when done.
Reduces the whole processing to a few seconds.

Flaccid_Jack · November 8, 2018

Replying to get my post count up to 5 so I can clean up the mess I've made.

Flaccid_Jack · November 8, 2018

2 minutes ago, water said:

To enhance the speed of your script you need to reduce the number of calls to _Excel_RangeRead/_Excel_RangeWrite.
Don't read cell by cell in a loop. Read the whole used range into an array, then process the array and rewrite the array to Excel when done.
Reduces the whole processing to a few seconds.

Okay, thanks.

I will try that!

Subz · November 8, 2018

See water has already replied, but was going to say the same thing, use the array data than _Excel_RangeRead within a loop something like the following:

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

Local $sWorkbook = @ScriptDir & "\Timesheets.xls"
Local $oExcel = _Excel_Open()
Local $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
Local $aWorkbook = _Excel_RangeRead($oWorkbook, Default, $oWorkbook.ActiveSheet.Usedrange.Columns("A:AU"))
_ArrayDelete($aWorkbook, "0-4")
For $i = 0 To UBound($aWorkbook) - 1
    ConsoleWrite("Column A: " & $aWorkBook[$i][0] & @CRLF)
Next
_ArrayDisplay($aWorkbook)

Flaccid_Jack · November 8, 2018

It's going to take some time for me to convert my whole script but I can already tell it's working and will be much better!

Much thanks to both of you!

Speed of excel_rangeread

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members