_Communicating between 2 XL instances_ !

[Jaafar Tribak]

I have two instances of XL running at the same time.

Instance A has workBook A loaded.

Instance B has WorkBook B loaded.

How do I get ,via code, the current value of Range("A1") in Sheet1 in Workbook B from WorkBook A ?

Any idea how to best achieve this ?

Regards.

 

[Legacy 98055]

The code worked in every way I could concieve of. A new workbook that had not been saved, a new workbook that had been saved but never closed, and a workbook that had been saved, closed, and then reopened. The behavior differed for each. However, it did do as advertised by passing a reference to the correct instance. In what way is it not working for you? Please explain...

I searched for the post Tusharm was speaking of and could not find it. I did find an example but the developer is reponsible for starting each instance of Excel himself.

See the framework's Marshal Class for a possible net solution.
See this page for some info on which API functions are called by GetObject...
Look here for various COM API functions...

I found a handful of posts from various boards that contained similiar requirements as yours. None of them were answered satisfactory... I would really like to see the post that Tusharm was talking about. Keep in mind, Tusharm, that we are looking for existing instances that the developer had no part in initiating...

Tom

 

[Jaafar Tribak]

Hi Tom,

I keep getting "Not located or does not exist..." when I assign the workbook name to the WorkbookShortName variable in the example procedure !

Sometimes I get the one of the Recently opend files instead !

I haven't followed the code properly so I may be missing something.

Thanks.

 

[Legacy 98055]

See edit^

 

[Legacy 98055]

I just tried the code, as is from the post, and it worked well. Try it again.

 

[Jaafar Tribak]

Tom,

Thank you for the various links

the stuff inside looks promising but quite intimidating and I don't know anything about COM at this stage but I am curious to learn about it & other related subjects.I can see the enormeous potential.

Do you have any suggestions as to how/if I should invest in learning this ?

Regards.

 

[Legacy 98055]

Just my opinion. I would focus on net. If I were to create a solution that you are looking for, I would check out the Marshall class. The limitation involved would be that the Net Framework needs to be installed on the PC. MS could be wrong, but COM should eventually, albeit slowly, phase out. Atleast MS hopes so. Nevertheless, Microsoft thought the same thing about DDE. If you are going to program COM you are not going to do it in VB but in a lower level language. From what I found searching and according to what Tusharm has claimed, you can accompish your goal from VB/VBA using the COM Apis. You will need to learn to create IUnknown variables in VB which I don't have a clue but I know it can be done. Look at the C/C++ examples on the net, the functions are recognizable. I just can't find a good enough reason. I checked out your scrollbar post and it appears that the only way to send a SetProperty message to Excel's scrollbars would be through subclassing and intercepting the messages sent to the parent window. It would be easier to literally create and register your own scrollbars using straight API functions. We all know that subclassing Excel in VBA = VBno...

Tom

 

[Jaafar Tribak]

Tom,

Thanks for the info.

Regarding the Scrollbars post, do you have any sample code at all ? even if it doesn't work at least it can give me an idea and hopefully I can build on it.

I have used the SetBkColor API on the scrollbars DC but I can't get it to work .

Regards.

 

[Legacy 98055]

Not specifically for Excel's scrollbars.

 

[tusharm]

Untested suggestions:

1) Use EnumWindows to get a list of handles to all running processes/applications. For each, in the callback procedure, use GetWindowText to get the window title (caption). Use that to figure out if it is the window of interest.

2) Ivan Moala has an example on his site of how to get a list of class names / captions for all running processes. Check out http://www.xcelfiles.com/API_06.html

Edit: Addition:

3) Also check Stephen Bullen's EnumDlg.zip at http://www.oaltd.co.uk/Excel/SBXLPage.asp#TheySaid

 

[Jaafar Tribak]

Untested suggestions:

1) Use EnumWindows to get a list of handles to all running processes/applications. For each, in the callback procedure, use GetWindowText to get the window title (caption). Use that to figure out if it is the window of interest.

2) Ivan Moala has an example on his site of how to get a list of class names / captions for all running processes. Check out http://www.xcelfiles.com/API_06.html

Edit: Addition:

3) Also check Stephen Bullen's EnumDlg.zip at http://www.oaltd.co.uk/Excel/SBXLPage.asp#TheySaid

Tusharm,

Thanks for the suggestions.

I know how to get the Window handle and Process ID of the second XL appllication (and all other running apps for that matter) using API functions, but don't know how to create a reference to it..so I could call it using a syntax like :

Set objXLSecondInstance=XLapplications(Instance2)

By setting a reference as shown above, one could easily use automation and manipulate all the objects in the second XL instane.

The real question is how to get a reference to a foreign process from it's Window hwnd or Process ID !

I have checked Stephen Bullen' s website as well as that of Ivan's but I couldn't find an answer to this problem.

Regards.