What exactly is _Excel_BookAttach doing?

[Bagel]

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.

 

[pseakins]

Did you try it for yourself to see what happens, before asking here?

Show us some example code which you feel does not work the way you expect it to.

[Subz]

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

 

[Bagel]

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?

[water]

_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

[Subz]

@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"

 

[water]

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.

[Bagel]

Okay, thanks a lot for the clarification. I will investigate more when I have the time. And, water, I'll review your post again.

[Bagel]

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 April 9, 2021 by Bagel

[water]

I have updated the wiki to clarify how _Excel_BookAttach works.