Jump to content
SorryButImaNewbie

_ExcelRangeFind error in line 656

Recommended Posts

SorryButImaNewbie

Good day everyone!

I'm having a problem with my underconstruction excel script (again... , sorry haven't used autoIT for a while, pretty sure this is a basic error as well)

If $xls2 = 1 Then
        $ExcelName = $aWorkBooks[2][1]
        $ExcelPath = $aWorkBooks[2][2]
        MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file path: " & $ExcelPath)
        MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file name: " & $ExcelName)
    EndIf

    If $xls3 = 1 Then
        $ExcelName = $aWorkBooks[3][1]
        $ExcelPath = $aWorkBooks[3][2]
        ;MsgBox($MB_SYSTEMMODAL, "Excel", " Excel file path: " & $ExcelPath)
    EndIf


$ExcelObject = _Excel_BookAttach($ExcelName, "filename")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Error attaching to '" & $ExcelObject & "'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_BookAttach Example 2", "Search by 'filename':" & @CRLF & @CRLF & "Successfully attached to Workbook '" & $ExcelObject & "'." & @CRLF & @CRLF & "Value of cell A2: " & $ExcelObject.Activesheet.Range("A2").Value)


$knkod = _Excel_RangeFind($ExcelObject, "knkod")
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value 'knkod' (partial match)." & @CRLF & "Data successfully searched.")
_ArrayDisplay($knkod, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment")

I ask my users, to only run one excel instances while starting my script (so I can develope a simple a script I can, and then upgrade it once I code like i was able to, once again...), thats why I dont use UBound but give an array of 4 for possible running excel detection. If its needed I can easly add to array size for now.

I check for given file extensions and also have an error+exit if I found more then 1 running excel. Of course I know that my code is pretty childish for now.

I use Excel_BookAttach to attach to the excel object, the script succesfully run, and gives back the value of A2 cell (code basicly copy pasted from help file) but then I get an error in the console that reads:

--> Press Ctrl+Alt+Break to Restart or Ctrl+Break to Stop
"C:\ToolBox\AutoIT\AutoIt3\Include\Excel.au3" (656) : ==> The requested action with this object has failed.:
$aResult[$iIndex][1] = $oMatch.Name.Name
$aResult[$iIndex][1] = $oMatch^ ERROR
->17:53:47 AutoIt3.exe ended.rc:1
+>17:53:47 AutoIt3Wrapper Finished.
>Exit code: 1    Time: 24.24

(sorry if i should have wrote this some other way)

I suspect that I screw something up with the workbook/worksheet handling or that $ExcelObject isn't an object somehow. I'm not sure, but IsObj return 1 if i check $excelobject

Edit: Maybe there is some kind of problem with the name of the excel? It has "á" in it's name, could that be a problem?

Thank you for your insight! and sorry for the elementary questions

Edited by SorryButImaNewbie
forgot something from the original post

Share this post


Link to post
Share on other sites
water

It's a bug in AutoIt 3.3.14.2 with COM error handling.
If you search the forum for this bug you will find a few hits.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (2018-10-31 - Version 1.3.4.1) - Download - General Help & Support - Example Scripts - Wiki
iCal (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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

  • Similar Content

    • SlackerAl
      By SlackerAl
      I have an issue when starting Excel with the following code
      #include <Excel.au3> #include <GUIConstantsEx.au3> Opt("GUIOnEventMode", 1) GUICreate("Excel Test", 600, 440) GUISetOnEvent($GUI_EVENT_CLOSE, "MenuExit") GUISetState(@SW_SHOW) ; Create application object Local $oExcel = _Excel_Open() If @error Then Exit MsgBox(0, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) ; sit here forever with an option to react every 10ms While 1 Sleep(10) WEnd Exit Func MenuExit() GUIDelete() Exit EndFunc If the Excel is a standard install, everything is OK. If Excel has the Kutools add-in (https://www.extendoffice.com/product/kutools-for-excel.html) installed and active the excel process runs (and is visible in task manager until killed), but it never displays. Disabling the add-in restores normal functionality.
      If I add some additional code to interact with the excel application then still nothing happens if the add-in is active. However, if Excel is started first and then the AutoIt code is run, it is able to interact with the Excel session as normal.
      Summary: The Excel add-in Kutools prevents excel being started with the _Excel_Open() command from AutoIt. Any AutoIt side work-arounds for this?
    • Morphice
      By Morphice
      Hello , 
      I am new to autoIT, I am wondering if someone could guide me in the correct path for this program. Attached are the steps for the program as well as what I currently have. Any help is greatly appreciated . Thank You. 
      * workbooks\sheets will be organized on per level basis , 1st sheet lvl 1 ,2nd sheet lvl 2 etc or workbook 1 = level 1, workbook 2 = level 2 etc. 
      ; = comments and reminders 
      #include <MsgBoxConstants.au3> #include <EditConstants.au3> #include<excel.au3> #include<Array.au3> Global Const $PatientLookupX = 320 Global Const $PatientLookupY = 64 ; down 1 and enter Global Const $PatientTextBoxX = 410 Global Const $PatientTextBoxY = 217 ; click , Ctrl + V , Enter Global Const $PHMhubX = 512 Global Const $PHMhubY = 613 ;click down 1 enter button Global Const $HealthRiskAssesmetX = 40 Global Const $HealthRiskAssesmetY = 162 Global Const $AddnewAssesmentX = 168 Global Const $AddnewAssesmentY = 98 Global Const $SelectAssesmentX = 342 Global Const $SelectAssesmentY = 98 ; Down 7 and enter Risk score new Global Const $EmptyAnswerBarX = 465 Global Const $EmptyAnswerBarY = 145 Global Const $LowriskpreventionX = 716 Global Const $LowriskpreventionY = 324 Global Const $MediumriskPreventionX = 716 Global Const $MediumriskPreventionY = 352 Global Const $HighriskPreventionX = 716 Global Const $HighriskPreventionY = 377 Global Const $CatatrosphicPreventionX = 714 Global Const $CatatrosphicPreventionY = 399 Global Const $ClosebuttonX = 1167 Global Const $ClosebuttonY = 666 Global Const $SaveRiskButtonX = 1161 Global Const $SaveRiskButtonY = 692 Global Const $ExitCPScreenX = 1339 Global Const $ExitCPScreenY = 8 Global Const $ExitpatientHubX = 1000 Global Const $ExitpatientHubY = 79 Global Const $sleepMod = 2 Global Const $sleepVal = 5000*$sleepMod Global Const $sleepLow = 200*$sleepMod Global Const $sleepMed = 1000*$sleepMod Global Const $sleepHigh = 3500*$sleepMod ;Function Open excel , read account number in column A ;------------------------------------------------------------------------------------------------------------------------------------------------------- HotKeySet("{ESC}","stopbaby") Func _WinWaitActivate($title,$text,$timeout=0) $hWnd = WinWait($title,$text,$timeout) If Not WinActive($title,$text) Then WinActivate($title,$text) WinWaitActive($title,$text,$timeout) EndFunc $i=0 While $i <=2 $i = $i+1 Local $Open_excel = _Excel_Open() Local $File_path = "D:\AutoIT\Risk_Test.xlsx" Local $Open_workbook = _Excel_BookOpen($Open_excel,$File_path) WinActivate($Open_workbook) Local $Read_account_number = _Excel_RangeRead($Open_workbook,default,"A" &$i) _Excel_Close($Open_excel,False) WEnd ;--------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoSearch() WinActivate(eClinicalWorks (Garcia,Erick) Sleep($sleepMed) MouseClick("",693,77) Send("!p") ; shortcut for patient menu Send("{DOWN}") ; down 1 send ("{Enter}") ; patient lookup Sleep($sleepMed) ;paste account number How would I do this??? Send("{Enter}") ;Once patient is found, + enter = takes you to patient hub Sleep($sleepMed) Next NavtoPHMHub() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoPHMHub() MouseClick("",$PHMhubX,$PHMhubY) Sleep($sleepLow) Send("{DOWN}") Send("{ENTER}") ;takes you to Care Plan HUB Next NavtoRiskScore() ;------------------------------------------------------------------------------------------------------------------------------------------------------- Func NavtoRiskScore() MouseClick("",$HealthRiskAssesmetX,$HealthRiskAssesmetY) ;Clicks on HealthRisk assesment Sleep($sleepLow) MouseClick("",$AddnewAssesmentX,$AddnewAssesmentY) ; Click addnew assesment Sleep($sleepLow) MouseClick("",$SelectAssesmentX,$SelectAssesmentY) ;click select assesment tab Sleep($sleepLow) Send("{DOWN 7}") Send("{ENTER}") Sleep($sleepLow) MouseClick("",$EmptyAnswerBarX,$EmptyAnswerBarY) ;Click on Empty answer bar Sleep($sleepLow) MouseClick("",$LowriskpreventionX,$LowriskpreventionY) ; selects Risk Score, Change for other types 1-6 Next NavtoNextPatient() Func NavtoNextPatient MouseClick("",$ClosebuttonX,$ClosebuttonY) MouseClick("",$SaveRiskButtonX,$SaveRiskButtonY) MouseClick("",$ExitCPScreenX,$ExitCPScreenY) MouseClick("",$ExitpatientHubX,$ExitpatientHubY) EndFunc ;Function should loop back to excel sheet, copy next account number, activate eclinicalworks, and repeat the steps ;--------------------------------------------------------------------------------------------------------------------------------------------------------- Func stopbaby() exit EndFunc Best Regards,
      Morphice
      steps for program.docx
    • SlackerAl
      By SlackerAl
      Running the first example of _Excel_RangeFind from the help file (note I have added the version MsgBox and changed the path to _Excel1.xls)
      #include <Array.au3> #include <Excel.au3> #include <MsgBoxConstants.au3> MsgBox(0, "Version", @AutoItVersion) ; Create application object and open an example workbook Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\_Excel1.xls") If @error Then MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example", "Error opening workbook '" & @ScriptDir & "\_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended) _Excel_Close($oExcel) Exit EndIf ; ***************************************************************************** ; Find all occurrences of value "37000" (partial match) ; ***************************************************************************** Local $aResult = _Excel_RangeFind($oWorkbook, "37000") If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Error searching the range." & @CRLF & "@error = " & @error & ", @extended = " & @extended) MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeFind Example 1", "Find all occurrences of value '37000' (partial match)." & @CRLF & "Data successfully searched.") _ArrayDisplay($aResult, "Excel UDF: _Excel_RangeFind Example 1", "", 0, "|", "Sheet|Name|Cell|Value|Formula|Comment") I have also created a simple new Excel file "_Excel1.xls" in my script area and added "37000" to one cell.
      I generate the error:

      I arrived at this after generating the same error within my code. I'm using AutoIt version 3.3.14.2
      Any thoughts?
       
       
       
    • Ibet
      By Ibet
      Hey all, 
      Ending day 2 of learning AutoIt, and I'm stumped. I wrote an extremely rudimentary script simulating keystrokes for reading/copying values from one excel spreadsheet and pasting them into another spreadsheet, line by line. It works, but it doesn't use any of the Excel UDFs and was just sloppy. So, I'm trying to re-write it using some Excel UDFs to not only optimize the script, but to also learn how to use the Excel UDFs. If the answer is in a help file, please explain as I'm sometimes having problems understanding the examples in the help files.
      I'm getting the error: 
      "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Read spreadsheet 1 - write spreadsheet 2-version2.au3" (25) : ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.: MsgBox(0,"Test","Test",$SourceEntry[1]) MsgBox(0,"Test","Test",^ ERROR >Exit code: 1 Time: 1.804 Here is the code:
      #include<Array.au3> #include<Excel.au3> ;-------------------Read from Source--------------------------- Local $oExcel_Source = _Excel_Open() Local $sWorkbook = "C:\Users\johndoe\Desktop\AutoIt Test\AutoIt_Testing_SOURCE.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel_Source,$sWorkbook) Local $SourceRow = 3 ;--eventually will be used to iterate through the rows, one at a time Local $SourceEntry[5] = _Excel_RangeRead($oWorkbook,Default,"A"&$SourceRow&":E"&$SourceRow) _ArrayDisplay($SourceEntry, "1D Display") ;--Displays array values correctly MsgBox(0,"Test","Test",$SourceEntry[1]) ;--Gives error, for any index in the array I want to make sure I can read the values of the array individually, before I try putting them into another document. This is because I've got to add some checks against the values already existing in the destination spreadsheet before any manipulation. I've spent the last hour or more googling that error and reading multiple posts where that error is meaning many different things, so unsure EXACTLY what the problem is. Would greatly appreciate a fix and/or explanation as well as patience with my noob-ness.
      Thanks in advance
    • nooneclose
      By nooneclose
      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  
×