Jump to content

Excel determine application obj from workbook


frank10
 Share

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

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

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

Link to comment
Share on other sites

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

Edited by TheXman
Link to comment
Share on other sites

Just now, frank10 said:

How I tell in which workbook I want the selection?

By using or obtaining the correct Workbook object.

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

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

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
UI-SimpleWrappers UDF - Use UI Automation more Simply-er
KeePass UDF - Automate KeePass, a password manager
InputBoxes - Simple Input boxes for various variable types

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

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

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...