Ceej Posted June 9, 2008 Share Posted June 9, 2008 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now