frank10 Posted October 29, 2020 Share Posted October 29, 2020 (edited) I can have 2 Excel application opened by Autoit both with a workbook with some sheets. Also, it's possible I must attach to a workbook just opened by user, out of Autoit. Then I call a func in which I pass a worbook obj. But sometimes I need to use a code that needs the application obj instead of workbook, so I'm asking if it's possible to determine which application object refers to a specific Workbook. I.e. local $oWBbase, $oWBkw local $oExcelBase = _Excel_Open(false) local $oExcelKW = _Excel_Open(false) $oWBbase = _Excel_BookOpen($oExcelBase, @ScriptDir & "/" & $file) $oWBkw = _Excel_BookAttach( @ScriptDir & "/" & $file1) ;I can call my func with one of the 2 workbooks: _myFunc($oWBbase) ; or _myFunc($oWBkw) Func _myFunc($oWB) _Excel_SheetAdd($oWB, -1, False, 1, "test") $oWB.Worksheets( "test").Range("B1").Select ;$oWB to which application obj belongs? How can I determine which workbook the func has received as an argument? $oExcelBase.ActiveWindow.FreezePanes = False ;or ?? $oExcelKW.ActiveWindow.FreezePanes = False EndFunc Edited October 29, 2020 by frank10 Link to comment Share on other sites More sharing options...
water Posted October 29, 2020 Share Posted October 29, 2020 $oWBkw.parent My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
TheXman Posted October 29, 2020 Share Posted October 29, 2020 (edited) Or to be more explicit... $oWBkw.Application In this case, .Parent and .Application should provide the same result -- The application object of the workbook. https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.application Edited October 29, 2020 by TheXman CryptoNG UDF: Cryptography API: Next Gen jq UDF: Powerful and Flexible JSON Processor | jqPlayground: An Interactive JSON Processor Xml2Json UDF: Transform XML to JSON | HttpApi UDF: HTTP Server API | Roku Remote: Example Script About Me How To Ask Good Questions On Technical And Scientific Forums (Detailed) | How to Ask Good Technical Questions (Brief) "Any fool can know. The point is to understand." -Albert Einstein "If you think you're a big fish, it's probably because you only swim in small ponds." ~TheXman Link to comment Share on other sites More sharing options...
frank10 Posted October 29, 2020 Author Share Posted October 29, 2020 Perfect. But now I'm a bit confused to what's happening... Because If I do this: local $oExcel = _Excel_Open() local $oExcel1 = _Excel_Open() local $oWB_KW = _Excel_BookOpen($oExcel, @ScriptDir & "/KW.xlsx") local $oWB_KW1 = _Excel_BookOpen($oExcel1, @ScriptDir & "/KW1.xlsx") ; both workbooks has sheet named "test" ; if I do: $oExcel.Worksheets( "test").Range("B1:D5").Select ;or $oExcel1.Worksheets( "test").Range("B1:D5").Select I get always the workbook $oWB_KW1 with the range selected... that is the last in order of declaration (If I invert the order of the declaration I get the $oWB_KW selected) Shouldn't select the one belonging to the corresponding $oExcel or $oExcel1 ? Link to comment Share on other sites More sharing options...
TheXman Posted October 29, 2020 Share Posted October 29, 2020 (edited) Both objects, $oExcel & $oExcel1, point to the same application, Excel. Edited October 29, 2020 by TheXman CryptoNG UDF: Cryptography API: Next Gen jq UDF: Powerful and Flexible JSON Processor | jqPlayground: An Interactive JSON Processor Xml2Json UDF: Transform XML to JSON | HttpApi UDF: HTTP Server API | Roku Remote: Example Script About Me How To Ask Good Questions On Technical And Scientific Forums (Detailed) | How to Ask Good Technical Questions (Brief) "Any fool can know. The point is to understand." -Albert Einstein "If you think you're a big fish, it's probably because you only swim in small ponds." ~TheXman Link to comment Share on other sites More sharing options...
frank10 Posted October 29, 2020 Author Share Posted October 29, 2020 So, it becomes irrelevant to use one or the other obj excel? How I tell in which workbook I want the selection? Link to comment Share on other sites More sharing options...
TheXman Posted October 29, 2020 Share Posted October 29, 2020 Just now, frank10 said: How I tell in which workbook I want the selection? By using or obtaining the correct Workbook object. CryptoNG UDF: Cryptography API: Next Gen jq UDF: Powerful and Flexible JSON Processor | jqPlayground: An Interactive JSON Processor Xml2Json UDF: Transform XML to JSON | HttpApi UDF: HTTP Server API | Roku Remote: Example Script About Me How To Ask Good Questions On Technical And Scientific Forums (Detailed) | How to Ask Good Technical Questions (Brief) "Any fool can know. The point is to understand." -Albert Einstein "If you think you're a big fish, it's probably because you only swim in small ponds." ~TheXman Link to comment Share on other sites More sharing options...
frank10 Posted October 29, 2020 Author Share Posted October 29, 2020 So, I got it activating before the correct workbook, like: $oWB_KW.Activate But now, what's the need to make 2 Excel objects if I can activate a sheet, generally referring to one of the two Excel objs? Are there some uses for declaring and associating 2 Excel obj with different workbooks? I tried the above example and it works also with one Excel obj... When to use more? Link to comment Share on other sites More sharing options...
TheXman Posted October 29, 2020 Share Posted October 29, 2020 (edited) 22 minutes ago, frank10 said: But now, what's the need to make 2 Excel objects if I can activate a sheet, generally referring to one of the two Excel objs? In all of the years that I've been using Excel COM objects, I can't remember a time that I've needed more than 1 application object to do whatever I needed to get done. 22 minutes ago, frank10 said: Are there some uses for declaring and associating 2 Excel obj with different workbooks? I guess you'll know the answer when/if you ever have an actual need to use more than 1 application object. Edited October 29, 2020 by TheXman CryptoNG UDF: Cryptography API: Next Gen jq UDF: Powerful and Flexible JSON Processor | jqPlayground: An Interactive JSON Processor Xml2Json UDF: Transform XML to JSON | HttpApi UDF: HTTP Server API | Roku Remote: Example Script About Me How To Ask Good Questions On Technical And Scientific Forums (Detailed) | How to Ask Good Technical Questions (Brief) "Any fool can know. The point is to understand." -Albert Einstein "If you think you're a big fish, it's probably because you only swim in small ponds." ~TheXman Link to comment Share on other sites More sharing options...
water Posted October 30, 2020 Share Posted October 30, 2020 Imagine this situation: A user has started Excel and opened a workbook He then starts an AutoIt script that works with Excel as well. _Excel_Open attaches to the already running instance of Excel If this script closes Excel the workbook opened by the user is closed as well as there is only a single instance of Excel This could be avoided by starting a second instance of Excel. My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
seadoggie01 Posted October 30, 2020 Share Posted October 30, 2020 I use a second application only if I want to run a script that may take a while and I need to let the user continue working in Excel. Since Excel (mostly...) runs on a single thread, anytime my script uses Excel, all the other workbooks freeze until I'm done. That said, having two Excel applications is usually the bane of my lowly existence. From Excel VBA, there isn't a way that I've found to interact with a second instance. This means that if the user has the workbook I want to use open in a second instance, I have to make the user handle the situation. Usually Excel prevents two instances from existing, but it decides to have bad days occasionally. All my code provided is Public Domain... but it may not work. Use it, change it, break it, whatever you want. Spoiler My Humble Contributions:Personal Function Documentation - A personal HelpFile for your functionsAcro.au3 UDF - Automating Acrobat ProToDo Finder - Find #ToDo: lines in your scriptsUI-SimpleWrappers UDF - Use UI Automation more Simply-erKeePass UDF - Automate KeePass, a password managerInputBoxes - Simple Input boxes for various variable types Link to comment Share on other sites More sharing options...
water Posted October 30, 2020 Share Posted October 30, 2020 On 10/29/2020 at 6:37 PM, frank10 said: But now I'm a bit confused to what's happening... Because If I do this: local $oExcel = _Excel_Open() local $oExcel1 = _Excel_Open() local $oWB_KW = _Excel_BookOpen($oExcel, @ScriptDir & "/KW.xlsx") local $oWB_KW1 = _Excel_BookOpen($oExcel1, @ScriptDir & "/KW1.xlsx") ; both workbooks has sheet named "test" ; if I do: $oExcel.Worksheets( "test").Range("B1:D5").Select ;or $oExcel1.Worksheets( "test").Range("B1:D5").Select I get always the workbook $oWB_KW1 with the range selected... that is the last in order of declaration (If I invert the order of the declaration I get the $oWB_KW selected) Shouldn't select the one belonging to the corresponding $oExcel or $oExcel1 ? @frank10 You have to tell _Excel_Open to create a new instance of Excel. If you just use defaults then _Excel_Open will attach to an already running instance or create it. The help file is your friend ; Force the creation of a new Excel application $oExcel2 = _Excel_Open(Default, Default, Default, Default, True) My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
frank10 Posted October 31, 2020 Author Share Posted October 31, 2020 Thank you all, now it's clear. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now