Jump to content
frank10

Excel determine application obj from workbook

Recommended Posts

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 by frank10

Share this post


Link to post
Share on other sites

$oWBkw.parent


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 by TheXman

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

So, it becomes irrelevant to use one or the other obj excel?

How I tell in which workbook I want the selection?

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites
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.  :muttley:

Edited by TheXman

Share this post


Link to post
Share on other sites

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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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 functions
Acro.au3 UDF - Automating Acrobat Pro
ToDo Finder - Find #ToDo: lines in your scripts

Share this post


Link to post
Share on other sites
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 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-06-30 - Version 1.6.2.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (NEW 2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
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
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

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

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...