Jump to content
Sign in to follow this  
JohnD

_ExcelBookAttach

Recommended Posts

JohnD

Hello.

Per the recommendation of another member of the forum I am attempting to use the Excel UDF's to navigate excel. I need to simple get the handle of the instance that is already running, in other words, I won't be opening the file using the full pathname and _excelbookopen. I was playing around with the example from the helpfile, and attempting to adapt it for my purposes. Here is what I have, but the $oExcel variable returns 0.

#include <Excel.au3>

#include <File.au3>

Opt("WinTitleMatchMode", 2)

$oExcel = _ExcelBookAttach("Microsoft Excel", "Title") ;with $s_mode = "Title" ==> Title of the Excel window

_ExcelWriteCell($oExcel, "If you can read this, then Success!", 2, 2) ;Write to the Cell

MsgBox(0, $oExcel, "Press OK to Save File and Exit")

_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

Can anyone clue me in to what I am doing wrong? Thanks.

Share this post


Link to post
Share on other sites
Mikesch

I think you're right, I see the same behaviour here.

My guess is _ExcelBookAttach won't accept the WinTitleMatchMode-Option and ignore it. Maybe it insists on getting the complete Title. (Providing the complete SearchString worked fine.)

$s_mode="FileName" seems to work, (maybe a workaround if your file has already been saved before.

As another workaround, you might want to consider using WinActivate.

Regards,

Mike

Share this post


Link to post
Share on other sites
PsaltyDS

The code's not very deep. Open your copy of Excel.au3 and under _ExcelBookAttach() you find:

Case "title"
                If ($o_workbook.Application.Caption) = $s_string Then
                    Return $o_workbook
                EndIf

You could change it to:

If StringLeft($o_workbook.Application.Caption, StringLen($s_string)) = $s_string Then
                    Return $o_workbook
                EndIf

Or just:

If StringInStr($o_workbook.Application.Caption, $s_string) Then
                    Return $o_workbook
                EndIf

Depends on your requirements.

:D


Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
JohnD

I think you're right, I see the same behaviour here.

My guess is _ExcelBookAttach won't accept the WinTitleMatchMode-Option and ignore it. Maybe it insists on getting the complete Title. (Providing the complete SearchString worked fine.)

$s_mode="FileName" seems to work, (maybe a workaround if your file has already been saved before.

As another workaround, you might want to consider using WinActivate.

Regards,

Mike

WinActivate worked to retrieve the window's handle, but it still (for some reason) does not allow the other excel functions to execute.

So, nothing is written to the cells via excelwritecell, nor is the application closed by excelbookclose. This is frustrating.

#include <Excel.au3>

#include <File.au3>

Opt("WinTitleMatchMode", 2)

$oExcel = WinActivate("week", "")

;$oExcel = _ExcelBookAttach("Microsoft Excel", "Title") ;with $s_mode = "Title" ==> Title of the Excel window

_ExcelWriteCell($oExcel, "If you can read this, then Success!", 2, 2) ;Write to the Cell

MsgBox(0, $oExcel, "Press OK to Save File and Exit")

_ExcelBookClose($oExcel, 1, 0) ;This method will save then Close the file, without any of the normal prompts, regardless of changes

Share this post


Link to post
Share on other sites
Mikesch

Well, PsaltyDS's approach will do the trick just perfectly. His last Code snippet would match Opt("WinTitleMatchMode",2) (while using _ExcelBookAttach, not WinActivate, of course).

Thanks, PsaltyDS!

Share this post


Link to post
Share on other sites
JohnD

Salty,

Thank you and everyone else for the contributions so far. I am still learning and I appreciate your patience. I am constantly back and forth to the help file trying to learn as much as I can, and obviously what you folks have been contributing has been enormously helpful. However, a few questions persist.

So, I see from trial and error that what Mike said is true, for some reason the opt flag for in string matching does not apply for the excelbookattach function when used in Title mode. It does however work if you include the entire title in the first parameter. Progress.

Secondly (perhaps this is directed to mike), when I attain the window handle from the running Excel application I am not able to process it as a object (i.e. $oExcel.application.caption). In other words, $oExcel.application.caption, when used with WinActivate and its parameters does not work. I get runtime errors for that line even though I have a valid handle. Now, I did it a slightly different way and was able to work with it as an object, using

$oExcel = ObjGet("","Excel.Application") ; Get an existing Excel Object

This is sort of an imprecise way of getting the window I want (should others be opened at the same time the program could grab the wrong one), however, it does return the object that I can work with, and employee Salty's work around. With this method I gain an object I can work with, but lose the flexibility of being able to discriminate between other excel processes, and with Mike's way I gain the ability to discriminate, but lose the ability of work with it as an object.

Can there be a happy coexistence here? I'd like to have the functionality of being able to find the precise excel window (already opened) and work with it as an object. Still not there yet. Thanks for any help =]

Share this post


Link to post
Share on other sites
Mikesch

Hi JohnD,

where are these Excel Objects from? Do you open previously saved ones or are they created "on the fly" and yet unsaved / unnamed? It might help in suggesting solutions.

Share this post


Link to post
Share on other sites
JohnD

Hi JohnD,

where are these Excel Objects from? Do you open previously saved ones or are they created "on the fly" and yet unsaved / unnamed? It might help in suggesting solutions.

If I am understanding correctly, the object is being derived from the already opened windows process. I haven't written any further code that what is above. I guess I just have a few questions. Since excelbookattach can only be used with an exact title match that is not an option for me. So, if I am to use WinActivate, and use its return value (which is a windows handle) - how then do I address the the excel application as far as using the functions in the excel udf?

Also, I've seen a digital book for autoit on amazon. Is this worth purchasing? I'm wondering how best to get started. Thanks!

Share this post


Link to post
Share on other sites
PsaltyDS

There is only one instance of the Excel application running. This application has a collection of workbooks, and _ExcelBookAttach() goes through that collection looking for matching Title/File/Path. Open your copy of Excel.au3 and look at the code. What you get back from _ExcelBookAttach() is an object reference to the first matching workbook object (or 0 on failure). Window handles have nothing to do with it.

Unlike the object model for IE, I don't see any way to get the workbook's window HWND directly from Excel. The workbook object has a Windows collection of Window objects, but the HWND is not listed as a property of any that I can see.

:D

P.S. There does seem to be an Excel.Application.Hwnd property, but that's only the top level main Excel window. I don't know how you would cross that to multiple workbook windows.

Edited by PsaltyDS

Valuater's AutoIt 1-2-3, Class... Is now in Session!For those who want somebody to write the script for them: RentACoder"Any technology distinguishable from magic is insufficiently advanced." -- Geek's corollary to Clarke's law

Share this post


Link to post
Share on other sites
Mikesch

You're right, when it comes to handles, Excel is not very communicative.

JohnD, is there no way to make the Excel workbook show a unique title or filename e.g. by saving it, if only temporarily.

Could simplify things if working with multiple workbooks. _ExcelBookAttach with the modification mentioned above should work then, or am I missing something?

As for tutorials, I'd recommend the one mentioned here Learning to Script... for the very start, and this one, too: AutoIt 1-2-3

Share this post


Link to post
Share on other sites
Mikesch

Sorry, JohnD, I forgot to add a sample and ask if this could be more or less what you are looking for?

#include <excel.au3>

$Excel = ObjCreate("Excel.Application") ; Get an existing Excel Object

$book1 = _ExcelBookNew()
_ExcelBookSaveAs($book1, "d:\week.xls", "xls", 0, 1)

$book2 = _ExcelBookNew()
_ExcelBookSaveAs($book2, "d:\Values.xls", "xls", 0, 1)

_ExcelBookAttach("Week", "Title")
_ExcelWriteCell($book1, "This is Workbook ONE", 1, 1)

_ExcelBookAttach("Values", "title")
_ExcelWriteCell($book2, "This is Workbook TWO", 2, 2)

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
Sign in to follow this  

×