Automatic Entry Script dies after 438 entries

[Ceej]

Hey everyone, nice program here. I have been using it in work a good bit.

Ended up making a script that takes a csv file and creates entries in a job/schedule program.

However, the script always seems to end around line 438. Not sure if it's a problem with the script or the windows program we're entering data to.

It just, stops. Neither thing crashes, and a close/rerun of the script works fine...for another 438 runs.

I don't have any idea why this happens, I'm thinking I must be making some kind of rogue array data which after 400 runs makes it do the subscript range exceeded error. But if it did - the program would close, which it doesn't. I'm rambling now.

If you could have a look through my code and give me any direction it would be great. Hoping my comments are a good enough explanation of what I'm trying to achieve. Also, one of my first big scripts, any pointers? Bad habits forming?

Ceej

CODE

#include <SQLite.au3>

#include <SQLite.dll.au3>

#include <array.au3>

#include <Date.au3>

#include <File.au3>

Opt("SendKeyDelay", "75")

HotKeySet("{ESC}", "exitl")

If MsgBox(1, "Job Import", "Employee import initiated. Reading from JobImportCsv.csv in current directory. Ok?") = 2 Then

MsgBox(0, "Employee Import", "Import cancelled by user.")

Exit

EndIf

$LogFile = FileOpen("JobImportLog.log", 1)

If $LogFile = -1 Then

MsgBox(0, "Error", "Unable to open JobLog file.")

Exit

EndIf ; Might use this in the future

$JobImportFile = FileOpen("JobImportCsv.csv", 0)

If $JobImportFile = -1 Then

MsgBox(0, "Error", "Unable to open Job file.")

Exit

EndIf

_SQLite_Startup()

If @error > 0 Then

MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!")

Exit -1

EndIf

_SQLite_Open("Import.db") ; Holds import data in alphabetical order

If @error > 0 Then

MsgBox(16, "SQLite Error", "Can't Load Database!")

Exit -1

EndIf

; init counting vars

$i = 1

$lines = _FileCountLines(@ScriptDir & "\jobimportcsv.csv")

;Scan

While $i < $lines

$JobImportLine = FileReadLine($JobImportFile)

If @error = -1 Then ExitLoop ; IF EOF is reached exit loop

$Column = StringSplit($JobImportLine, ",") ; read the first line into array $Column

; Validation

If Not $Column[0] = "11" Then

MsgBox(0, "Error", "Comma on line: " & $i)

Exit

EndIf

; It's ok for column 1 to be blank in this version

; If $Column[1] = "" Then

; MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 1")

; Exit

; EndIf

If $Column[2] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 2")

Exit

EndIf

If $Column[3] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 3")

Exit

EndIf

If $Column[4] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 4")

Exit

EndIf

If $Column[5] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 5")

Exit

EndIf

If $Column[6] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 6")

Exit

EndIf

; If $Column[7] = "" Then

; MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 7")

; Exit

; EndIf

If $Column[8] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 8")

Exit

EndIf

; If $Column[9] = "" Then

; MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 9 (Daycode)")

; Exit

; ElseIf StringLen($Column[9]) > 7 Then

; MsgBox(0, "Error", "Daycode column has more than 7 days in line: " & $i)

; Exit

; EndIf

If $Column[10] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 10")

Exit

EndIf

If $Column[11] = "" Then

MsgBox(0, "Error", "Blank at line: " & $i & @CR & "in Column 11")

Exit

EndIf

$i = $i + 1

WEnd

; Reopen File to reset position after first scan

FileClose($JobImportFile)

$JobImportFile = FileOpen("JobImportCsv.csv", 0)

If $JobImportFile = -1 Then

MsgBox(0, "Error", "Unable to open Job file the second time.")

Exit

EndIf

; Scan to create Man Hour rules.

#cs

While $i < $lines

$j = $j + 1

$JobImportLine = FileReadLine($JobImportFile)

If @error = -1 Then ExitLoop ; IF EOF is reached exit loop

$Column = StringSplit($JobImportLine, ",") ; read the first line into array $Column

$CurrentLineDate = $Column[6]

Msgbox(0, "", $CurrentLineDate, & $LastLineDate)

$LastLineDate = $Column[6]

$i = $i + 1

WEnd

#ce

WinActivate("ServiceCEO -")

$l = 1

While WinExists("ServiceCEO -") = 1

$JobImportLine = FileReadLine($JobImportFile)

If @error = -1 Then ExitLoop ; IF EOF is reached exit loop

$Column = StringSplit($JobImportLine, ",") ; read the first line into array $Column

; $Column[1] = CustCode

; $Column[2] = Customer

; $Column[3] = SiteName

; $Column[4] = Frequency

; $Column[5] = Summary

; $Column[6] = StartDate

; $Column[7] = WO Notes

; $Column[8] = TeamName

; $Column[9] = DayCode

; $Column[10] = StartTime ; 7pm, end at 5pm

; $Column[11] = Manhours ; divide 7-5 in mins by the amount of stops in the current day

WinWaitActive("ServiceCEO -")

MouseClick("left", 190, 110)

Send($Column[2])

Sleep(500)

Send("{ENTER}")

; Wait for CustomerName window to be active

WinActivate($Column[2] & " - Customers")

WinWaitActive($Column[2] & " - Customers")

Sleep(1000)

Send("!J") ; Alt + J to Jobs

Sleep(2000)

; Waits for Jobs to be active

Send("!F")

Send("{DOWN}")

Send("{ENTER}") ; New Schedule

WinWaitActive(" - Job")

Send($Column[5]) ; Summary

Send("{TAB}")

; Location now

Local $SitesKeyDown

_SQLite_QuerySingleRow(-1, "SELECT Down FROM SitesDown WHERE SiteName='" & $Column[3] & "' AND CustomerName='" & $Column[2] & "' LIMIT 1;", $SitesKeyDown)

While $SitesKeyDown[0] > 0

Send("{DOWN}")

$SitesKeyDown[0] = $SitesKeyDown[0] - 1

WEnd

Send("{TAB}")

Send($Column[6]) ; Start Date

Send("{TAB}")

Send($Column[10]) ; Start Time

Send("{TAB}")

Send($Column[11]) ; Man Hours

Send("{TAB 8}")

; Get Team List

Local $TeamsKeyDown

_SQLite_QuerySingleRow(-1, "SELECT Down FROM TeamsDown WHERE TeamName='" & $Column[8] & "' LIMIT 1;", $TeamsKeyDown)

While $TeamsKeyDown[0] > 0

Send("{DOWN}")

Send("+{TAB}")

$TeamsKeyDown[0] = $TeamsKeyDown[0] - 1

WEnd

Sleep(500)

Send("{TAB 11}") ; To work order notes

Send($Column[7]) ; WO Notes

; Frequency next

$StartDate = StringRegExpReplace($Column[6], "/", "")

$YearN = StringTrimLeft($StartDate, 4)

$MonthN = StringTrimRight(StringTrimLeft($StartDate, 2), 4)

$DayN = StringTrimRight($StartDate, 6)

$DateNo = _DateToDayOfWeek($YearN, $MonthN, $DayN)

$DayCode = $Column[9] ; $Column[9] = $DayCode

Switch $DayCode

Case ""

; Blank

; DO NOTHING

Case Else

Local $length = StringLen($DayCode) ; = 4

Global $Day[$length] ; 0-6

Local $i = 0

EndSwitch

Switch $Column[4]

Case "365"

; case 365 daily

Send("^F")

Send("{ENTER}")

Sleep(500)

Case "312"

; 312 - 6 days a week

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("1")

While $i < UBound($Day) ; lots of fun...

$Day[$i] = StringLeft($DayCode, 1)

$DayCode = StringTrimLeft($DayCode, 1)

Send("{TAB " & $Day[$i] & "}")

Send("{SPACE}")

$i = $i + 1

Send("{ENTER}")

Sleep(500)

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{TAB}")

WEnd

Send("{ENTER}")

Sleep(500)

Case "250"

; 250 - every day Monday to Friday

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{TAB}")

Send("{UP}")

Sleep(1000)

Send("{ENTER}")

Sleep(500)

Case "251"

; 251 - any five days a week

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("1")

While $i < UBound($Day)

$Day[$i] = StringLeft($DayCode, 1)

$DayCode = StringTrimLeft($DayCode, 1)

Send("{TAB " & $Day[$i] & "}")

Send("{SPACE}")

$i = $i + 1

Send("{ENTER}")

Sleep(500)

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{TAB}")

WEnd

Send("{ENTER}")

Sleep(500)

Case "200"

; case 200 - 4 times per week

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("1")

While $i < UBound($Day)

$Day[$i] = StringLeft($DayCode, 1)

$DayCode = StringTrimLeft($DayCode, 1)

Send("{TAB " & $Day[$i] & "}")

Send("{SPACE}")

$i = $i + 1

Send("{ENTER}")

Sleep(500)

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{TAB}")

WEnd

Send("{ENTER}")

Sleep(500)

Case "156"

; 156 - 3 times per week

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("1")

While $i < UBound($Day)

$Day[$i] = StringLeft($DayCode, 1)

$DayCode = StringTrimLeft($DayCode, 1)

Send("{TAB " & $Day[$i] & "}")

Send("{SPACE}")

$i = $i + 1

Send("{ENTER}")

Sleep(500)

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{TAB}")

WEnd

Send("{ENTER}")

Sleep(500)

Case "104"

; 104 - 2 times per week

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("1")

While $i < UBound($Day)

$Day[$i] = StringLeft($DayCode, 1)

$DayCode = StringTrimLeft($DayCode, 1)

Send("{TAB " & $Day[$i] & "}")

Send("{SPACE}")

$i = $i + 1

Send("{ENTER}")

Sleep(500)

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{TAB}")

WEnd

Send("{ENTER}")

Sleep(500)

Case "52"

; 52 - once per week

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("1")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(500)

Send("{ENTER}")

Case "24"

; 24 - twice per month

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("2")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(1000)

Send("{ENTER}")

Case "17"

; 17 - three weekly

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("3")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(500)

Send("{ENTER}")

Case "12"

; 12 - once per month

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("4")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(500)

Send("{ENTER}")

Case "6"

; 6 - once every two months

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("8")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(500)

Send("{ENTER}")

Case "4"

; 4 - once every 3 months

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("12")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(500)

Send("{ENTER}")

Case "2"

; 2 - once every 6 months

Send("^F")

WinWaitActive("Schedule Frequency")

Send("{RIGHT}{SPACE}")

Send("{TAB}")

Send("26")

Send("{TAB " & $DateNo & "}")

Send("{SPACE}")

Sleep(500)

Send("{ENTER}")

Case Else

MsgBox(0, "Error", "Frequency not defined for: " & @LF & $Column[1] & "," & $Column[2] & "," & $Column[3] & @LF & "Entered as a one time job.", 10)

EndSwitch

Sleep(3000)

; Save and Close

Send("!F")

Send("{DOWN}")

Send("{ENTER}")

Sleep(2000)

Send("!F")

Send("{DOWN 4}")

Send("{ENTER}")

Sleep(2000)

$l = $l + 1

WEnd

_SQLite_Close()

_SQLite_Shutdown()

FileClose($JobImportFile)

FileClose($LogFile)

MsgBox(0, "Done!", "Done")

Func exitl()

Msgbox(0, "Script Stopped", "Script stopped at Line: " & $l & " for customer " & $Column[2] & @CR & "With data of--" & @CR & "StartDate: " & $Column[6] & @CR & "Location: " & $Column[3] & @CR & "Team: " & $Column[8])

Exit

EndFunc ;==>exitl