Jump to content

Recommended Posts

The .Application.Caption will display the objects top most window, for example from your previous post $oExcel1 would be Book2 (since it was opened last for $oExcel1), $oExcel2 would be _Excel1.xls - [Read Only] because that was opened last.  If you selected Book3 after the MsgBox, than the top most window for $oExcel2 (all 3 instances) would change to Book3 (although I only write the application.caption for the workbook that has the same name i.e. stringinstr)

Hope that makes sense.

Share this post


Link to post
Share on other sites

Thanks still testing. 

This is what I initially got when I select a cell in _Excel1.xls.  I was surprised to see no Active Window Title.

---------------------------

---------------------------
Active Window Title:

Active WorkBook: _Excel1.xls

Active Sheet: Tabelle2
---------------------------
OK   
---------------------------

 

When I select a cell in another workbook (not _Excel1.xls) it reverts back to the _Excel1.xls sheet not the one I selected.  Ugh.

*Edit* is this because of the 2 in  $iSearch = _ArraySearch($aWorkBooks, StringReplace($aWinList[$i][0], " - Excel", ""), 0, 0, 0, 1, 1, 2)

 

Here's the code:

#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.")

;new approach by Subz
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


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
Just tested with a sheet not _Excel1.xls

Share this post


Link to post
Share on other sites

Sorry forgot to update the second loop it should have been:

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, $aWinList[$i][0], 0, 0, 0, 1, 1, 3)
    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

 

Share this post


Link to post
Share on other sites

Subz,

I now get neither working.  Not not even _Excel1.xls.  Could it be the 3 in $iSearch = _ArraySearch($aWorkBooks, $aWinList[$i][0], 0, 0, 0, 1, 1, 3) ?

Here's the full code now.

#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.")

;new approach by Subz - see his latest entry
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, $aWinList[$i][0], 0, 0, 0, 1, 1, 3)
    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

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

 

Share this post


Link to post
Share on other sites

It works fine for me, can you check that $aWorkbooks Column 3, can match a Window Title in $aWinList Column 0?

 

Workbooks.jpg

Share this post


Link to post
Share on other sites

Here's what I'm getting it looks like what you call Active Window (Application.Caption) is blank.  I'm not sure why I'm using your code from above example.

It appears to be this line: 

$aWorkBooks[$i][3] = StringInStr($aWorkBooks[$i][0].Application.Caption, $aWorkBooks[$i][1]) > 0 ? $aWorkBooks[$i][0].Application.Caption : ""

image.png.3ba71db554a4c3b8bd909bbef041a642.png

Share this post


Link to post
Share on other sites

I've been working in parallel on another variation, and so far this seems to work.

#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 - partial search
;~     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

;okay the above does a wrong way partial match when _Excel_BookList() returns
;Row|Col 0|Col 1|Col 2
;Row 4||_Excel1.xls|C:\Program Files (x86)\AutoIt3\Examples\Helpfile\Extras
;and WinList returns
;Row|Col 0|Col 1
;Row 78|_Excel1.xls  [Read-Only]  [Compatibility Mode]|0x001318D4
;
;notice seaching "_Excel1.xls  [Read-Only]  [Compatibility Mode]" for a partial match with "_Excel1.xls"
;it should be looking for "_Excel1.xls" in "_Excel1.xls  [Read-Only]  [Compatibility Mode]"
;so writing our own
For $i = 1 To $aWinList[0][0]   ;$aArray[0][0] = Number of windows returned
    ;$aWinList[$i][0]   ;pull an entry from one row at a time in Col 0 which has the name
    For $j = 0 to UBound($aWorkBooks) - 1
        If StringInStr($aWinList[$i][0], $aWorkBooks[$j][1]) Then   ;found, otherwise go to next row
            ;because of the difference in what _Excel_Booklist() returns we need to keep what WinList returns for attaching
            ;then once we have the object we can just use that
            MsgBox(4096, "", "Current WorkBook: " & $aWinList[$i][0])
            ExitLoop 2  ;exit inner (j) and outer (i) loops
        EndIf
    Next
Next

;we have the current workbook so get other info from $aWorkBooks - recall
;$aWorkBooks[$j][0] ;has object
;$aWorkBooks[$j][1] ;has workbook name
;$aWorkBooks[$j][2] ;has full filename path

MsgBox(4096, "",    "Current WorkBook from $aWinList[$i][0]: " & $aWinList[$i][0] &@CRLF & _
                    "Current WorkBook from $aWorkBooks[$j][1]: " & $aWorkBooks[$j][1] &@CRLF)
;$aWorkBooks[$j][0] ;has object - use this rather than BookAttach since the names are different
MsgBox(0, "Info", "The name of the active sheet is '" & $aWorkBooks[$j][0].ActiveSheet.Name & "'")

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

In either version _Excel_BookList() seems to be really slow.  Is it slow for you?

Edited by ahha

Share this post


Link to post
Share on other sites

Okay further testing done and there appears to be a fundamental problem using WinList - try the following program and you'll see that the Book in Excel is not listed in WinList - UGH :(  Or am I doing something wrong?

;test of WinList()
#AutoIt3Wrapper_run_debug_mode=Y

#include <Debug.au3>

MsgBox(0, "Info", "Open Excel manually then click OK.")

$aWinList = WinList()
MsgBox(0, "Info", "Try and find the Book in the array that follows.")
_DebugArrayDisplay($aWinList, "$aWinList ALL TOP LEVEL WINDOWS")

 

Share this post


Link to post
Share on other sites

Haven't read the whole thread but I would try the following.
This code is untested and might need a COM error handler to grab all COM errors so you can check @Error

#include <Excel.au3>
Global $oExcelTemp, $oExcelWindow
Global $oExcel = _Excel_Open()                      ; Connect to an already started Excel instance
Global $aWorkbooks = _Excel_BookList()              ; Get a list of workbooks for all Excel instances
For $i = 0 to UBound($aWorkbooks) - 1
   $oExcelTemp = $aWorkbooks[$i].Parent             ; Application object for the selected workbook
   $oExcelWindow = $oExcelTemp.ActiveWindow         ; Get active Window for the selected Excel instance
   If IsObj($oExcelWindow) Then                     ; Is there an active window for this Excel instance?
       $oExcelActiveCell = $oExcelWindow.ActiveCell ; Get the active cell of the window
       If @error = 0 Then ExitLoop                  ; If active cell found stop searching
   EndIf
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (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

I get this error:

 ==> Array variable has incorrect number of subscripts or subscript dimension range exceeded.:
$oExcelTemp = $aWorkbooks[$i].Parent
$oExcelTemp = ^ ERROR

 

I added an ArrayDisplay and it shows

image.png.22e7544c9eafbd28d8fdadcfb512b506.png

 

Here's the slight mod

#include <Excel.au3>
#include <Debug.au3>
MsgBox(0, "Info", "Open Excel manually then click OK.")
Global $oExcelTemp, $oExcelWindow
Global $oExcel = _Excel_Open()                      ; Connect to an already started Excel instance
Global $aWorkbooks = _Excel_BookList()              ; Get a list of workbooks for all Excel instances
_DebugArrayDisplay($aWorkbooks, "$aWorkbooks = _Excel_BookList()")  ;let's look at it
For $i = 0 to UBound($aWorkbooks) - 1
   $oExcelTemp = $aWorkbooks[$i].Parent             ; Application object for the selected workbook
   $oExcelWindow = $oExcelTemp.ActiveWindow         ; Get active Window for the selected Excel instance
;~    MsgBox(0, "Info",     "$aWorkbooks[$i].Parent = " & $aWorkbooks[$i].Parent & @CRLF & _
;~                      "$oExcelTemp.ActiveWindow = " & $oExcelTemp.ActiveWindow)
   If IsObj($oExcelWindow) Then                     ; Is there an active window for this Excel instance?
       $oExcelActiveCell = $oExcelWindow.ActiveCell ; Get the active cell of the window
       If @error = 0 Then ExitLoop                  ; If active cell found stop searching
   EndIf
Next

 

Share this post


Link to post
Share on other sites

My bad. It's a 2D array so the script should look like:

#include <Excel.au3>
Global $oExcelTemp, $oExcelWindow
Global $oExcel = _Excel_Open()                      ; Connect to an already started Excel instance
Global $aWorkbooks = _Excel_BookList()              ; Get a list of workbooks for all Excel instances
For $i = 0 to UBound($aWorkbooks) - 1
   $oExcelTemp = $aWorkbooks[$i][0].Parent          ; Application object for the selected workbook <== MODIFIED
   $oExcelWindow = $oExcelTemp.ActiveWindow         ; Get active Window for the selected Excel instance
   If IsObj($oExcelWindow) Then                     ; Is there an active window for this Excel instance?
       $oExcelActiveCell = $oExcelWindow.ActiveCell ; Get the active cell of the window
       If @error = 0 Then ExitLoop                  ; If active cell found stop searching
   EndIf
Next

 


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (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

BTW: Your debugging statement  won't work as the Parent and ActiveWindow property return an object which can't be displayed using MsgBox.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (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

Thanks for that comment.  So how do I display the text of the parent, active window, and active cell?  I'd like to verify that I've got the correct objects.

Share this post


Link to post
Share on other sites
#include <Excel.au3>
Global $oExcelTemp, $oExcelWindow
Global $oExcel = _Excel_Open()                       ; Connect to an already started Excel instance
Global $aWorkbooks = _Excel_BookList()               ; Get a list of workbooks for all Excel instances
For $i = 0 to UBound($aWorkbooks) - 1
   $oExcelTemp = $aWorkbooks[$i][0].Parent           ; Application object for the selected workbook <== MODIFIED
   $oExcelWindow = $oExcelTemp.ActiveWindow          ; Get active Window for the selected Excel instance
   If IsObj($oExcelWindow) Then                      ; Is there an active window for this Excel instance?
       $oExcelActiveCell = $oExcelWindow.ActiveCell  ; Get the active cell of the window
       If @error = 0 Then 
           MsgBox(0, "Result", $oActiveCell.Address) ; Displays the selected cell or the top left cell of a range
           ExitLoop                                  ; If active cell found stop searching
       EndIf
   EndIf
Next
Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (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

I'm trying.  I got this:

image.thumb.png.22d8c06be86b4bbb0ae37923cf484fa7.png

So I put $oActiveCell in the Global at top.

Now I get:

0013: 0-0:            MsgBox(0, "Result", $oActiveCell.Address) ; Displays the selected cell or the top left cell of a range
"C:\Program Files (x86)\AutoIt3\AH Code\water's approach v1a_DebugIt.au3" (50) : ==> Variable must be of type "Object".:
MsgBox(0, "Result", $oActiveCell.Address)
MsgBox(0, "Result", $oActiveCell^ ERROR

 

So far not my day ;) but I appreciate all the help.

Share this post


Link to post
Share on other sites

I will need to check this tomorrow when I get my hands on a Windows system. Linux with LibreOffice doesn't help much at the moment ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (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

water - thanks I really appreciate it.  No rush as I've been working on it for weeks.  What started out (see first post) as what I thought would be simple is, so far, not turning out to be so simple.

Share this post


Link to post
Share on other sites

A quick question @ahha. Can you tell me what you see in the Excel windows title bar of all your instances.  Since I am on Win7 and using Office 2003, it is a bit hard to figure it out...

Share this post


Link to post
Share on other sites

Sure.  Here's the 2 instances and what the titles show from running the program.

image.png.62d00a2fdec610d0d13c4857a7440f0b.png

 

image.png.a9adb82c7c56a839f0b3b6ea7917f4c0.png

 

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

    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($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 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
    • 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?
×
×
  • Create New...