Jump to content
Sign in to follow this  
nooneclose

[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

Share this post


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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By MiKa666
      Hi all,
      I'm developing a tool for exploratively testing another software. For that I wanna cover all the GUI functionality from that software and verify everything works as intended. But now I've a problem which I couldnt solve so far.
      One thing that software I'm testing does, is to load up either a single picture or a series of pictures. Depending on how much pictures got loaded, further actions will be proceeded...
      This loaded pictures are schon within a ToolbarWindow and they're clickable. What I need to know is, how much pictures are in this ToolbarWindow...
      Can anybody provide me a tip on how to achive this?
      Would be amazing!!
      BR,
      Mic
    • By meety
      Hello!
      wingettext function can get the text content of the IE browser page, but the text content of the page cannot be obtained in the chrome browser? What should I do?
    • By Cristin
      Dear all,
      first of all hi.
      I`m in trouble, again 😕
      I can`t find right solution, tried all possible combinations generated by my mind and nothing.
      I need to copy range of values depending by values from two different cells in excel sheet.
      I put an example in attachment.

      I have two values which is never changed (is changed only TEXT). I need to copy all the rows from VALUE 1 to VALUE 2 in new Sheets (Output 1, Output 2, ... , Output X).
      It is possible to do this using Autoit? 🤔
      Also in attachment you can find last example which I have tried and it`s something near, it is copying by _Excel_RangeFind row with needed VALUE 1 but I need to copy whole range from VALUE 1 to VALUE 2 😵 ( see in attachment examples).
      Thank you very much, in advance, for your support, solution really will make my life much easier 🙃
      Have a nice day all of you!
      Best Regards,
      Cristin
      Book1.xlsx something.au3
    • By sudeepjd
      I was looking for a UDF using which I could Add and Update Pivot tables and Pivot Charts in Excel easily and could not find one that I could use. So I build this UDF. 
      It has the following functions :
      _ExcelPivot_CreateCache ; Easily Create a pivot table data cache from a Sheet _ExcelPivot_CreateTable ; Create a table from a cache at a specified location on the sheet _ExcelPivot_RefreshTable ; Refresh the datatable data with a new cache _ExcelPivot_AddField ; Add a Field and Aggregate function to the Datatable _ExcelPivot_AddFilter ; Adds in the Filter to a specific field _ExcelPivot_ClearFilter ; Removes the filter for a field or all the filters in the table _ExcelPivot_GetRange ; Get specific areas of a Pivot as a Range Object _ExcelPivot_AddChart ; Add a Pivot Chart linked to a specific Pivot table Attached the UDF to this post.
      Please do let me know if I can improve or add additional functions to it.
      A detailed example on the usage is below. The excel file and the example can be downloaded from the Example.zip file attached.
      #include "ExcelPivot.au3" $oExcel = _Excel_Open() $oBook = _Excel_BookOpen($oExcel, @ScriptDir & "\TestPivot.xlsx") ;Create a Sheet to put the pivot into $pSheet = _Excel_SheetAdd($oBook, -1, False, 1) $pSheet.Name = "Pivot" ;Get the cache for the pivot table $pCache = _ExcelPivot_CreateCache($oBook, "Data") ;Add in the Pivot Table from the Cache _ExcelPivot_CreateTable($pCache, $pSheet, "A1", "FruitsPivot") ;Add in the Fields into the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Category", "Filter") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Product", "Row") _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 1) ;Add in a Running total to the Pivot _ExcelPivot_AddField($pSheet, "FruitsPivot", "Amount", "Value", "Sum", 2, "PercentageRunningTotal", "Product") ;Filter only the fruits _ExcelPivot_Filter($pSheet, "FruitsPivot", "Category", "Fruit") ;Draw a Paretto Chart $chart = _ExcelPivot_AddChart($oBook, $pSheet, "FruitsPivot", "ColumnClustered", "Paretto", "E2", 570) $chart.Chart.FullSeriesCollection(1).ApplyDataLabels $chart.Chart.FullSeriesCollection(2).ChartType = 4 ;Change the percentage to a line graph $chart.Chart.FullSeriesCollection(2).AxisGroup = 2 ;Move it to secondary axis $chart.Chart.Axes(2, 2).MaximumScale = 1 ;Adjust to scale to 100% max  
       
      ExcelPivot.au3 Example.zip
    • By DirtyJohny
      Hi everyone.Need rewrite this function how in еxample.
      Original:
      #RequireAdmin #NoTrayIcon Opt("MustDeclareVars",1) Func _a() Local $sls=ObjGet("winmgmts:{impersonationLevel=impersonate," _ &"authenticationLevel=Pkt}!\\"& _ @ComputerName&'\root\wmi'),$lss=$sls.ExecQuery _ ('SELECT * FROM WmiMonitorID'), _ $lll,$sll,$sss="",$lsl,$lls,$i,$z For $z In $lss $lsl=$z.UserFriendlyName For $i=0 To Ubound($lsl)-1 if ($lsl[$i]) Then $lll&=Chr($lsl[$i]) Next $lls=$z.SerialNumberID For $i=0 To Ubound($lls)-1 if ($lls[$i]) Then $sll&=Chr($lls[$i]) Next $sss&="Model"&@TAB&@TAB&": "&$lll&@CR&"Serial Number"&@TAB&": "&$sll&@CR&@CR $lll="" $sll="" Next MsgBox(262144,'Monitors '&$lss.Count,$sss&" "&@CR) $lss=Null $sls=Null EndFunc _a() Example:
      Func _InfoPC() Local $ObjService = ObjGet('winmgmts:{impersonationLevel = impersonate}!\\' & @ComputerName & '\root\cimv2') Local $ObjMB = $ObjService.ExecQuery('SELECT * FROM Win32_BaseBoard', 'WQL', 0x30) If IsObj($ObjService) Then For $objItemMB In $ObjMB $sInfo &= @TAB & 'Motherboard: ...... ' & $objItemMB.Product & @CRLF I'm beginner in this sphere and need  you all speak easy and simply because i'm Russian.Thanks)
×
×
  • Create New...