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
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

    • 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  
    • XinYoung
      By XinYoung
      HI! ... this is a big one (at least for me) 
      You guys previously helped me copy the used range in column A and paste them into a Website one at a time in a loop. Cool! Now, for another function, I have 2 columns, A and B, and two input boxes in the Website. I'm having a hard time replicating the loop for the 2 columns. 
      This is how I'm opening the Excel workbook (copied from the previous function that only had 1 column). I need to also get the used range in column B.
      Func OpenExcelForCopy() Global $aBBTableData Global $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in column A:A Global $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:A")) ;~ Duplicate the $aSearchItems Array Global $aSearchResult = $aSearchItems ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 $aSearchResult[$i] = SearchCourseForCopy($aSearchItems[$i]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Finished() EndFunc ;==>OpenExcelForCopy Then we eventually get here. I don't think anything needs to change here but I'm not sure. This is where I paste the data from Column A into an input field (which is a search tool in a website). If the search is good, then we get to the tricky part...
      ;~ OK, we logged in and we searched for a course. Lets COPY it! Func CopyCourseBegin() Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) ;~ If the course was not found, do this. If $iSearchIndex = -1 Then ;~ MsgBox(4096, "Search Error", "Item not found") $sResult = "Source Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ Now go back to the Excel sheet and search for the next one. ;~ If the course was found, begin the COPY process. Else For $i = 0 To UBound($aSearchItems) - 1 $aSearchResult[$i] = CopyCourseNow($aSearchItems[$i]) Next $sResult = "Copied" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndFunc ;==>CopyCourseBegin This is the "tricky part" where I'm confused. I can copy and paste what's in column A just fine, but I can't manage to replicate it for column B. I need to paste whats in Column B into "destinationCourseId"
      ;~ The course search was successful. COPY the course now. Func CopyCourseNow($_sSearchResult) ;~ Navigate to the course copy page. _IENavigate($oIE, $urlBBCourseCopy) ;~ Copy the SOURCE course ID from the Excel sheet ;~ Paste whats copied from column A into the Source Course ID text box Local $oForm = _IEGetObjByName($oIE, "selectCourse") Local $oSearchString = _IEFormElementGetObjByName($oForm, "sourceCourseId") _IEFormElementSetValue($oSearchString, $_sSearchResult) ;~ Paste whats copied from column B into the Destination Course ID text box ?!?!?!?! Local $oForm = _IEGetObjByName($oIE, "selectCourse") Local $oSearchString = _IEFormElementGetObjByName($oForm, "destinationCourseId") _IEFormElementSetValue($oSearchString, $_sSearchResult) ;~ Just exit cause im stuck :( _Exit() EndFunc ;==>CopyCourseNow After I paste the data from column A into "sourceCourseId" and column B into "destinationCourseId", I'll make it do some stuff. Then I need it to loop around until the used ranges in column A & B is finished.
      Does the entire code need to change now that there's two columns?
       
       
    • AnonymousX
      By AnonymousX
      Hello,
      I'm trying to be able to switch back and forth between multiple excel spreadsheets and I can't seem to get the WinActivate function to work, and bring the desired window the be the active window.
      Could I please get some assistance, I've tried a few things and nothing seems to work quite right. Below is a test case where I'm just trying to make the first excel sheet that was opened become the active window, and testing it by grabbing a cell value off that workbook. The message box produces the correct answer if both files are closed before running but the 2nd test file will appear to be the active window. If the code is run again without closing the excel files, nothing works (file does not appear to be active and message box will not give an answer).
       
      #include <Excel.au3> Opt("WinTitleMatchMode", 2) ;1=start, 2=subStr, 3=exact, 4=advanced, -1 to -4=Nocase ;Open Test1 Excel Workbook local $oExcel = _Excel_open() Local $ofile = @ScriptDir & "\test1.xlsx" Local $oWorkbook = _Excel_BookOpen($oExcel,$ofile) ;Open Test2 Excel Workbook local $mExcel = _Excel_open() Local $mfile = @ScriptDir & "\test2.xlsx" Local $mWorkbook = _Excel_BookOpen($mExcel,$mfile) ; This workbook is completely blank WinActivate($oWorkbook); should make Test1 the active window local $read1 = _Excel_RangeRead($oWorkbook,Default,"B2"); Cell B1 in Test1 workbook contains the word Test MsgBox(0,0,$read1);Should returns the word test  
    • nooneclose
      By nooneclose
      I want to check some Excel data against data on a website in Chrome. I use Chrome because the site I use does not function properly in Internet Explorer or Firefox. I know how to do the Excel stuff I just can not figure out how to send to Chrome, let alone check to see if the data matches or not. I am also having trouble finding any help online while searching for Chrome functions for Autoit. I have a Chrome UDF installed but I still can not figure out how to get my code to properly function. (I am not posting code because I am  sure my code isn't right, to begin with)
      As usual, any and all help would be greatly appreciated. 
    • meral40
      By meral40
      #include <Excel.au3> #include <MsgBoxConstants.au3> #include <Array.au3> ; Create application object and open an example workbook Local $var1= "D:\Documents\testbook.xls" Local $oExcel = _Excel_Open() Local $oWorkbook = _Excel_BookOpen($oExcel, $var1) Local $sRead = _Excel_RangeRead($oWorkbook, Default, "Q2") Local $sRead2 = _Excel_RangeRead($oWorkbook, Default, "Q2") $text1= "hello there" $text2= "read me" While 1=1 If $sRead = $text1 Then ;MouseClick Consolewrite($sRead) Elseif $sRead2 = $text2 Then ;MouseClick Consolewrite($sRead2) EndIf sleep(30000);reads field every 30s WEnd Ok I am writing a script in excel that monitors a field that changes every so often then creates an action based on whether it is text1 or text2 I have problem here if I run script it will read the right text but if I go edit the text in excel it still displays the text before the change.
       
      Thanks for your help.
×