Jump to content

Recommended Posts

Here you go (now I see where the " - Microsoft Excel" text is coming from, a maximized window.  This is getting really confusing :'()

One instance:

image.thumb.png.51977d8265160b9fe171b7c7b522dff3.png

image.thumb.png.ce665094144a7a765ca20b9c204e690f.png

 

image.thumb.png.78cc40247929feaa8090ba9326dd54ef.png

 

Next instance:

image.thumb.png.4e29d9165a7eb93fd4f785fc7e80ffad.png

 

image.thumb.png.848d32d3953edf813254fef3ab7cea7b.png

Share this post


Link to post
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.

Share this post


Link to post
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?

 

Share this post


Link to post
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.

 

 

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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

 

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
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...

Share this post


Link to post
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.

Share this post


Link to post
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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
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 (2018-12-03 - Version 1.4.11.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2019-03-02 - Version 1.3.5.0) - Download - General Help & Support - Example Scripts - Wiki
Outlook Tools (2019-01-22 - Version 0.1.0.0) - Download - General Help & Support
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
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.

Share this post


Link to post
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

  • Similar Content

    • By Rhidlor
      Quick question, when working with Excel, does each workbook require its own Excel instance, or can multiple workbooks be opened off of the same Excel instance? I tested both ways, on the surface they both seemed to work, just thought I'd ask here to make sure before proceeding any further. Thanks!
      Disclaimer: The following is pseudo code
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $workbook2 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book2.xlsx") Or
      $excel_instance1 = _Excel_Open() $workbook1 = _Excel_BookOpen($excel_instance1, @ScriptDir & "\book1.xlsx") $excel_instance2 = _Excel_Open() $workbook2 = _Excel_BookOpen($excel_instance2, @ScriptDir & "\book2.xlsx")  
    • By TheDcoder
      Hello everyone, I am working on a project which requires reading a few values from Excel, the catch is that I need it to be very fast... unfortunatley I found out that read operations using the supplied Excel UDF are very slow, more than 150 ms for each operation on average
      Here is my testing setup that I made:
      #include <Excel.au3> #include <MsgBoxConstants.au3> Global $iTotalTime = 0 Test() Func Test() Local $oExcel = _Excel_Open() Local $oBook = _Excel_BookAttach("Test.xlsx", "FileName", $oExcel) Local $sSheet = "Sheet1" If @error Then Return MsgBox($MB_ICONERROR, "Excel Failed", "Failed to attach to Excel") Local $iNum For $iRow = 1 To 6 Time() Local $iNum = Number(_Excel_RangeRead($oBook, $sSheet, "A" & $iRow)) If ($iNum = 1) Then ConsoleWrite("Row " & $iRow & " is 1 and value of column B is " & _Excel_RangeRead($oBook, $sSheet, "B" & $iRow)) Else ConsoleWrite("Row " & $iRow & " is not 1") EndIf ConsoleWrite(". Reading took: ") Time() Next ConsoleWrite("The whole operation took " & $iTotalTime & " milliseconds." & @CRLF) EndFunc Func Time() Local Static $hTimer Local Static $bRunning = False If $bRunning Then Local $iTime = Round(TimerDiff($hTimer), 2) $iTotalTime += $iTime ConsoleWrite($iTime & @CRLF) Else $hTimer = TimerInit() EndIf $bRunning = Not $bRunning EndFunc And Test.xlsx in CSV format:
      1,-1 -1,1 1,-1 1,1 -1,-1 1,1 Here is the actual xlsx but it should expire in a week: https://we.tl/t-EVkxGp1kc6
      And finally output from my script:
      Row 1 is 1 and value of column B is -1. Reading took: 276.06 Row 2 is not 1. Reading took: 163.36 Row 3 is 1 and value of column B is -1. Reading took: 302.58 Row 4 is 1 and value of column B is 1. Reading took: 294.65 Row 5 is not 1. Reading took: 152.33 Row 6 is 1 and value of column B is 1. Reading took: 284.92 The whole operation took 1473.9 milliseconds.  
      Taking ~1.5 seconds for reading 6 rows of data is bad for my script, which needs to run as fast as possible . It would be nice if I can bring this down to 100 ms somehow, I am not very experienced working with MS office so I thought about asking you folks for help and advice on how I can optimize my script to squeeze out every bit of performance that I can get from this script
       
      Thanks for the help in advance!
    • By SlackerAl
      I have been using some AutoIt scripts to manipulate Excel for a few weeks now. Today they stopped working. I have rebooted the PC and I'm not aware of any other significant changes. I can start and use Excel conventionally without a problem, but any attempt to create an excel object from AutoIt fails. E.g
      #include <MsgBoxConstants.au3> #include <Excel.au3> Local $oExcel = _Excel_Open() If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_Open Example 1", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended) Produces:
      @error = 1
      @extended = -2147221005
      I can't find that COM error listed anywhere... Anyone have any ideas?
    • By Eggie6
      Hi,
      I'm trying to write an formula to the excel, but it actually does not write anything into the cell, any ideas?
      $formula1="=MID(B"&$utakmice&",SEARCH("-",B"&$utakmice&")-1,1)" _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, $formula1, "C"&$smth)  
    • By XinYoung
      Hello all,
      Preface: Column A is full of courses. Column B is full of usernames. If course (A1) exists, we check to see if username (B1) is enrolled. If user is found, the user is un-enrolled. Then Loop.
      I am working on a script that loops through an Excel file and pastes the content from A1 into a textbox in IE, does some stuff, then pastes the content from B1 into a different textbox. Then it loops around until all used rows in columns A and B have been accounted for.
      For some reason, column A loops properly but column B doesn't. B1 is pasted over and over again. So, as it loops, B1 is constantly being pasted, first accompanying A1's loop, then A2, and so on.
      The OpenExcel() func opens the Excel file the user specifies in an earlier function. It's supposed to gather the entire used range of columns A and B. The SearchCourse() func only uses column A, pasting its content into a Search tool in IE. This seems to be working fine. SearchResult() puts "Course Not Found" into column C if the search fails. If the search is successful, however, we move onto... EnterCourse(). This simply gets us to the place where column B's content comes into play. UnenrollNow(). Here, we paste the variable $_userName into a textbox. I don't know why it's always B1  Func OpenExcel() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else $oExcel = _Excel_Open() Global $oWorkbook = _Excel_BookOpen($oExcel, $ChosenFileName, Default, True, True) $oExcel.Sheets("CopyCourses").Activate ;~ Get all used cells in columns A and B $aSearchItems = _Excel_RangeRead($oWorkbook, 1, $oWorkbook.Sheets("CopyCourses").Usedrange.Columns("A:B")) ;~ Create the $aSearchResult array ReDim $aSearchResult[UBound($aSearchItems)] ;~ Loop through the array starting at 0 until the end of the array which is (Ubound($aSearchItems) - 1) For $i = 0 To UBound($aSearchItems) - 1 ;~ Column 0 ConsoleWrite($aSearchItems[$i][0] & @CRLF) ;~ Column 1 ConsoleWrite($aSearchItems[$i][1] & @CRLF) $aSearchResult[$i] = SearchCourse($aSearchItems[$i][0], $aSearchItems[$i][1]) Next _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") ;~ When the loop is complete, run the Finished function. Finished() EndIf EndFunc ;==>OpenExcel Func SearchCourse($_sSearchResult, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else _IENavigate($oIE, $urlBBCourseSearch) _IELoadWait($oIE) Local $oForm = _IEGetObjByName($oIE, "courseManagerFormSearch") Local $oSearchString = _IEFormElementGetObjByName($oForm, "courseInfoSearchText") _IEFormElementSetValue($oSearchString, $_sSearchResult) _IEFormSubmit($oForm) _IELoadWait($oIE) Local $oBBTable = _IETableGetCollection($oIE, 2) $aBBTableData = _IETableWriteToArray($oBBTable) Return SearchResult() EndIf EndFunc ;==>SearchCourse Func SearchResult() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $sResult $iSearchIndex = _ArraySearch($aBBTableData, "Course ID", 0, 0, 0, 1, 1, 0) If $iSearchIndex = -1 Then $sResult = "Course Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Else EnterCourse() $sResult = "UnEnrolled!" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") EndIf Return $sResult EndIf EndFunc ;==>SearchResult Func EnterCourse() If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $clickFail = "DATE CREATED" _IELinkClickByIndex($oIE, 34) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $clickFail) <> 0 Then _IELinkClickByIndex($oIE, 35) _IELoadWait($oIE) EndIf $oLink = _IEGetObjById($oIE, "controlpanel.users.and.groups_groupExpanderLink") _IEAction($oLink, "click") Sleep(500) _IELinkClickByText($oIE, "Users") _IELoadWait($oIE) $aSearchResult[$i] = UnenrollNow($aSearchItems[$i][0], $aSearchItems[$i][1]) EndIf EndFunc ;==>EnterCourse Func UnenrollNow($_sourceCourseId, $_userName) If Not WinExists($hWnd) Then MsgBox(16, "Window closed", "The Internet Explorer window was closed. Please start over.") _Exit() Else Local $UserError = "No users found" Local $sResult $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchKeyString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') $criteriaUsername = _IEGetObjById($oIE, "userInfoSearchOperatorString") _IEFormElementOptionSelect($criteriaUsername, 0, 1, 'byIndex') ;Paste whats copied from column B into the Username text box. Local $oForm = _IEGetObjByName($oIE, "userManagerSearchForm") Local $oSearchString = _IEFormElementGetObjByName($oForm, "userInfoSearchText") ;PROBLEM HERE... _IEFormElementSetValue($oSearchString, $_userName) ;^^^^^^ WHY IS $_userName ALWAYS B1 ??? Sleep(1000) _IEFormSubmit($oForm) _IELoadWait($oIE) $sourceCode = _IEBodyReadHTML($oIE) If StringInStr($sourceCode, $UserError) <> 0 Then $sResult = "User Not Found" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult Else MsgBox(0, "Unenrollment READY!", "We're ready to unenroll foreal") $sResult = "DUN" _Excel_RangeWrite($oWorkbook, Default, $aSearchResult, "C1") Return $sResult EndIf EndIf EndFunc ;==>UnenrollNow Please let me know if any further information is needed.
      If you see other problems or redundancies in my code, please let me know.
      Thank you!
       
×
×
  • Create New...