Jump to content

[SOLVED] How do I find every other Saturday in any month?


Recommended Posts

I forgot an important and needed aspect of my code. I need to be able to check for people who work every other week. Is it possible to write the dates that they work (every other week) between two date ranges? 

Example:  Bob Smith has a Start date of 8-26-2018 (that is the date he is hired) And He stops working on 12-12-18 (that's the day he goes on vacation or whatever) However his work shift is every other Saturday from 7:30 AM to 6:00 Pm.  I need to calculate every other Saturday between 8-26-18 and 12-12-18 (is this clear?)

 

here is the code I have so far. I am just missing this last part:

; Step 7
Func SendData()

    ;*******************************************************************************
    ; Sends all collected data to the Excel file in correct order for Upload
    ;*******************************************************************************
    MsgBox($MB_ICONINFORMATION, "Scheduler_Bot", "Sending Data", 2)

    ; Loop Counters
    $LoopCount   = 0
    $Array_Index = 0
    $DayIndex    = 0
    $dataIndex   = 0

    ; Counter for the day of the week
    Local $dCount = 2

    ; Counter for the numbers of Ys
    Local $yesCount = 0

    ; Excel Write Counter (VERY IMPORTANT!)
    Local $EWriteCount = 2

    ; Declare the global shift arrays (Sunday - Saturday)
    Global $ShiftDaySU[100][600]
    Global $ShiftDayM[100][600]
    Global $ShiftDayT[100][600]
    Global $ShiftDayW[100][600]
    Global $ShiftDayR[100][600]
    Global $ShiftDayF[100][600]
    Global $ShiftDayS[100][600]
    Global $sDates[400]

    While $Formatted_Names[$dataIndex] <> $Formatted_Names[$IndexRows] ; $LoopCount < $IndexRows

        $ACounter = 0 ; Array counter

        ; Gets the Start date from the array
        $Temp = $StartDate[$Array_Index]
        $TempStart = StringLeft($Temp, 8)
        $StartTempYear  = StringLeft($TempStart, 4)
        $StartTempMonth = StringMid($TempStart, 5, 2)
        $StartTempDay   = StringRight($TempStart, 2)

        ; Gets the End date from the array
        $Temp = $EndDate[$Array_Index]
        $TempEnd = StringLeft($Temp, 8)
        $EndTempYear  = StringLeft($TempEnd, 4)
        $EndTempMonth = StringMid($TempEnd, 5, 2)
        $EndTempDay   = StringRight($TempEnd, 2)

        ; The starting date (in value form)
        $sdate = _DateToDayValue($StartTempYear, $StartTempMonth, $StartTempDay)    ;_DateToDayValue(2019,1,9)
        ;ConsoleWrite(@CRLF & "$start date  " & $sdate & @CRLF & @CRLF)

        ; The ending date (in value form)
        $edate = _DateToDayValue($EndTempYear, $EndTempMonth, $EndTempDay)          ;_DateToDayValue(2019,4,9)
        ;ConsoleWrite(@CRLF & "$end date  " & $edate & @CRLF & @CRLF)

        ; Variables for readability
        Local $iYear, $iMonth, $iDay

        ;Stores what day of the week that shift lands on
        Local $tSU = _Excel_RangeRead($OpenWorkbook, Default, "I" & $dCount)
            If $tSU = "Y" Then
                $yesCount = 1
            EndIf
        Local $tM  = _Excel_RangeRead($OpenWorkbook, Default, "J" & $dCount)
            If $tM = "Y" Then
                $yesCount += 1
            EndIf
        Local $tT  = _Excel_RangeRead($OpenWorkbook, Default, "K" & $dCount)
            If $tT = "Y" Then
                $yesCount += 1
            EndIf
        Local $tW  = _Excel_RangeRead($OpenWorkbook, Default, "L" & $dCount)
            If $tW = "Y" Then
                $yesCount += 1
            EndIf
        Local $tR  = _Excel_RangeRead($OpenWorkbook, Default, "M" & $dCount)
            If $tR = "Y" Then
                $yesCount += 1
            EndIf
        Local $tF  = _Excel_RangeRead($OpenWorkbook, Default, "N" & $dCount)
            If $tF = "Y" Then
                $yesCount += 1
            EndIf
        Local $tS  = _Excel_RangeRead($OpenWorkbook, Default, "O" & $dCount)
            If $tS = "Y" Then
                $yesCount += 1
            EndIf

        ConsoleWrite(@CRLF & @CRLF)
        ConsoleWrite($tSU & @CRLF)
        ConsoleWrite($tM & @CRLF)
        ConsoleWrite($tT & @CRLF)
        ConsoleWrite($tW & @CRLF)
        ConsoleWrite($tR & @CRLF)
        ConsoleWrite($tF & @CRLF)
        ConsoleWrite($tS & @CRLF)
        ConsoleWrite(@CRLF & @CRLF)

        ; Check to see if they work every other week
        Local $rotationWeek  = _Excel_RangeRead($OpenWorkbook, Default, "U" & $dCount)
            If $rotationWeek = "0" Then
                ; Do nothing
            Else
                If $rotationWeek = 1
                    ; Do something
                Else ; $rotationWeek = 2
                    ; Do something
                EndIf
            EndIf

        Local $repeatWeek  = _Excel_RangeRead($OpenWorkbook, Default, "V" & $dCount)
            If $rotationWeek = "0" Then
                ; Do nothing
            Else
                If $rotationWeek = 1
                    ; Do something
                Else ; $rotationWeek = 2
                    ; Do something
                EndIf
            EndIf

        While $yesCount > 0

            If $tSU = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 1 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDaySU[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDaySU[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "SU " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Sunday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tSU = "N"

            ElseIf $tM = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 2 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDayM[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayM[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "M " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Monday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tM = "N"

            ElseIf $tT = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 3 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDayT[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayT[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "T " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Tuesday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tT = "N"

            ElseIf $tW = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 4 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDayW[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayW[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "W " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Wednesday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tW = "N"

            ElseIf $tR = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 5 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDayR[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayR[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "R " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Thursday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tR = "N"

            ElseIf $tF = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 6 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDayF[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayF[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "F " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Friday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tF = "N"

            ElseIf $tS = "Y" Then
                For $Index = $sdate To $edate
                    _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                    ; Finds the day based on its numerical value (1 = Sunday)
                    If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 7 Then
                        $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                        $ShiftDayS[$ACounter][$DayIndex] = $Temp
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayS[$ACounter][$DayIndex], "AI" & $EWriteCount)

                        ; Send Name
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                        ; Send Start Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                        ; Send End Time
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                        ; Send Work Hours
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                        ; Send Work Group
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                        ; Send Department
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                        ; Send Supervisor
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                        ; Send Notes
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                        ; Send Shift Number
                        $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                        $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                        _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "S " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                        $ACounter    += 1
                        $EWriteCount += 1

                        ConsoleWrite(@CRLF & "Saturday  " & $Temp & @CRLF)

                    EndIf
                Next

                $yesCount -= 1
                $tS = "N"

            Else
                ;Error Nothing equals "Y"
                ConsoleWrite(@CRLF & "Error Nothing equals 'Y'" & @CRLF)
            EndIf

            $DayIndex    += 1
            $LoopCount   += 1

        WEnd

        $Array_Index += 1
        $dataIndex   += 1
        $dCount      += 1

    WEnd

    MsgBox($MB_ICONINFORMATION, "Scheduler_Bot", "Finished Sending Data", 2)

EndFunc

 

Edited by nooneclose
Link to post
Share on other sites

I figured it out here is a good chunk of the finished code:

#include <Array.au3>                                ; Allows arrays
#include <date.au3>                                 ; Allows Date functions
#include <Misc.au3>                                 ; Allows the "is pressed" function to be used
#include <MsgBoxConstants.au3>                      ; Allows message box to be used
#include <Excel.au3>                                ; Allows Excel functions
#include <File.au3>                                 ; Allows file manipulation
#include <Word.au3>                                 ; Allows certain word functions
#include <Constants.au3>                            ; Allows constants to be used
#include <StaticConstants.au3>                      ; Allows static constants
#include "ExtMsgBox.au3"                            ; Allows custom MsgBoxs

Global $WeekASunday                                 ; First Sunday of the Semester always week A
Global $WeekAMonday                                 ; First Monday of the Semester always week A
Global $WeekATuesday                                ; First Tuesday of the Semester always week A
Global $WeekAWednesday                              ; First Wednesday of the Semester always week A
Global $WeekAThursday                               ; First Thursday of the Semester always week A
Global $WeekAFriday                                 ; First Friday of the Semester always week A
Global $WeekASaturday                               ; First Saturday of the Semester always week A
Global $modResult = 9999999                         ; Is what the name suggests / Given crazy numbr for a reason - see line 1027-1833

Global $today = _NowDate()                          ; Gets todays (current) date



; Step 7
Func SendData()

    ;*******************************************************************************
    ; Sends all collected data to the Excel file in correct order for Upload
    ;*******************************************************************************
    MsgBox($MB_ICONINFORMATION, "Scheduler_Bot", "Sending Data", 2)

    ; Loop Counters
    $LoopCount   = 0
    $Array_Index = 0
    $DayIndex    = 0
    $dataIndex   = 0

    ; Counter for the day of the week
    Local $dCount = 2

    ; Counter for the numbers of Ys
    Local $yesCount = 0

    ; Excel Write Counter (VERY IMPORTANT!)
    Local $EWriteCount = 2

    ; Declare the global shift arrays (Sunday - Saturday)
    Global $ShiftDaySU[100][600]
    Global $ShiftDayM[100][600]
    Global $ShiftDayT[100][600]
    Global $ShiftDayW[100][600]
    Global $ShiftDayR[100][600]
    Global $ShiftDayF[100][600]
    Global $ShiftDayS[100][600]
    Global $sDates[400]

    While $Formatted_Names[$dataIndex] <> $Formatted_Names[$IndexRows] ; $LoopCount < $IndexRows

        $ACounter = 0 ; Array counter

        ; Gets the Start date from the array
        $Temp = $StartDate[$Array_Index]
        $TempStart = StringLeft($Temp, 8)
        $StartTempYear  = StringLeft($TempStart, 4)
        $StartTempMonth = StringMid($TempStart, 5, 2)
        $StartTempDay   = StringRight($TempStart, 2)

        ; Gets the End date from the array
        $Temp = $EndDate[$Array_Index]
        $TempEnd = StringLeft($Temp, 8)
        $EndTempYear  = StringLeft($TempEnd, 4)
        $EndTempMonth = StringMid($TempEnd, 5, 2)
        $EndTempDay   = StringRight($TempEnd, 2)

        ; The starting date (in value form)
        $sdate = _DateToDayValue($StartTempYear, $StartTempMonth, $StartTempDay)    ;_DateToDayValue(2019,1,9)
        ;ConsoleWrite(@CRLF & "$start date  " & $sdate & @CRLF & @CRLF)

        ; The ending date (in value form)
        $edate = _DateToDayValue($EndTempYear, $EndTempMonth, $EndTempDay)          ;_DateToDayValue(2019,4,9)
        ;ConsoleWrite(@CRLF & "$end date  " & $edate & @CRLF & @CRLF)

        ; Variables for readability
        Local $iYear, $iMonth, $iDay

        ;Stores what day of the week that shift lands on
        Local $tSU = _Excel_RangeRead($OpenWorkbook, Default, "I" & $dCount)
            If $tSU = "Y" Then
                $yesCount = 1
            EndIf
        Local $tM  = _Excel_RangeRead($OpenWorkbook, Default, "J" & $dCount)
            If $tM = "Y" Then
                $yesCount += 1
            EndIf
        Local $tT  = _Excel_RangeRead($OpenWorkbook, Default, "K" & $dCount)
            If $tT = "Y" Then
                $yesCount += 1
            EndIf
        Local $tW  = _Excel_RangeRead($OpenWorkbook, Default, "L" & $dCount)
            If $tW = "Y" Then
                $yesCount += 1
            EndIf
        Local $tR  = _Excel_RangeRead($OpenWorkbook, Default, "M" & $dCount)
            If $tR = "Y" Then
                $yesCount += 1
            EndIf
        Local $tF  = _Excel_RangeRead($OpenWorkbook, Default, "N" & $dCount)
            If $tF = "Y" Then
                $yesCount += 1
            EndIf
        Local $tS  = _Excel_RangeRead($OpenWorkbook, Default, "O" & $dCount)
            If $tS = "Y" Then
                $yesCount += 1
            EndIf

        ConsoleWrite(@CRLF & @CRLF)
        ConsoleWrite($tSU & @CRLF)
        ConsoleWrite($tM & @CRLF)
        ConsoleWrite($tT & @CRLF)
        ConsoleWrite($tW & @CRLF)
        ConsoleWrite($tR & @CRLF)
        ConsoleWrite($tF & @CRLF)
        ConsoleWrite($tS & @CRLF)
        ConsoleWrite(@CRLF & @CRLF)

        ; Check to see if they work every other week
        Local $rotationWeek  = _Excel_RangeRead($OpenWorkbook, Default, "U" & $dCount)
        Local $repeatWeek    = _Excel_RangeRead($OpenWorkbook, Default, "V" & $dCount)

        While $yesCount > 0

            If $tSU = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 1 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDaySU[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDaySU[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "SU " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Sunday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tSU = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 1 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Sunday " & $WeekASunday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekASunday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf

            ElseIf $tM = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 2 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDayM[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayM[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "M " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Monday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tM = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 2 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Monday " & $WeekAMonday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekAMonday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf

            ElseIf $tT = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 3 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDayT[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayT[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "T " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Tuesday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tT = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 3 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Tuesday " & $WeekATuesday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekATuesday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf

            ElseIf $tW = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 4 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDayW[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayW[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "W " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Wednesday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tW = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 4 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Wednesday " & $WeekAWednesday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekAWednesday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf

            ElseIf $tR = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 5 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDayR[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayR[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "R " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Thursday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tR = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 5 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Thursday " & $WeekAThursday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekAThursday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf

            ElseIf $tF = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 6 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDayF[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayF[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "F " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Friday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tF = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 6 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Friday " & $WeekAFriday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekAFriday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf

            ElseIf $tS = "Y" Then
                If $repeatWeek = 1 Then
                    Local $MainLoopCount = 0
                    For $Index = $sdate To $edate
                        ; Send appropriate Work Shift dates $rotationWeek
                        ; Week A Shift
                        If $modResult = 0 And $MainLoopCount >= 1 Then
                            $Index += 1
                        ; Week B Shift
                        ElseIf $modResult <> 0 And $modResult <> 9999999 And $MainLoopCount >= 1 Then
                            $Index += 2
                        EndIf

                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)
                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 7 Then
                            $Temp = $iYear & "-" & $iMonth & "-" & $iDay & "T" & "00" & ":" & "00" & ":" & "00" & ":" & "000"
                            $ShiftDayS[$ACounter][$DayIndex] = $Temp
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $ShiftDayS[$ACounter][$DayIndex], "AI" & $EWriteCount)

                            ; Send Name
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Formatted_Names[$dataIndex], "AA" & $EWriteCount)
                            ; Send Start Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $StartTimes[$dataIndex], "AB" & $EWriteCount)
                            ; Send End Time
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $EndTimes[$dataIndex], "AC" & $EWriteCount)
                            ; Send Work Hours
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $HoursWorked[$dataIndex], "AD" & $EWriteCount)
                            ; Send Work Group
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkGroup[$dataIndex], "AE" & $EWriteCount)
                            ; Send Department
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkDepartment, "AF" & $EWriteCount)
                            ; Send Supervisor
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $Supervisors[$dataIndex], "AG" & $EWriteCount)
                            ; Send Notes
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, $WorkNotes[$dataIndex], "AH" & $EWriteCount)

                            ; Send Shift Number
                            $tempStime = StringReplace($StartTimes[$dataIndex], ":", "")
                            $tempEtime = StringReplace($EndTimes[$dataIndex], ":", "")
                            _Excel_RangeWrite($OpenWorkbook, $OpenWorkbook.ActiveSheet, "S " & $tempStime & "-" & $tempEtime, "AJ" & $EWriteCount)

                            $ACounter    += 1
                            $EWriteCount += 1

                            ConsoleWrite(@CRLF & "In Main loop: Saturday " & $Temp & @CRLF)

                        EndIf

                        $MainLoopCount += 1

                    Next

                    $yesCount -= 1
                    $tS = "N"
                    $modResult = 9999999
                Else
                    Local $LoopCount = 0
                    ; Get first potential work day
                    For $Index = $sdate To $edate
                        _DayValueToDate($Index, $iYear, $iMonth, $iDay)
                        ; Finds the day based on its numerical value (1 = Sunday)

                        If _DateToDayOfWeek($iYear, $iMonth, $iDay) = 7 Then
                            $TempFirstShift = $iYear & "/" & $iMonth & "/" & $iDay & " " & "00" & ":" & "00" & ":" & "00"
                        EndIf

                        ; Makes sure the For loop only loops once
                        If $LoopCount = 7 Then ExitLoop
                        $LoopCount += 1
                    Next

                    ConsoleWrite(@CRLF & "Week A Saturday " & $WeekASaturday & @CRLF)
                    ConsoleWrite(@CRLF & "Potential first shift " & $TempFirstShift & @CRLF)

                    ; Use _Datediff
                    Local $iDateCalc = _DateDiff('D', $WeekASaturday, $TempFirstShift)

                    ConsoleWrite(@CRLF & "Difference in days is " & $iDateCalc & @CRLF)

                    ; Use Mod
                    $modResult = Mod($iDateCalc, 14)
                    ConsoleWrite(@CRLF & "Mod Result " & $modResult & @CRLF)

                    ; check to see if it is suppose to be week a or week b
                    If $rotationWeek = 1 And $modResult = 0 Then
                        ; Keep going as planned
                    ElseIf $rotationWeek = 2 And $modResult = 0 Then
                        ; Something strange is in the neighborhood
                        $TempDateHolder = _DateAdd('d', 7, $TempFirstShift)

                        ConsoleWrite(@CRLF & "Correct First Shift " & $TempDateHolder & @CRLF)
                        ConsoleWrite(@CRLF & "Start date " & $sdate & @CRLF)

                        $tempStartDate = StringReplace($TempDateHolder, "/", "")

                        ; Gets the Start date from the array
                        $TempSD = $tempStartDate
                        $Temp_Start = StringLeft($TempSD, 8)
                        $Start_TempYear  = StringLeft($Temp_Start, 4)
                        $Start_TempMonth = StringMid($Temp_Start, 5, 2)
                        $Start_TempDay   = StringRight($Temp_Start, 2)

                        $sdate = _DateToDayValue($Start_TempYear, $Start_TempMonth, $Start_TempDay)
                        ConsoleWrite(@CRLF & @CRLF & "Start date After change " & $sdate & @CRLF & @CRLF)

                    EndIf
                    ; Set repeat week = 1 so the main loop will send the new dates
                    $repeatWeek = 1
                EndIf
            Else
                ;Error Nothing equals "Y"
                ConsoleWrite(@CRLF & "Error Nothing equals 'Y'" & @CRLF)
            EndIf

            $DayIndex    += 1
            $LoopCount   += 1

        WEnd

        $Array_Index += 1
        $dataIndex   += 1
        $dCount      += 1

    WEnd

    MsgBox($MB_ICONINFORMATION, "Scheduler_Bot", "Finished Sending Data", 2)

EndFunc

 

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By PeterVerbeek
      This topic give you access to an AutoIt functions library I maintain which is called PAL, Peter's AutoIt Library. The latest version 1.26 contains 214 functions divided into these topics:
      window, desktop and monitor GUI, mouse and color GUI controls including graphical buttons (jpg, png) logics and mathematics include constants string, xml string and file string dialogues and progress bars data lists: lists, stacks, shift registers and key maps (a.ka. dictionaries) miscellaneous: logging/debugging, process and system info Change log and files section  on the PAL website (SourceForge).
      A lot of these functions were created in the development of Peace, Peter's Equalizer APO Configuration Extension, which is a user interface for the system-wide audio driver called Equalizer APO.
    • By Hermes
      Hi, I am struggling in setting the value of a textarea based on the value of clipboard (that contains a long web page source codes). If I use _WD_SetElementValue, it freezes after some time, or appears to be pressing tab and goes out of focus. I can also use send keys but i need the script to run in the background.
      Here is the full script:
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "http://demo.borland.com/testsite/stadyn_largepagewithimages.html") _WD_LoadWait($sSession) Global $sSource = _WD_GetSource($sSession) Local $Paste = ClipPut($sSource) Local $sData = ClipGet() Local $aArray = 0, _ $iOffset = 1 While 1 $aArray = StringRegExp($sData, '(?s)<p>.*</p>', $STR_REGEXPARRAYMATCH, $iOffset) If @error Then ExitLoop $iOffset = @extended For $i = 0 To UBound($aArray) - 1 Local $Paste = ClipPut($aArray[$i]) Local $sRegExData = ClipGet() ;MsgBox(0, "", "$sRegExData = " & $sRegExData) Next WEnd _WD_Navigate($sSession, "https://www.w3schools.com/tags/tryit.asp?filename=tryhtml5_textarea_placeholder") _WD_WaitElement($sSession, $_WD_LOCATOR_ByCSSSelector, "iframe#iframeResult") Local $sElement1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "iframe#iframeResult") _WD_FrameEnter($sSession, $sElement1) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//html/body/textarea") $textarea = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//html/body/textarea") _WD_ElementAction($sSession, $textarea, 'click') ;WD SetElementValue(SsSession, Stextarea, $sRegExData) <-- I can do this but the focus goes out, or the browser freezes _WD_FrameLeave($sSession) sleep(2000) Send("^v") _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome Can someone help me please, or re-direct me to the right path? TIA!
    • By Rskm
      Hi, I have 5 notepad files with lot of data in each of it. The data are arranged in lines and i wish to get it pasted/copied into excel.  Say, i need to read notepad1 and paste the contents into sheet1 of excel and notepad2 to sheet2 and so on.  If i read the notepad and paste it line by line, it is taking lot of time.  Is there a way by which i can paste the whole of notepad file into excel sheet(and get it pasted line by line as shown in the attached excel), instead of using code to write it line by line?.. I was using Filewriteline(data, line i) initially to write to excel.  The 'i' value was incremented with for loop and the excel was updated, but this takes lot of time.  The expected excel format is attached here.  any help is appreciated.  thanks
      Tmp.xls
    • By DJ143
      I have a autoit exe file which is used in upload/browse file functionality.  This has been integrated with selenium framework and I am invoking the autoit exe using Java process and runtime. 
      Now the issue is when I run the scripts and invoke the autoit exe in local it works perfectly.  But when I use selenium grid or jenkins to run the scripts in another windows server it is not working.
      Can anyone please suggest any solution for this?
    • By Hermes
      Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient.
      What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row
      Appreciate any help that I can get to achieve this.
      table1.html test.xlsx
×
×
  • Create New...