Location

WWW

Interests

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