Get excel workbook and sheet name

[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

 

expandcollapse popup

#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

 

[Subz]

_Excel_BookAttach?

[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

[water]

$oRange1 = $oExcel1.Selection

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

[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 January 12, 2019 by ahha
Clarification

[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 January 12, 2019 by Subz

[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

[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

[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 January 12, 2019 by ahha
correction

[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]")

[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

[Nine]

I am getting the active workbook for each instance.

 

Edited March 31, 2023 by Nine

[ahha]

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

Here's what I get:

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

[ahha]

In the Apps section:

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

 

Under the Details tab it just shows this:

 

Edited January 12, 2019 by ahha

[Nine]

that might be it, I am using win 7 

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

[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 January 12, 2019 by ahha

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

expandcollapse popup

#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 January 12, 2019 by ahha
Additional comment

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

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