Get excel workbook and sheet name

[ahha]

Here you go (now I see where the " - Microsoft Excel" text is coming from, a maximized window.  This is getting really confusing )

One instance:

 

 

Next instance:

 

[Nine]

run those 4 lines please with the maximized books/instances only :

Local $aWinList = WinList("[CLASS:XLMAIN]")
Local $oXL = _Excel_BookAttach ($aWinList[1][0],"Title")
Local $Name = $oXL.ActiveSheet.Name
MsgBox (0,"",$Name)

 

[ahha]

Here you go:

[Nine]

Nice you got your active sheet !

 

[ahha]

But of which instance and which workbook?  I had both instances open and all the books. 

Here are two books I opened (manually) as you can see the selected one is on the left Book1 Sheet3

I run your lines of code and here's what I get.

It's picking Book2 Sheet1, not Book1 Sheet3.  Ugh.

 

 

 

Edited January 13, 2019 by ahha
Full screen shot deleted.

[Nine]

it will only work with maximized books !  If you run all your instances with maximized books it will work all the time

[ahha]

That appears to be correct.   So I need to maximize a window grab the info and restore it to its previous size?  The problem is users cascade their windows and do not necessarily maximize them.  As I understand you're relying on the maximized window so the WinList picks up the Book - is that correct?

 

[Nine]

yes that is my strategy.  at least you got one way to go now...

[ahha]

My variation above works except when an Excel sheet is opened manually.  It's not maximized so that may be part of the problem as WinList does not pick it up.  I have to tell you the WinList versus _Excel_BookList() issue is confusing.  I think that perhaps I may need to use an _Excel_Open to connect to the existing instance but this is confusing because I think that if the sheet is maximized this will cause problems.  I need to code and test more.

 

 

[Subz]

Are you using Office 64-bit?  I've tested my code on Windows 10 x64 - Office 2007 and Windows 10 x64 - Office 2016, they both work, I've minimized, maximized and with the title truncated (Window width), it always works.  The fact that application.caption is blank means something isn't quite right on your system.

[ahha]

Subz,

Interesting.  I'm using Windows 10 Pro x64 , Version  10.0.17134.  However Excel 2007 appears to be 32 bit.

When I get into the office I can check on a variety of computers and Office 2016.

There were many versions above, so could you be so kind as to post the code you want me to test?  That way we're on the same page. 

Thanks.

ahha

[Subz]

Here is the code I was testing:

expandcollapse popup

#AutoIt3Wrapper_run_debug_mode=Y

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

While ProcessExists("EXCEL.exe")
    ProcessClose("EXCEL.exe")
WEnd
;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 & "'")

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
    ConsoleWrite("Workbook Name: " & $aWorkBooks[$i][1] & @CRLF & "Window Title: " & $aWorkBooks[$i][0].Application.Caption & @CRLF)
    $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
_ArrayDisplay($aWorkBooks, "Workbooks", "", 0, Default, "Excel Object|WorkBook Name|WorkBook Path|Active Window|Active Sheet")
Local $aWinList = WinList()
_ArrayDisplay($aWinList)
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

 

[ahha]

Subz,

Thanks.  I tested the code again just now as it's been a hectic day with many versions and Active Window (Col 3) is still empty.

We'll see what other computers show.

 

 

Edited January 13, 2019 by ahha

[Nine]

lol, that thread will never end. Continue your good work Subz !

[Subz]

@Nine BTW I think the WinList("[CLASS:XLMAIN]") you suggested is the best method, I'm just curious as to why the title isn't showing.

[Nine]

Just now, Subz said:

@Nine BTW I think the WinList("[CLASS:XLMAIN]") you suggested is the best method, I'm just curious as to why the title isn't showing.

dude stop throwing codes all around and start reading.  I explained why to OP.  Very basic.  BTW, I really respect your work. You are a great foundation of this forum and I think  we would lose a huge part if you were not here.  But truly, you got to stop throwing code all around and instead teach the foundation of the wonderful world of autoit scripting...

[Subz]

 Not sure what "throwing codes all around and start reading." is all about, as I said in my previous post WinList returns the full title for me, whether minimized, maximized or resized manually, since the OP was also on Windows 10 and I had tested it on three separate Win 10 machines with different office version, I was curious as to why his machine wasn't returning the same.

[water]

This script works perfect on Windows 7, Excel 2016, single instance and 3 open workbooks
Will test with multiple Excel instances soon.
 

#include <Excel.au3>
Global $oExcelApp, $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
; MsgBox(0, "Info", "Activate a workbook and select a range. 10 Seconds to do", 10)
For $i = 0 to UBound($aWorkbooks) - 1
   $oExcelApp = $aWorkbooks[$i][0].Parent                            ; Application object for the selected workbook <== MODIFIED
   $oExcelWindow = $oExcelApp.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", "Address: " & $oExcelActiveCell.Address & @CRLF & _
                "Sheet Name: " & $oExcelApp.ActiveSheet.Name & @CRLF & _
                "Book Name: " & $oExcelApp.ActiveWorkbook.Name)      ; Displays the selected cell or the top left cell of a range
           ExitLoop                                                  ; If active cell found stop searching
       EndIf
   EndIf
Next

 

[water]

This example creates two instances of Excel with 3 workbooks and works fine as well:

#include <Excel.au3>

Global $oExcelApp, $oExcelWindow
; Create instance 1 with 2 workbooks
Global $oExcel1 = _Excel_Open()                                      ; Connect to an already started Excel instance
Global $oWB1_1 = _Excel_BookNew($oExcel1)                            ; Create workbook 1
Global $oWB1_2 = _Excel_BookNew($oExcel1)                            ; Create workbook 2
; Create instance 2 with 1 workbook
Global $oExcel2 = _Excel_Open(Default, Default, Default, Default, True) ; Create new instance
Global $oWB2_1 = _Excel_BookNew($oExcel2)                            ; Create workbook 1
; Select cell in instance 2 workbook 1
$oWB2_1.ActiveSheet.Range("b3").select

Global $aWorkbooks = _Excel_BookList()                               ; Get a list of workbooks for all Excel instances
For $i = 0 to UBound($aWorkbooks) - 1
   $oExcelApp = $aWorkbooks[$i][0].Parent                            ; Application object for the selected workbook <== MODIFIED
   $oExcelWindow = $oExcelApp.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", "Address: " & $oExcelActiveCell.Address & @CRLF & _
                "Sheet Name: " & $oExcelApp.ActiveSheet.Name & @CRLF & _
                "Book Name: " & $oExcelApp.ActiveWorkbook.Name)      ; Displays the selected cell or the top left cell of a range
           ExitLoop                                                  ; If active cell found stop searching
       EndIf
   EndIf
Next

 

[ahha]

water - very close.  Open a couple of instances manually with several workbooks in each.  Then run this code to generate some more and to open an existing file.

;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

Now select any range in Tabelle3 to run your prior code.  It jumps to another instance and another book and reports that.  Here's a before and after shot.

Before showing selected cell in _Excel1.xls Tabelle3 C1 (If I select the second window it shows Book3 Sheet2 E3 active and rightmost Book2 C10.)

So with _Excel1.xls Tabelle3 C1 active I run your prior code and the second screen shot below is what I get.

 

After

 

So you can see it's selected to report the rightmost window rightmost Book2 C10 rather than the _Excel1.xls Tabelle3 C1.  Ugh.

 

Edited January 14, 2019 by ahha
Edited to show sheet tabs in rightmost window.