Jump to content

Get excel workbook and sheet name


ahha
 Share

Recommended Posts

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

 

Link to comment
Share on other sites

$oRange1 = $oExcel1.Selection

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

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,

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 by ahha
Clarification
Link to comment
Share on other sites

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 by Subz
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by ahha
correction
Link to comment
Share on other sites

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Link to comment
Share on other sites

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 by ahha
Link to comment
Share on other sites

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.

#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 by ahha
Additional comment
Link to comment
Share on other sites

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.

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