Search the Community

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