Jump to content

Get excel workbook and sheet name


ahha
 Share

Recommended Posts

Link to comment
Share on other sites

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

image.png.ab3c29e8bdcb1137571ae7ba3cfc98eb.png

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

image.png.e8f6ef1546d63f254d9a5fe28bb4c062.png

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

 

 

 

Edited by ahha
Full screen shot deleted.
Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Here is the code I was testing:

#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

 

Link to comment
Share on other sites

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.

 

 

image.png

Edited by ahha
Link to comment
Share on other sites

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

Link to comment
Share on other sites

:blink: 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.

Link to comment
Share on other sites

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

 

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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.

image.png.f12c7ec5f6d09295efea61009571ad20.png

 

After

image.png.9cf84e7e356ce6e58daeea744929d690.png

 

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

 

Edited by ahha
Edited to show sheet tabs in rightmost window.
Link to comment
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
 Share

×
×
  • Create New...