Excel determine application obj from workbook

[frank10 6]

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

[water 2,365]

$oWBkw.parent

[TheXman 391]

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

[frank10 6]

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 ?

[TheXman 391]

Both objects, $oExcel & $oExcel1, point to the same application, Excel.

Edited October 29, 2020 by TheXman

[frank10 6]

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

How I tell in which workbook I want the selection?

[TheXman 391]

Just now, frank10 said:

How I tell in which workbook I want the selection?

By using or obtaining the correct Workbook object.

[frank10 6]

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?

[TheXman 391]

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

[water 2,365]

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.

[seadoggie01 161]

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.

[water 2,365]

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)

 

[frank10 6]

Thank you all, now it's clear.