How do I get one instance of Excel to communicate with another instance?

[HONOREDANCESTOR]

I want to run 2 versions of Excel that communicate with each other.
This seems unusual, but let me explain why:

I have purchased a addin called "Evolver" which I want to run a macro
that runs another addin (that I've also purchased) called "Ptestpro".
"Ptestpro" has a menu that it adds to Excel.

The problem is that Excel does not seem to allow this. One addin
cannot run another addin, not even by sending keystrokes (using
"sendkeys") to the menu of the other addin.

So the only solutions I can think of are:
1. copy all the code of "Ptestpro" to a spreadsheet that is being
processed by "Evolver". (I am unclear as to whether this would help)
2. Run two instances of Excel, one of which has the "Evolver" addin,
the other which has the "PtestPro" addin. Evolver in one instance
would send a message to run "Ptestpro" in the other instance. When
"Ptestpro" is done, Ptestpro writes an answer to a cell in its instance
of Excel. This answer would have to be retrieved somehow by the Excel
running "Evolver".
so this breaks down to three parts:
a) Excel1 runs a macro in Excel2
b) Excel1 waits for Excel2 to finish running the macro
b) Excel 1 retrieves a value from a cell in Excel2

My question is, is this the way to go, and if so, what the code look
like?

Or is this a too complicated way of solving this problem?
Thanks,
HA

 

[John Coleman]

Greetings,

Your approach does seem too complicated. If you have both add-ins
installed and your project has references to both add-ins, then code in
your project can invoke procedures from either add-in and thus
coordinate them. You could do a search for "Solver" in this newsgroup
since that is an add-in which is frequently invoked by other code and
some of the code fragments you find might give you an idea of how to
invoke add-ins from VBA.

If you are using the raw code in Evolver (as opposed to using Evolver
as an add-in) then it might be as simple as adding a reference to
Ptestpro to your project with the Evolver code. Go to tools ->
references.

Hope this helps. If it doesn't - don't give up. I'd be surprised if you
really need to communicate between difference instances of Excel.

-John Coleman

p.s. What is Evolver? Sounds like a genetic algorithm optimization
tool.

 

[Peter T]

I really don't understand why you can't run the two addins in the same
instance, or as you've suggested copy the code into the same addin.

If necessary one addin can be loaded from file by the caller (even if it's
already loaded in another instance) and not necessary to 'install' it as an
addin.

It's convoluted and involves a lot of code to set cross references between
unknown instances. However if code in xl-1 knows the full-name of the file
loaded in xl-2 you could do something like this

'' code in instance 1
Dim sFullname As String
Dim vResult
Dim num As Long

sFullname = "C:\Temp\myAddin.xla" ' > change
'' for testing with an unsaved file in the other instance its fullname
simply -
'' sFullname = "Book2"

On Error Resume Next

Set xlApp2 = GetObject(sFullname).Parent
If xlApp2 Is Nothing Then
MsgBox "Can't find " & sFullname
Exit Sub
End If

num = 20
vResult = xlApp2.Run("fnExcel2", ThisWorkbook.FullName, num)

MsgBox vResult
End Sub

'' code in instance 2
Function fnExcel2(sCaller, n As Long) As Double
Dim xlApCaller As Excel.Application
Dim rng As Range

Set xlApCaller = GetObject(sCaller).Parent

Set rng = xlApCaller.ActiveSheet.Range("A1:A2")
rng(1, 1) = "Hi from " & ThisWorkbook.Name
rng(2, 1) = n * 100 ' direct to sheet

fnExcel2 = n * 100 ' return to function

End Function

Regards,
Peter T

 

[HONOREDANCESTOR]

Thanks for your help and also thanks to Peter T. Evolver is a genetic
alogorithm optimization tool - you can get a good description at:
http://www.jurikres.com/catalog/ms_evol.htm#top
--- HA