asynchronous communication between Excel instances

[vesc]

Any ideas on asynchronous communications between Excel instances?
Here are a few options, but I have no real knowledge/code to implement any of them:
1) Windows messages
2) Invoke sub/func in other instance, which sets up an OnTime call to the sub/func that does the actual work and returns.
3) Named Pipes
4) DDE
5) Shared Workbooks (new in Excel)

Thanks,

Vesc

 

[Legacy 98055]

An ActiveX.exe server is a great way depending on how much data will be transfered. It involves cross process handshaking and can bog down an app if there are huge amounts of data. If, however, you send your data in structured types, you can even get good performance with fairly large amounts of data. Another advantage is the simplicity of use. You can declare a reference to your server WithEvents and receive synchronal notifications via event procedures. Also, more than one project can use a single activex.exe simultaneously.

Another option for simple exchanges is using an API timer. I prefer this to using OnTime. Limited to Office 2000 and later.

If you are looking for specific help, please post more details about your project...

 

[vesc]

more info...

Hmmm...
I'm posting some further details below. A couple questions first...
1) Is it possible to build the ActiveX object in Excel? If so, how?
2) How do you use the API Timer to invoke a VBA macro?

I'm trying to solve the following issues:

1) A main "governor" opens other workbooks in new instances of Excel (new instances are required because loading all of this into one Excel instance kills it and I also want to run the processes in parallel to save on overall execution time). I want to be able to open these workbooks and invoke a macro, but I don't want to wait until that macro is finished processing. The macro that started the process needs to keep doing things while it waits for the invokee macro to complete processing.

2) I'd like to use the same or similar method to monitor the status of macro completion in the other workbook.

3) I'd love to be able to use this solution over a network (allow an instance to be invoked on another machine), but I doubt that's "in scope" at this time. The corp. network or our "managed environment" would likely stop any "solution" in tracks any way.

Any help with getting this to work on a single machine is greatly appreciated.

-Vesc

 

[vesc]

more info...

Hmmm...
I'm posting some further details below. A couple questions first...
1) Is it possible to build the ActiveX object in Excel? If so, how?
2) How do you use the API Timer to invoke a VBA macro?

I'm trying to solve the following issues:

1) A main "governor" opens other workbooks in new instances of Excel (new instances are required because loading all of this into one Excel instance kills it and I also want to run the processes in parallel to save on overall execution time). I want to be able to open these workbooks and invoke a macro, but I don't want to wait until that macro is finished processing. The macro that started the process needs to keep doing things while it waits for the invokee macro to complete processing.

2) I'd like to use the same or similar method to monitor the status of macro completion in the other workbook.

3) I'd love to be able to use this solution over a network (allow an instance to be invoked on another machine), but I doubt that's "in scope" at this time. The corp. network or our "managed environment" would likely stop any "solution" in tracks any way.

Any help with getting this to work on a single machine is greatly appreciated.

-Vesc

 

[Legacy 98055]

1) No. In Visual Basic 6.0
2) there are many examples on this board.

Here is another method that may do well enough for your purposes...

Create a workbook named "Caller.xls"
Place this code in ThisWorkbook class

Private app As Application
'using latebound method.  hence, object type instead of Workbook type
Private wb As Workbook

'Run code in another instance and be notified upon completion
Sub Example()
    Set app = New Application
    Set wb = app.Workbooks.Open(Me.Path & "\Notifier.xls")
    app.Run "Notifier", Me
End Sub

Public Sub ProcedureCompleted()
    MsgBox "ProcedureCompleted..."
    wb.Close False
    app.Quit
End Sub

Save it...

Create another workbook named "Notifier.xls". Place this code in a standard module.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private wb As Object

Sub Notifier(CallBackCaller As Object)
    Set wb = CallBackCaller
    Application.OnTime Now, "RunSomeCode"
End Sub

Sub RunSomeCode()
    Dim x
    
    For x = 1 To 10
        Sleep 1000
    Next
    
    wb.ProcedureCompleted
End Sub

Save and close. Save to the same path of "Caller.xls" for the sake of the example code...

Run sub "Example" in "Caller.xls". If you need an explanation, please reply...

Download and extract if you have any probs.

Notification from another wb in another instance.zip

 

[vesc]

Wow... just one follow-up question...

Tom, this is amazing. Exactly what I needed.

I do have a question, well two, but I think they are related...

The Excel instance running the Notifier.xls does not show up in the taskbar, seems odd. I'm wondering if this is related to creating a new Application object as opposed to using CreateObject("Excel.Application"), which I just found out about.

Tom, thanks for your post. It's truly amazing!

Thanks again,

Vesc

 

[Legacy 98055]

Vesc. Glad it works.

When you create an instance of Excel via automation, such as in our code, the application's Visible property is False by default. If you need to see it, then:

App.Visible = True

 

[vesc]

Thanks again!

How about expanding this notion to include creating the new instance on a different machine over a network! Any ideas on how to do this?

Regards,

Vesc

 

[Legacy 98055]

DCOM(Distributed COM) is the only way I know of though I am sure there are other avenues you might take. My knowledge is thin in the remoting arena.

 

[startingExcel]

1) No. In Visual Basic 6.0
2) there are many examples on this board.

Here is another method that may do well enough for your purposes...

Create a workbook named "Caller.xls"
Place this code in ThisWorkbook class

Private app As Application
'using latebound method.  hence, object type instead of Workbook type
Private wb As Workbook

'Run code in another instance and be notified upon completion
Sub Example()
    Set app = New Application
    Set wb = app.Workbooks.Open(Me.Path & "\Notifier.xls")
    app.Run "Notifier", Me
End Sub

Public Sub ProcedureCompleted()
    MsgBox "ProcedureCompleted..."
    wb.Close False
    app.Quit
End Sub

Save it...

Create another workbook named "Notifier.xls". Place this code in a standard module.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Private wb As Object

Sub Notifier(CallBackCaller As Object)
    Set wb = CallBackCaller
    Application.OnTime Now, "RunSomeCode"
End Sub

Sub RunSomeCode()
    Dim x
    
    For x = 1 To 10
        Sleep 1000
    Next
    
    wb.ProcedureCompleted
End Sub

Save and close. Save to the same path of "Caller.xls" for the sake of the example code...

Run sub "Example" in "Caller.xls". If you need an explanation, please reply...

Download and extract if you have any probs.

Notification from another wb in another instance.zip

Thank you for this subject.
It is helpfull.
But I have a question. Can we run two macros ( each one in a different excel application) in the same time ( in parallel)?