Simple Example to Explain Defining all Open Workbooks

[AlvaroSiza]

Assume:

1. Launch Excel. File > Open "Test.xlsm". From Excel, File > Open "Test2.xlsm". From Excel, File Open "Test3.xlsm"
2. Start > Launch new instance of excel. File > Open "Test4.xlsm", File > Open "Test5.xlsm".

1 instance of excel with 3 "open" workbooks, another separate instance of excel with two "open" workbooks".

How do I properly identify each of the files for manipulation?

[Jakobshavn]

It is easier to open the workbook via a macro. You can assign the Object after each is opened:

Please Login or Register  to view this content.

[JosephP]

if you know the full paths to each file you can use getobject to return a reference to them

if not you will require api calls to locate each excel window (XLMAIN) then iterate the workbook window handles within-you can use the accessibility objects to convert a window handle to an automation object

[TMS]

if you know the full paths to each file you can use getobject to return a reference to them

if not you will require api calls to locate each excel window (XLMAIN) then iterate the workbook window handles within-you can use the accessibility objects to convert a window handle to an automation object

That's one of those sentences where I understand all the words, or at least the great majority, but, joined together, it's just like a really loud buzzing in my head.

Care to elucidate?

Regards, TMS

[AlvaroSiza]

+1 @ TM. I need to see a sample script to understand the API calls.

Expanding on OP, the scenario is driven off a vbYesNo whereby user is asked if the data they want to retrieve is in an open workbook (which theoretically could be an open workbook in "active" instance of Excel, or another instance, which may include one or many other workbooks); or, if "no", .GetOpenFileName to a closed workbook.

I have the closed workbook via ADO method working. I am now trying to understand how to ask the user to pick which of all open workbooks, despite instance of excel, contains their data, set a path, and then consume.

[JosephP]

gee I was hoping nobody would ask ;-)

I don't have complete code to hand for that-but can probably put it together at lunch time if work clears a little

this is the code to get an Application object from a workbook handle (orig from stackoverflow I think)

Please Login or Register  to view this content.

then you just need a loop with findwindowex looking for XLMAIN windows then within each get the XLDESK window and finally one of the child EXCEL7 windows which you can pass to that function

easy as pie :-)

[AlvaroSiza]

Really? Sigh.

[TMS]

And, when the fairy dust settles ...

[JosephP]

like so-including proper attrib to stackoverflow. needs a reference to the scripting runtime as written but could easily be late bound

Please Login or Register  to view this content.

[JosephP]

if you're lazy you could also automate word and use its Tasks collection ;-P

[TMS]

@JP: oh, now you're taking the p.... mickey

[JosephP]

:-)
hey it wasn't me that decided only one instance of excel should get registered in the ROT