Jump to content
ahha

Get excel workbook and sheet name

Recommended Posts

ahha

I think this is a very basic question, but I'm stumped after trying to solve it for weeks.  The program below illustrates the issue.  I have several instances of Excel open, each instance having several books open, each book with several sheets.  I'm able to list all this information, however I can't seem to figure out the sheet and workbook for a user selected range.  Any hints appreciated because at this point as I feel like a blind squirrel looking for a nut :(

 

#AutoIt3Wrapper_run_debug_mode=Y

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>

;Illustrate issue I'm having.  For a user seletecd range (possibly multiple $oWorkbooks open with multiple sheets), I need to determine the Excel application object of the selected cells and the sheet
;I need $oWorkbook, $WorkSheet, $Range

;Simulate issue - in real world user may have opened Excel and I have no knowledge of the object
$oExcel1 = _Excel_Open()        ;open first instance
_Excel_BookNew($oExcel1)        ;workbook with 3 sheets
_Excel_BookNew($oExcel1)        ;another workbook in same instance with 3 sheets

$oExcel2 = _Excel_Open(Default, Default, Default, Default, True)    ;open second instance
_Excel_BookNew($oExcel2)        ;workbook with 3 sheets
_Excel_BookNew($oExcel2)        ;another workbook in same instance with 3 sheets

$oExcel3 = _Excel_Open(Default, Default, Default, Default, True)    ;open third instance
_Excel_BookNew($oExcel3)        ;workbook with 3 sheets
_Excel_BookNew($oExcel3)        ;another workbook in same instance with 3 sheets

;now here's what I know without a priori knowledge of the objects
;the workbook names are unigue - that is there will never be a Book1 in any but one of the instances or filename (i.e. single open instance of a particular file)

$aWorkBooks = _Excel_BookList()     ;get an array of all workbooks open
;Success: a two-dimensional zero based array with the following information:
;col 0 - Object of the workbook
;col 1 - Name of the workbook/file
;col 2 - Complete path to the workbook/file
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing workbooks.", "@error = '" & @error & "'" & @CRLF &"@extended = '" & @extended & "'")
_DebugArrayDisplay($aWorkBooks, "List of all workbooks open.  Col 0 = Object, Col 1 = workbook name, Col 2 = full filename path")
;at this point we have the Object associated with the book name but no full filename path as not saved yet

;now list the sheets for each Object Workbook
For $i = 0 to UBound($aWorkBooks, $UBOUND_ROWS) - 1 ;0 based
    $aWorkSheets = _Excel_SheetList($aWorkBooks[$i][0]) ;Col 0 = Workbook object
    ;Success: a two-dimensional zero based array with the following information:
   ; 0 - Name of the worksheet
   ; 1 - Object of the worksheet
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing Worksheets.", "@error = '" & @error & "'" & @CRLF & "@extended = '" & @extended & "'")
    _ArrayDisplay($aWorkSheets, "$aWorkSheets for $aWorkBooks[" & $i & "]")
Next

MsgBox($MB_SYSTEMMODAL + $MB_OK, "Info", "Select a range in any Excel instance, any Workbook, and any sheet.  Then click OK.")

;I have spent weeks trying to figure this out.  Looked at Water's UDF (excellent tight code) and got nothing using a default.  All need $oExcel

;********** all this is attempts to get it and they all failed ;**********
;from this: https://www.autoitscript.com/autoit3/docs/functions/ObjGet.htm
;found a possible clue in comment "Error Getting an active Excel Object. <------- **ACTIVE** - so try it

Local $oDefaultActiveExcelObject = ObjGet("", "Excel.Application") ; Get an existing Excel Object

If @error Then
    MsgBox($MB_SYSTEMMODAL, "DEBUG", "Error Getting an active Excel Object. Error code: " & Hex(@error, 8))
Else
    MsgBox($MB_SYSTEMMODAL, "DEBUG", "Success - we got an active Excel Object")
EndIf

;Now I have the object so get the rest of the info.  We could check this instance against the opened ones.
;hard coded for testing.
If $oDefaultActiveExcelObject = $oExcel1 Then
    MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel1 is the active Excel Object")
Else
    If $oDefaultActiveExcelObject = $oExcel2 Then
        MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel2 is the active Excel Object")
    Else
        If $oDefaultActiveExcelObject = $oExcel3 Then
            MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oExcel3 is the active Excel Object")
        Else
            MsgBox($MB_SYSTEMMODAL, "DEBUG", "ERROR - I have no idea what the active Excel Object is.")
        EndIf
    EndIf
EndIf

;go ahead and get information
MsgBox($MB_SYSTEMMODAL, "DEBUG", "$oDefaultActiveExcelObject.ActiveWorkbook.Name = '" & $oDefaultActiveExcelObject.ActiveWorkbook.Name & "'")
;  <<<<<<<<<<<<<------------ this picked the wrong one.  **So it looks like each instance has an active workbook.**
;At this point I'm really stumped.  I probably should submit to the experts.

;I need to find $oExcel, $oWorkbook, $vWorkSheet, for the user selected range because I want to use
;$vRange = _Excel_RangeRead($oWorkbook, $vWorksheet, $oExcel.Selection.Address) ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2
$vRange = _Excel_RangeRead("Book4", "Sheet2", "C2") ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2

MsgBox(0, "Info", "The name of the active sheet is '" & $oExcel1.ActiveSheet.Name & "'")    ;still need application object $oExcel1

MsgBox($MB_SYSTEMMODAL, "Info", "$vRange = '" & $vRange & "'")

;knowing $oExcel instance might be helpful
;$vRange = $oExcel.Selection.Address        ;this returns absolute like $D$3:$E$5 UNLESS it's a single cell then it returns only single absolute like $E$2
;I need to know the $oExcel
;I don't think I can use _Excel_BookAttach in any way as I need to know in advance a string, a filename, or an instance
;Au3Info not showing any distinctions - I'm stuck.

MsgBox($MB_SYSTEMMODAL, "Info", "Pause before exit.")
Exit

 

Share this post


Link to post
Share on other sites
Subz

_Excel_BookAttach?

Share this post


Link to post
Share on other sites
ahha
;I don't think I can use _Excel_BookAttach in any way as I need to know in advance a string, a filename, or an instance

Share this post


Link to post
Share on other sites
water
$oRange1 = $oExcel1.Selection

will return the selected Range (or nothing if nothing has been selected) for the first Excel instance.


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
ahha

Water,

I don't know the $oExcel.  That's the problem I'm having.  I need to find the sheetname and book.  The example illustrates the issue.  The Excel instances in the real world are already opened by someone (not under or by Autoit) and while I can get the information on all instances, books, sheets, etc.  If a user selects a range I can't determine that information. 

ahha

Edited by ahha
Clarification

Share this post


Link to post
Share on other sites
Subz

Not 100% sure if this is what you are after, but what about:

...
MsgBox($MB_SYSTEMMODAL + $MB_OK, "Info", "Select a range in any Excel instance, any Workbook, and any sheet.  Then click OK.")
Local $aWinList = WinList()
For $i = 1 To $aWinList[0][0]
    $iSearch = _ArraySearch($aWorkBooks, StringReplace($aWinList[$i][0], " - Excel", ""), 0, 0, 0, 1, 1, 1)
    If $iSearch > -1 Then
        MsgBox(4096, "", "Current WorkBook: " & $aWorkBooks[$iSearch][1])
        $oDefaultActiveExcelObject = _Excel_BookAttach($aWinList[$i][0], "Title")
        ExitLoop
    EndIf
Next

-- Update --

Had to use the full window title when using _Excel_BookAttach

Edited by Subz

Share this post


Link to post
Share on other sites
ahha

Subz,

It indeed returns at least the Current WorkBook, but alas not the sheet.

I displayed the $aWinList and it lists all the Books and I see no distinguishing feature for the selected Book , so I'm still trying to figure out how the StringReplace aids in the search and yields the correct current WorkBook.

ahha

Share this post


Link to post
Share on other sites
Subz

Winlist returns windows from the top down.  Since _Excel_BookList only returns the name, you need to use StringReplace to remove the " - Excel" at the end of the Window Title to search it against the $aWorkBooks array (name column).  When you attach you need to use the $aWinList[$i][1] Window Title for it to attach correctly.  To get access to the active worksheet you just use $oDefaultActiveExcelObject.ActiveSheet or to get the name $oDefaultActiveExcelObject.ActiveSheet.Name

Share this post


Link to post
Share on other sites
ahha

Subz,

Thanks for the further explanation.  I see that "Winlist returns windows from the top down." (most of the time) but certainly the active one on top is listed first.

I added:

Local $aWinList = WinList()
_DebugArrayDisplay($aWinList, "$aWinList ALL TOP LEVEL WINDOWS")

I don't see "- Excel" at the end of any WinList entries.  I see entries like:

Row|Col 0|Col 1
Row 68|Book3|0x00041282
Row 69||0x00041468
Row 70|Book4|0x0004127E

What am I missing?

BTW this looks to get the sheet info

MsgBox(0, "Info", "The name of the active sheet is '" & $oDefaultActiveExcelObject.ActiveSheet.Name & "'")

Thanks,

ahha

Edited by ahha
correction

Share this post


Link to post
Share on other sites
Nine

Why do you list all windows, if you are only interested in Excel ?  Why not use Excel class like :

Local $aWinList = WinList("[CLASS:XLMAIN]")

Share this post


Link to post
Share on other sites
ahha

Nine,

Because WinList("[CLASS:XLMAIN]")  does not list the books.  Here's an example of what it returns.

Row|Col 0|Col 1
Row 1|Microsoft Excel|0x000817C6
Row 2|Microsoft Excel|0x003D1364
Row 3|Microsoft Excel|0x008E024A

ahha

Share this post


Link to post
Share on other sites
Nine

I am getting the active workbook for each instance :

image.png.7aa0af81de144570a5a935cdb1fce551.png

Share this post


Link to post
Share on other sites
ahha

Hmm.  I'm using Excel 2007.  What version are you using?

Here's what I get:

image.thumb.png.520fa2322ba5f8199b7d24d98f62af75.png

Share this post


Link to post
Share on other sites
Nine

Excel 2003.  But I don't think it really matters cause it is a winlist autoit command.  If you look in the task manager do you see the workbooks ?

Share this post


Link to post
Share on other sites
ahha

In the Apps section:

image.png.63c56b6cbc7e9dc5933c3e65789c593c.png

I'm using Win 10.  What version are you using?

 

Under the Details tab it just shows this:

image.png.da248e812e9af38b01970445f10573fb.png

 

Edited by ahha

Share this post


Link to post
Share on other sites
Nine

that might be it, I am using win 7 :)

but there should be a way to get full win name...

Share this post


Link to post
Share on other sites
ahha

Nine - thanks for trying to narrow the WinList search.  The full version seems to be working at this point.  I'm still testing it.  It seems strange that contortions are needed to get what I thought would be easy - but then again I didn't think that WinList might be an approach that worked.

Edited by ahha

Share this post


Link to post
Share on other sites
ahha

All,

Upon further testing it's not working when I open a spreadsheet that already exists.  Run the program below and select any cell in the _Excel1.xls spreadsheet.  The correct workbook name and workbook sheet are not returned.  I'm stuck again. :( Any help appreciated.  Edit: I'm suspecting _ArraySearch as it seems to be picking the next one in order.  Still debugging.

#AutoIt3Wrapper_run_debug_mode=Y

#include <Array.au3>
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Debug.au3>

;Illustrate issue I'm having.  For a user seletecd range (possibly multiple $oWorkbooks open with multiple sheets), I need to determine the Excel application object of the selected cells and the sheet
;I need $oWorkbook, $WorkSheet, $Range
;look at: https://www.autoitscript.com/forum/topic/197347-get-excel-workbook-and-sheet-name/

;Simulate issue - in real world user may have opened Excel and I have no knowledge of the object
$oExcel1 = _Excel_Open()        ;open first instance
_Excel_BookNew($oExcel1)        ;workbook with 3 sheets
_Excel_BookNew($oExcel1)        ;another workbook in same instance with 3 sheets

$oExcel2 = _Excel_Open(Default, Default, Default, Default, True)    ;open second instance
_Excel_BookNew($oExcel2)        ;workbook with 3 sheets
_Excel_BookNew($oExcel2)        ;another workbook in same instance with 3 sheets
_Excel_BookOpen($oExcel2, "C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras\_Excel1.xls")    ;open an existing workbook that has been saved so we have an entry in Col2 for full pathname

;now here's what I know without a priori knowledge of the objects
;the workbook names are unigue - that is there will never be a Book1 in any but one of the instances or filename (i.e. single open instance of a particular file)

$aWorkBooks = _Excel_BookList()     ;get an array of all workbooks open
;Success: a two-dimensional zero based array with the following information:
;col 0 - Object of the workbook
;col 1 - Name of the workbook/file
;col 2 - Complete path to the workbook/file
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing workbooks.", "@error = '" & @error & "'" & @CRLF &"@extended = '" & @extended & "'")
_DebugArrayDisplay($aWorkBooks, "$aWorkBooks = _Excel_BookList()        ;get an array of all workbooks open.  Col 0 = Object, Col 1 = workbook name, Col 2 = full filename path")
;at this point we have the Object associated with the book name but no full filename path if not saved yet or previously

;now list the sheets for each Object Workbook
For $i = 0 to UBound($aWorkBooks, $UBOUND_ROWS) - 1 ;0 based
    $aWorkSheets = _Excel_SheetList($aWorkBooks[$i][0]) ;Col 0 = Workbook object
    ;Success: a two-dimensional zero based array with the following information:
   ; 0 - Name of the worksheet
   ; 1 - Object of the worksheet
    If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Error listing Worksheets.", "@error = '" & @error & "'" & @CRLF & "@extended = '" & @extended & "'")
    _ArrayDisplay($aWorkSheets, "_Excel_SheetList() $aWorkSheets for $aWorkBooks[" & $i & "]" & " Col 0 = Worksheet Name, Col 1 = Worksheet object")
Next

MsgBox($MB_SYSTEMMODAL + $MB_OK, "Info", "Select a range in any Excel instance, any Workbook, and any sheet.  Then click OK.")

;from Subz https://www.autoitscript.com/forum/topic/197347-get-excel-workbook-and-sheet-name/
Local $aWinList = WinList()
_DebugArrayDisplay($aWinList, "$aWinList ALL TOP LEVEL WINDOWS")
For $i = 1 To $aWinList[0][0]   ;$aArray[0][0] = Number of windows returned
    $iSearch = _ArraySearch($aWorkBooks, StringReplace($aWinList[$i][0], " - Excel", ""), 0, 0, 0, 1, 1, 1)     ;original
    If $iSearch > -1 Then
        MsgBox(4096, "", "Current WorkBook: " & $aWorkBooks[$iSearch][1])
        $oDefaultActiveExcelObject = _Excel_BookAttach($aWorkBooks[$iSearch][1])
        MsgBox(0, "Info", "The name of the active sheet is '" & $oDefaultActiveExcelObject.ActiveSheet.Name & "'")
        ExitLoop
    EndIf
Next


MsgBox($MB_SYSTEMMODAL, "Info", "Pause before exit.")

;force close all Excel w/o saving
_Excel_Close($oExcel1, False, True)
_Excel_Close($oExcel2, False, True)

Exit

 

Edited by ahha
Additional comment

Share this post


Link to post
Share on other sites
Subz

You can either parse the title in $aWinList or you can add the active window title to $aWorkBooks. example:

MsgBox($MB_SYSTEMMODAL + $MB_OK, "Info", "Select a range in any Excel instance, any Workbook, and any sheet.  Then click OK.")
ReDim $aWorkBooks[UBound($aWorkBooks)][5]
For $i = 0 To UBound($aWorkBooks) - 1
    $aWorkBooks[$i][3] = StringInStr($aWorkBooks[$i][0].Application.Caption, $aWorkBooks[$i][1]) > 0 ? $aWorkBooks[$i][0].Application.Caption : ""
    $aWorkBooks[$i][4] = $aWorkBooks[$i][0].ActiveSheet.Name
Next
Local $aWinList = WinList()
For $i = 1 To $aWinList[0][0]
    $iSearch = _ArraySearch($aWorkBooks, StringReplace($aWinList[$i][0], " - Excel", ""), 0, 0, 0, 1, 1, 2)
    If $iSearch > -1 Then
        MsgBox(4096, "", "Active Window Title: " & $aWorkBooks[$iSearch][3] & @CRLF & "Active WorkBook: " & $aWorkBooks[$iSearch][1] & @CRLF & "Active Sheet: " & $aWorkBooks[$iSearch][4])
        $oDefaultActiveExcelObject = _Excel_BookAttach($aWinList[$i][0], "Title")
        ExitLoop
    EndIf
Next

FYI: Windows 10 x64, Office 2016 32-bit.

Share this post


Link to post
Share on other sites
ahha

Thanks - will give it a try.  I was working on a StringInStr approach but did not know about the .Caption.  (I have so much to learn.)

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  
×