Jump to content

What exactly is _Excel_BookAttach doing?


Recommended Posts

I have a need to allow multiple separate scripts to have access to an Excel spreadsheet that is already open. I'm not quite sure how to do this. From what I understand of the Excel UDF _Excel_Open() will need to be called in each of the scripts. Now comes _Excel_BookAttach. Which I think will allow the workbook objects I create in each of the scripts to grab the single open workbook and interact with it, although I'm not sure about this.

So anyway, my question is, does the BookAttach function work this way? From what I understand, if an instance of Excel is already running when I call _Excel_Open() then the handle will reference the already created instance. But every example of using the BookAttach function shows that a workbook must first be opened with _Excel_BookOpen (I guess in my case it would have to be the workbook that's ALREADY opened). Will this need to be repeated for each script that wants to use the spreadsheet? I've searched around and can't find any clarity on this. Basically, I have no idea what BookAttach is actually doing how it should be useful.

 

Link to post
Share on other sites

Use something like the following (untested):

;~ Create or connect to existing Excel instance
Local $oExcel = _Excel_Open()
;~ Workbook variables
Local $oWorkbook, $sWorkbook = "Excel.xls"
;~ Try attach to workbook
$oWorkbook = _Excel_BookAttach($sWorkbook, "filename")
If @error = 1 Then
    ;~ If open workbook wasn't found, open the workbook
    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then Exit MsgBox(48, "Workbook Error", $sWorkbook & " - Workbook not found")
EndIf

 

Link to post
Share on other sites

What I've tried is this:

#include <Array.au3>
#include <Excel.au3>

;~ Create or connect to existing Excel instance
Local $oExcel = _Excel_Open()
;~ Workbook variables
Local $oWorkbook, $sWorkbook = "Book8.xlsx"
;Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\Book8.xlsx", Default, Default)
;~ Try attach to workbook
$oWorkbook = _Excel_BookAttach($sWorkbook, "Book8")
Local $aArray[3] = ['A', 'B', 'C']
_ArrayTranspose($aArray)
_Excel_RangeWrite($oWorkbook, $oWorkbook.ActiveSheet, $aArray, 'A1', Default, Default)
If @error = 1 Then
    ;~ If open workbook wasn't found, open the workbook
    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
    If @error Then Exit MsgBox(48, "Workbook Error", $sWorkbook & " - Workbook not found")
EndIf

This fails, but it works if I uncomment the line opening the book. But why is it necessary to keep reopening the book? If Autoit can search for and grab the window by the title shouldn't there be some way to grab the already open window as an object and then work with it using the Excel functions? What I have in my use case is multiple scripts that need to be able to interact with a single open workbook. I guess I could try code that automatically opens the workbook, makes the necessary changes, then saves and closes the workbook before another script needs to use it. But then I have to worry about conflicts where multiple scripts are trying to open/save/close the same workbook simultaneously. If simply opening the workbook activates a sheet and leaves it ready to be written to, then why would we need a separate "_Excel_BookAttach" function in the first place? What is it's purpose if not, as the name suggests to me, to allow us, in code, to grab an already open workbook and manipulate it without re-opening it or another book?

Link to post
Share on other sites

_Excel_BookAttach connects to an already existing workbook (by name etc.). There is no need to run _Excel_Open when trying to attach.
I already posted a working example in your last thread

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.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

 

Link to post
Share on other sites

@water - While _Excel_Open() wasn't required, I added it so you could reference the $oExcel application object for _Excel_BookOpen (only if _Excel_BookAttach failed).

@Bagel - Your _Excel_BookAttach $sMode parameter is incorrect it should be "FileName" not "Book8"

 

Link to post
Share on other sites

I see :)
If _Excel_BookAttach is successful (returning $oWorkbook) I use $oWorkbook.Application to get the correct Excel object for further processing.
Excel allows to have multiple instances of Excel running at the same time. _Excel_BookAttach can return the workbook object from any of this instances.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.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

 

Link to post
Share on other sites
Posted (edited)

I see, so when we declare the two variables $oWorkbook and $sWorkbook one is the object name that we will use to refer to the workbook that we're attaching to and the other is simply a string that we're telling _Excel_BookAttach to search for when looking for the already open Excel book.

And, confirmed, I can in fact use _Excel_BookAttach to manipulate the spreadsheet without calling _Excel_Open().

I think what was confusing me is that when I read about the UDF the wiki states at one point that all of the following functions must be preceded by _Excel_Open() and then every example I saw in the documentation after that point used _Excel_Open() AND _Excel_BookOpen. Which then lead me down a dark rabbit hole and left me wondering... why would we need to open Excel if it's already open and we're just trying interface with it, what's the point of using _Excel_BookAttach if we have to run Excel (again) and open the book directly beforehand...

I'm now able to do everything that I needed to in order to move forward with my project. Thanks again!

Edited by Bagel
Link to post
Share on other sites

I have updated the wiki to clarify how _Excel_BookAttach works.

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2021-06-05 - Version 1.5.4.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2021-06-14 - Version 1.6.5.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
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (NEW 2021-07-28 - Version 1.1.0.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

 

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